Reading Values from Graphs

When creating charts from Excel data, you can smooth out the lines between data points by using any number of methods. At some point, you may want to actually figure out how Excel does its calculations to determine where to actually plot points along the line. Rather than visually trying to figure out where a point falls,

If you are using Excel 2013 or a later version, follow these steps:

1. Right-click on the data series in question. Excel displays a Context menu.
2. Choose the Add Trendline option from the Context menu. Excel displays the Format Trendline pane at the right side of the screen. (See Figure 1.)



Figure 1. The Format Trendline task pane.

3. Make sure the regression type you want to use is selected.
4. Make sure the Display Equation on Chart check box is selected.
5. Close the task pane.

If you are using Excel 2007 or Excel 2010, the steps are different only in that Excel uses a dialog box instead of a task pane:

1. Right-click on the data series in question. Excel displays a Context menu.
2. Choose the Add Trendline option from the Context menu. Excel displays the Format Trendline dialog box. (See Figure 2.)



Figure 2. The Format Trendline dialog box.

3. Make sure the regression type you want to use is selected.
4. Make sure the Display Equation on Chart check box is selected.
5. Click on OK.

The result is that Excel shows a formula, on the chart, that represents how it calculated each point along the line. You can then use this formula to determine points, as well. No more guessing! Once you know the formula, you can turn off the formula display if you want it off.

If you would like to know the different formulas that Excel uses for different types of trend lines, you can use the online Help system to search for "equations for calculating trendlines."

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10698) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Reading Values from Graphs.