In Excel, you can add trendlines and drop lines to your charts to forecast data and show visual data trends.
Add a trendline
Select a chart.
Select a point in the data series (e.g. line chart, column chart, or bar chart).
Right-click and select Add Trendline.
Format a trendline
In the Format Trendline pane, select a Trendline Option to choose the trendline you want for your chart. Formatting a trendline is a statistical way to measure data:
Set a value in the Forward and Backward fields to project your data into the future.
Add drop lines
Select the chart in which you want to add drop lines.
Select Design > Add Chart Element > Lines > Drop Lines.
We can begin to analyze this data and possibly approach it and make some predictions about where we're headed in the future.
We can do this by adding what's called a trendline.
Trendline refers to actually a number of different choices.
If we right-click a line, and by the way, we can also do this with column charts and bar charts, I'm going to right-click the line, and add a trendline, and immediately in the background, we see a linear trendline, and off to the right, a dialog box, Format Trendline.
Now, if you know something about regression analysis, you have some feel or some knowledge of what these terms, Exponential, Linear, Logarithmic, Polynomial, Power, you might not have used them all but you have some familiarity with them, this is a statistical way to measure data.
Now, without going into a big explanation of it, and I'm not an expert on statistics, a linear analysis of the data here uses mathematical techniques and along the way it's also going to provide us with a number called the R-squared factor.
And we can display this.
This is used possibly as a predictor, also as a way to analyze existing data, so the closer to one it is, the better.
Some people might say, that's a fair number there. Now, if we were to make adjustments to the numbers over time, possibly that will change.
We can also make projections based on this.
For either one of these, we could consider projecting this line even further, so down below, you'll see ForecastForward.
I'm going to pick the number six here, and Enter, and now we see this projected six months beyond into December of 2016. But what does that point there represent?
Now we can certainly stop and point at it and see a pop-up that helps us, but it would be easier if we could attach lines from this point down to the actual date below.
So we can approach this in a couple different ways.
Now, from time to time, I've used this plus button to the right side here showing Chart Elements in various movies, and almost identical in most cases on the Design tab, the left button, Add Chart Elements.
That button and the plus to the right typically show the same choices.
I just click the plus, we see the choices there, let me leave that visible, and now I'll go back to that Add Chart Element off to the left.
Start comparing the entries.
We do see a few more entries, and the entry we're trying to get to here, the element that we'd like to be able to use, is called a drop line, and we don't see that at all under Chart Elements.
Off to the left, under Add Chart Element, we don't see it either, at least at first, until we come to Lines, and then we see Drop Lines, and immediately we see them on the chart.
And that makes the chart a lot more readable.
Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.
Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.
Get unlimited access to over 4,000 video courses.
Receive personal recommendations based on your LinkedIn profile.
Stream courses from your computer or mobile device.
Take courses for every level – beginner to advanced.
Practice while you learn with quizzes, exercise files, and coding windows.
Choose a plan for yourself or your entire team.