Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you analyze data using a PivotChart, you must pay close attention to the individual values in your datasets. Even so, it's often beneficial to take a step back and look at the overall trends in your data. You can enhance your analysis by projecting future values, assuming current trends stay constant, by adding a trend line to your PivotChart. To create a PivotChart trend line, you can click the PivotChart and then on the Layout contextual tab, click the Trendline button and then select the type of trend line that you want to create.
So in this case I'll click a Linear Trendline, which creates a line that best fits the data that's displayed within the PivotChart. Now the trend line only exists within the chart and the Excel program memory. It doesn't actually add data to your worksheet, and I should also point out that Excel uses linear regression techniques to create the trend line. These techniques are also implemented in the Forecast function. So you can generate the same results for data you don't summarize within a PivotChart. Now one other note is that you should always use Linear for your Forecast type, unless you know that you need to use another type of trend line.
Most of the time you'll only use the other types of regression for scientific and engineering work and you'll know when you need to use them. If you'd like more control of your trend line, make sure that the chart is still selected and then on the Layout tab click the Trendline button again, and then click More Trendline Options. When you do, the Format Trendline dialog box opens. One of the more common changes that I make is to extend the trend line by four periods to take it out into the future. So let's say for example that I have a Linear Trendline, which I do, and I want to create what's called a Forecast to look forward, and in this case I want to go from zero periods to four periods, and in this case each of my periods is, if you look back at the chart, 1 month.
So with that change in place, I can click Close and a trend line looking ahead four months appears in the body of the PivotChart. Please note though, that the farther out you make your forecasts, the more likely they are to be inaccurate, but even so, a trend line gives you a basis for comparison. Trend lines help you visualize future trends in your data. A PivotChart can't replace detailed analysis, but they do help to set the stage for your presentations regarding that data.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64867 Viewers
80 Video lessons · 124403 Viewers
52 Video lessons · 60327 Viewers
59 Video lessons · 46153 Viewers