Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you analyze financial data, you should pay close attention to the individual values within your data sets. Even so, it's often beneficial to take a step back and look at the overall trends in your data. If you summarize your data using a chart, you can project future values by adding a trendline. A trendline only exists within the chart and the Excel program memory. It doesn't actually add any data to your worksheet. So if you want to add a trendline to a chart, all you need to do is click the data series in the chart and this is important.
You don't want to click somewhere in the chart, just selecting the chart area. Instead, you want to select the data series and make sure that the data points are highlighted. That's how you know you are in the right position. As soon as you select the data series, under Chart Tools, go to Layout, and then in the Analysis group, click Trendline, and click Linear Trendline. Excel adds the trendline to the chart so you can see what the average progression will have been. So how do we extend this trendline into the future? Well first we'll remove the existing trendline and then with the data series still selected, Chart Tools> Layout, click Trendline, and then click More Trendline Options.
The Trendline Options dialog box gives a lot of tools that you can use to create a trendline, and there are different mathematical techniques that you can use to extend the line. Unless you are told differently, you should always use Linear. The other techniques are mostly useful in scientific and engineering applications. Now you need to determine how far into the future you want to create your forecast. To do that, you go down here to the forecast area, and in the Forward box, you type in how many periods you want to forecast into the future.
The data on this chart is annual, so our forecast will be for two years. We have that there, two years, click Close, and Excel updates the trendline. So that now you have the series extended two years into the future. Your data's measurements should occur at regular intervals so that your forecasting a given number of time periods into the future makes sense. In other words, you don't want some monthly measurements and some annual measurements. Trendlines help you visualize future trends in your data.
Summarizing data using a chart shouldn't replace detail analysis, but it does help 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 · 64717 Viewers
80 Video lessons · 124327 Viewers
52 Video lessons · 60259 Viewers
59 Video lessons · 46095 Viewers