Showing changes in data over time is one of the most common visualization tasks, and Excel makes it easy to create charts that do just that. Line charts and bar charts work quite well on their own for this, but if you want to get a more general big-picture sense of what the data is doing, it makes sense to add a trendline. They come in several different flavors, ranging from basic linear to the more specialized exponential and logarithmic. Adding and manipulating them, though, is quite straightforward.
Also read: How to Create a Dropdown List in Microsoft Excel
Generating the trendline
If you’re using the newest version of Excel (2013, 2016, 2019), it’s dead simple:
1. Select the chart you want to add a line to.
2. Click the “+” button on the top-right of the chart – it’s labeled “Chart Elements” when you hover over it.
3. Check the box that says “Trendline.”
4. By default, Excel inserts a linear trendline. If you want to change that, see below.
If you’re still using Excel 2010 (support for that is ending in 2020, by the way), it’s a little different:
1. Select the chart. The toolbar title above should say “Chart Tools” now.
2. Go to the Layout tab and find the “Analysis” group on the right.
3. Use the Trendline button to add whatever type of line you want.
Trendline types
If you click the little arrow that appears next to the Trendlines item in the Chart Elements menu, you’ll see that there are several different types available, as well as a “More Options …” box. Clicking through on that will show you every available trendline type.
Linear
This is the basic option and probably the easiest one for somebody reading the graph to understand. It just shows a line of best fit, or the rate at which something is increasing or decreasing. This is best for datasets where the points more or less fall on a straight line.
Moving average
If you have some rather choppy data, consider using a moving average to help you smooth it out and get a better picture of the general trend. For best results, adjust the periods – the number of data points Excel averages will decide where each point on the trendline will go.. The default is two, meaning it will take the average of every two data points. If that’s still overfitting, just add more to smooth out the line.
Exponential
If your data’s rate of change increases as the x-values increase, an exponential trendline can help you more accurately visualize what’s happening. This is best for data where the data is increasing or decreasing exponentially, as the name suggests.
Logarithmic
As the inverse of the exponential function, a logarithmic trendline is used for data where the rate of change decreases as the x-values increase. If something initially increases quickly and then levels out, a logarithmic trendline will probably fit quite well.
Polynomial
Polynomial trendlines are good for data that moves up and down in wave patterns. You’ll have to set the Order to make this work, but that’s pretty easy to figure out: just count the number of bends in the curve by looking at how many times it shifts from moving upward to moving downward or vice versa. Basically, just tally up the peaks and set the order to that.
Power
The power trendline is best for distributions where the data is increasing at a certain rate, such as with acceleration.
Also read: How to Combine Two or More Excel Spreadsheets
How to choose: check the R-squared
In the “More Options” panel there’s an option to display the R-squared value, which is a measure of how far each point on your chart is from the trendline. As a general rule, the closer the R-squared value is to one, the better your trendline fits the data. If you’re not sure which trendline fits your chart best, try checking which option gets the highest R-squared score.
Forecasting
If you want Excel to predict where the current trend is likely to take you, you can just use the “Forecasting” option to see its guesses about the future. Just set how many periods in the future (ticks on the x-axis) you’d like to see, and it’ll extrapolate based on the trendline you choose.
Adding multiple trendlines
There are two cases where you might want to have more than one trendline:
- Your chart is measuring multiple things, and you want to see trends for both
- You want to see what different types of trendlines have to say about the same data series
Either way, adding multiple lines is quite simple.
1. Right-click on the data series you want to add the trendline for. (If it’s a line, click on the line; if bars, click on the bars; etc.)
2. Click “Add trendline.”
3. Follow the steps above to make it look the way you want.
Cosmetic options
Having multiple trendlines with the same line style and colors could get confusing, so you probably want to make them look different or match up with their respective data series. Or maybe you just want to jazz up your chart a little bit. Either way, Excel has plenty of formatting options. The “Fill and line” menu lets you select line type, color, and thickness, and the “Effects” menu gives you Shadow, Glow, and Soft Edges options. Go crazy!
Editing and deleting trendlines
Changing or deleting trendlines once you’ve completed them is pretty intuitive. Just right-click on the line and select “Format Trendline,” and it will open up the options menu. You can make changes there, or just hit the Delete key to get rid of the trendline. They’re very quick to generate, so you don’t have much to lose by trying a few different types or combinations to see which one best gets your message across.
Subscribe to our newsletter!
Our latest tutorials delivered straight to your inbox
Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe
Andrew Braun
Andrew Braun is a lifelong tech enthusiast with a wide range of interests, including travel, economics, math, data analysis, fitness, and more. He is an advocate of cryptocurrencies and other decentralized technologies, and hopes to see new generations of innovation continue to outdo each other.
Comments are closed