A trendline, also known as a line of best fit, is a straight or curved line that sits on top of a series of data points to help visualise and determine if there is a clear pattern in our data. Adding a trendline in Excel to our data is a simple but effective technique.
It is a very common method when working with datasets to not only determine a pattern but to determine relationships between different variables.
In this simple guide, we are going to run through a quick and easy approach on how to apply trendlines in Excel to a scatterplot. While line of best fit is a common name for these as well, Excel refers to them as trendlines so for consistency this is what we will refer to them as for the rest of this guide.
We will cover:
- Applying a trendline to a chart
- Using trendlines to forecast results
Let’s get started with an example dataset.
Applying a trendline to a chart
In our example dataset, we are measuring sales over each month of a year, with the goal of seeing if there has been a clear uplift in our sales over time.
The example data looks like the below:
To start with, let’s make a quick scatterplot by highlighting all of the cells and under the Insert ribbon, select a scatterplot:
This gives us the below scatterplot to work with:
Before we move on to the trendline itself, it is worth noting that there is nothing stopping us adding a trendline to another type of visualization – a bar chart for example – however scatterplots are by far the easiest to read when working with this kind of project.
From here, to apply a trendline there are two different approaches we can take.
The first method involves selecting the chart, and then under the Chart Design ribbon, select Add Chart Element and select Trendline and then the preferred type – in this case we are selecting Linear.
The second approach is a bit quicker, and simply involves clicking on the little green + sign next to the chart itself and ticking Trendline. Easy!
This second approach defaults to a Linear Trendline, which will appear like the below output:
As we can see, there are various types of trendlines to choose from. Rather than write up a detailed summary of each there are some great resources online such as this article which describe in depth the types of options depending on the data we are using.
Next up, let’s have a look at how we can use this trendline to predict sales.
Using a trendline in Excel to forecast results
Taking this one step further, and assuming we are confident in the strength of our data and the trend that has appeared – we can also use trendlines in Excel to make predictions about future data.
In this case, and again assuming there are no outside factors such as seasonality, we can use the trendline to predict how many sales we will have from October – December.
Using our existing chart, if we right click into the trendline itself in our chart there is a Format Trendline option we can select:
Next up, you will see that outside of changing the type of trendline, there is a Forecast section where we can look forwards and backwards.
In our example since we are looking to forecast forwards to the end of the year, and our dataset is using individual dates as markers – let’s enter 90 in the forward box:
The resulting output will appear as follows:
As we can see, this has extended the trendline and X axis, indicating that we should be reaching 70,000 sales a month by December.
There is a lot more that can be done with trendlines / line of best fit, but this covers off the basics and provides a solid base to build on. This sums up our guide on how to add a trendline in Excel to our charts. For more handy guides on working with Excel, be sure to check out our Excel Tips page.