Trend lines in Excel can be a useful way to highlight data trends and patterns in your Excel charts, learn how to insert a trend line in Excel and which trend line to use…
What is a trend line?
A trend line is a way of graphically displaying an average over time on an Excel chart. For example you could insert a trend line on a chart that contains sales by month for a 24 month period to highlight the trend of increasing sales for the business.
When should you insert a trend line?
Some clients will state their preference for a trend line on Excel charts but in general you should be specific with your use of trend lines. With some data you will see a clear and obvious trend as soon as you chart the information which makes the addition of a trend line unnecessary.
For example there is little value in using a trend line on the chart below as the trend, or direction of the data, is clear:
However, a trend line would add more value to the next chart shown as the peaks and troughs can take the users eyes away from the fact that sales is actually trending upwards:
How to insert a trend line on an Excel chart
When you have created an Excel chart if you decide that a trend line would be a useful addition then complete the following steps to add a trend line.
First select the chart so that it becomes the active object, left-click anywhere on the chart to do this. If you then look up to the top of the worksheet you will see the chart tools options appear on the Excel Ribbon:
Select the Layout option from the Chart Tools and directly beneath the Layout you will see a Trendline icon, left click this icon to show the various trendline options:
The most common form of trend line used in Excel reporting is the linear trend line. The reason for this is because you use a linear trend line when something is increasing or decreasing at a certain rate, so for charts like sales or volumes over time this becomes the standard selection. Other options are discussed at the end of this post.
For now select the Linear Trendline option from the drop-down menu and when you check back to your Excel chart it will now have a Linear Trendline applied to it.
How to format a trend line
In the same way you can format the lines or bars on an Excel chart, changing the colour, using a border or changing line thicknesses etc. you can do all of this to a trend line. This is very useful when you use a common colour theme in your Excel dashboards or reports or you need the trendline to stand out.
To format right click on the trendline to bring up the Format Trendline option, then select it:
When selected a new options box will open containing all the possible formats you can make on a trend line:
The box is quite busy so may seem confusing and complicated to new users but unless you are looking to use one of the more technical modelling trend lines then the formatting box is really only used to change the line colour and perhaps alter the style.
For example to make a trendline grey and dashed rather than black and unbroken first select Line Color on the left side of the options box, followed by selecting Solid Line and Changing the Color to Grey using the Color palette box as shown below:
Next select Line Style from the left hand menu and select the dash option from the Dash Type icon:
Other trend line options and when to use them
There are other options available for trend lines in Excel and each has a different purpose. Take care when thinking of introducing one of these trend lines into a chart as while they can be very powerful for the right job it is important that the audience understands how to interpret the extra value they provide, otherwise they can be a confusing inclusion to the non-technical users out there.
Logarithmic: You should consider using the logarithmic trend line in Excel when you have data that rapidly increases/decreases before leveling out.
Polynomial: This one is more for the mathematical minded and can be used when you have data that fluctuates up and down across a large sample. You ideally should have an understanding of Polynomial ordering (i.e. an Order 3 polynomial will have 1-2 peaks and troughs) in order to use this effectively but this can be a powerful tool to illustrate the relationship between 2 measurements.
Power: A Power trend line in an Excel chart should be used when you are comparing measurements that increase at a specific rate. For example if you were charting distance over time with 1 hour intervals then a Power trend line can be used effectively.
Exponential: As might be expected you would use an Exponential trend line in your Excel chart when the data increases or decreases exponentially, that means the values rise or fall by increasingly higher rates over time.
Moving Average: The moving average trend line in Excel should be used when you have data which needs smoothing to show the trend more clearly. This works well on data values that are more erratic by nature. In order to use this effectively you must set the number of periods for the moving average to be based on therefore it works best with data stored over a longer period.
You can download a sample file in Excel format here.