Charts are great for visually displaying trends, but sometimes the actual trend is hard to distinguish. Chart values can vary significantly, making it difficult to determine a true trend. Fortunately, Excel 2013 allows us to add a true, statistically significant trend line to some charts. This type of analysis is also called a regression analysis. With Excel 2013 we can even extend the trend to predict values beyond what's in the chart. We won't be using one in this dashboard, but it's useful in many dashboard scenarios for forecasting, so it's worth covering.
To build a trend line into a chart we have to use a particular layout for the chart. Let's set up a chart and add a trend line to it:
Select the Revenue tab.
Highlight the pivot table, right-click, and select Copy.
Select cell A15, right-click, and select Paste.
Select the new pivot table. If the pivot table field list doesn't appear on the right, select Analyze under the Pivottable Tools ribbon area and select...