Creating Charts and Graphics in Microsoft Excel 2013

  • 3/31/2014
Contents

Finding trends in your data

You can use the data in Excel workbooks to discover how your business has performed in the past, but you can also have Excel make its best guess—for example, as to future shipping revenues if the current trend continues. Consider a graph that shows the fleet maintenance costs for the years 2006 through 2012 for Consolidated Messenger.

httpatomoreillycomsourcemspimages1561278.png

The total has increased from 2006 to 2012, but the growth hasn’t been uniform, so guessing how much maintenance costs would increase if the overall trend continued would require difficult mathematical computations. Fortunately, Excel can perform that math. To have Excel project future values in the maintenance costs data series, click the chart, click the Chart Elements action button, point to Trendline, click the right-pointing triangle that appears, and then click More Options to display the Format Trendline pane.

On the Trendline Options page of the Format Trendline pane, you can choose the data distribution that Excel should expect when it makes its projection.

httpatomoreillycomsourcemspimages1561279.png

TIP

If you don’t know which distribution to choose, use Linear, which applies to most business data. The other distributions are used for scientific and engineering applications and you will most likely know, or be told by a colleague, when to use them.

After you choose the distribution type, you can tell Excel how far ahead to project the data trend. The horizontal axis of the chart used in this example shows revenues by year from 2006 to 2012. To tell Excel how far in the future to look, enter a number in the Forecast area’s Forward box. In this case, to look ahead one year, enter 1 in the Forward box, and then click OK to add the trendline to the chart.

TIP

When you click the Trendline button in the Analysis group, one of the options Excel displays is Linear Forecast Trendline, which adds a trendline with a two-period forecast.

As with other chart elements, you can double-click the trendline to open a formatting dialog box and change the line’s appearance.

In this exercise, you’ll add a trendline to a chart.

SET UP

httpatomoreillycomsourcemspimages1561014.png

You need the FutureVolumes workbook located in the Chapter09 practice file folder to complete this exercise. Open the workbook, and then follow the steps.

  1. Select the chart.

  2. Click the Chart Elements action button, point to Trendline, click the right-pointing triangle that appears, and then click More Options. The Format Trendline pane appears.

  3. If necessary, in the Trend/Regression Type area, click Linear.

  4. In the Forecast area, in the Forward field, enter 3.

  5. Click the pane’s Close button to add the trendline to the chart.

    httpatomoreillycomsourcemspimages1561280.jpg

    Click to view larger image

CLEAN UP

httpatomoreillycomsourcemspimages1561018.png

Close the FutureVolumes workbook, saving your changes if you want to.

Save to your account

This chapter is from the book