How to work with trend lines in Microsoft Excel charts.
Contents
You can add a trend line to a chart in Excel to show the general pattern of data over time. You can also extend trend lines to forecast future data. Excel makes all this easy.
A trend line (or line of best fit) is a straight or curved line that visualizes the general direction of values. They are generally used to show a trend over time.
In this article, we’ll cover how to add different trend lines, format them, and extend them for future data.
Add a trend line
You can add a trend line to an Excel chart with just a few clicks. Let’s add a trend line to a line graph.
Select the chart, click the “Chart Elements” button, and then click the “Trendline” checkbox.
This adds the default linear trend line to the chart.
There are different trend lines available, so it’s a good idea to choose the one that works best with the pattern in your data.
Click the arrow next to the “Trend Line” option to use , including the moving or exponential average.
Some of the key trend line types include:
Linear: A straight line used to show a constant rate of increase or decrease in values.
Exponential: This trend line visualizes values increasing or decreasing at an increasing rate. The line is more curved than a linear trend line.
Logarithmic: This type is best used when the data is increasing or decreasing rapidly and then levels off.
Moving Average – To smooth out fluctuations in your data and show a trend more clearly, use this type of trend line. It takes a specified number of data points (two is the default), averages them, and then uses this value as a point on the trend line.
To see the full complement of options, click on “More Options”.
The Format Trendline panel opens, presenting all the trendline types and other options. We will explore more of these later in this article.
Choose the trend line you want to use from the list and it will be added to your chart.
Add trend lines to multiple data series
In the first example, the line chart had only one data series, but the following column chart has two.
If you want to apply a trend line to only one of the data series, right-click on the desired item. Then select “Add Trendline” from the menu.
The Format Trendline panel opens so you can select the trendline you want.
In this example, a moving average trend line has been added to the Tea data series charts.
If you click the “Chart Elements” button to add a trend line without first selecting a data series, Excel asks you which data series you want to add the trend line to.
You can add a trend line to multiple data series.
In the image below, a trend line has been added to the tea and coffee data series.
You can also add different trend lines to the same data series.
In this example, the linear trend lines and moving average have been added to the chart.
Format your trend lines
Trend lines are added as a dashed line and match the color of the data series to which they are assigned. You may want to format the trend line differently, especially if you have multiple trend lines on one chart.
Open the Format Trendline panel by double-clicking on the trendline you want to format or by right-clicking and selecting “Format Trendline”.
Click the Fill & Stroke category, and then you can select a different line color, width, stroke type, and more for your trend line.
In the example below, I’ve changed the color to orange, so it’s different from the column color. I also increased the width to 2pt and changed the dash type.
Extend a trend line to forecast future values
A very cool feature of trend lines in Excel is the option to extend them in the future. This gives us an idea of what future values might be based on the current trend in the data.
In the Trendline Format panel, click the Trendline Options category, and then type a value in the “Forward” box under “Forecast.”
Show R-squared value
The R-squared value is a number that indicates how well your trend line matches your data. The closer the R-squared value is to 1, the better the trend line fit.
In the Trendline Format panel, click the “Trendline Options” category, and then check the “Show R-squared value on chart” checkbox.
A value of 0.81 is displayed. This is a reasonable fit, as a value greater than 0.75 is generally considered decent; the closer to 1, the better.
If the R-squared value is low, you can try other types of trend lines to see if they fit your data better.
