Home > Sample chapters > Microsoft Office > Excel

Creating Charts and Graphics in Microsoft Excel 2013

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

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.

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.

  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.