Trend line in excel is used to visualize the trend of the past data and forecast the future. This can be very handy in a situation when you have to answer a few questions like how our product will perform next year, how much growth you see next month, by what time we will reach our break-even, etc.
This tutorial on excel trendline is for advanced excel users. Here, I will assume that you know how to plot charts from the data and have a basic concept about the trend lines.
Let’s start with a simple example. Suppose in last ten years the total number of sales for a company is 120, 135, 130, 150, 160, 140, 150, 180, 200 and 190 respectively. Now, you have to predict the sales for this company for next two years. Without trend line and just viewing the sales figures, probably you will be able to answer that it should grow. But by how much, it will be difficult for you to tell. Here the trend line in excel will be greatly useful.
What is Trend Line?
Before we begin, let’s understand first what a trend line is.
Trendline is basically a line which represents the trend based on the data series. Normally, a trendline starts from the first data point and goes up to the last one. But you can extend this trendline beyond the last point for forecasting the future data. Trendline will show you how your data will look like in the future. In Microsoft Excel 2003, 2007, 2010 & 2013; trendlines can be added to bar, column, 2-D, scatter, bubble, line and area charts. But it cannot be added in a stacked, 3-D, radar, pie, surface, or doughnut chart. The forecasting with trendline is also called regression analysis.
Creating our First Trend Line in Excel
Let’s work on the example above to draw our first trendline. Make a bar chart with the above sales data. Now, there are two methods to add a trend line. The simplest way is to right click on the data series in chart and select Add Trendline. Otherwise in case of Excel 2007, Excel 2010 or Excel 2013; click anywhere in the chart area and the chart tools appears. Inside chart tools option, select layout tab and under the analysis sub-group, click on Trendline and select ‘more trendline options’.
In Format Trendline pop-up window, you get the trendline options to select Trend/Regression Type, Trendline Name, set Forecast and display the equation and R-Square value on the chart. You also have the options to line color, style and shadow.
In excel 2003 or earlier; you get ‘Add Trendline’ pop-up window. Here you get ‘Type’ tab to select the trend line type and ‘Options’ tab for name, forecasting and displaying the equation. We will come to this later. For now, select the linear trend type (which is selected by default) and click close. This creates a linear trendline on the chart area.
Great! You have just created the first trendline in excel. Looking at this, you can clearly say that in next two years, there is a strong possibility of sales growth. But by how much, this we will discuss in forecasting section.
There are six types of trend lines to select from in Microsoft Excel. These are Exponential, Linear, Logarithmic, Polynomial, Power and Moving Average. However, the most commonly used trend type is Linear. If you are from statistics or mathematics background, you probably already understand what the various trend types are. But for the rest, I am going to explain in short about each of them.
Linear: The most widely used tend line is linear. When your data points in the chart follow a straight line (or appear to be nearly in a straight line); you should use linear type. A linear trend line indicates that the data value is changing (increasing or decreasing) at a constant rate. The sales example that we have taken is a good example of linear trendline.
- Logarithmic: If your plotted data points increases or decreases sharply at first and then starts to become nearly constant, you should use a logarithmic trendline. A logarithmic trendline can have both positive and negative data points. For a new born baby, the height growth is very fast in initial years, after about 15-20 years, it starts to flatten out. This is a good example where you should use a logarithmic trend line.
- Power: If your plotted data appears in a shape of curve (either upward or downward), choose power trendline. Basically power trendline illustrates a data series which either increases or decreases sharply but at a constant rate. However, for plotting a power trend line you must have non zero positive data values.
- Exponential: If your data changes sharply at constant increasing rates, you should use exponential trend line. Exponential trend lines are very similar to power trend line but are curvier at one end than the other. The power trend on the other hand is more symmetric.
- Polynomial: If you find that your data is fluctuating i.e. changing direction more than once in a definite fashion, you should try a polynomial trend line. But for good forecasting with polynomial trend line, you should have a large amount of data available. With polynomial trend line, you have the option to decide the order. If your fluctuation is more, go for higher order.
- Moving Average: We make a moving average trend line to smoothen the curve. For example if you plot the number of visitors to a website over a period of six months, there will be a lot of variation on day-to-day basis. But if you plot it weekly or monthly, you can see the clear trend. A moving average takes the average of defined number of points (set by period) and plots it as the next point.
Tips on choosing the right trendline
In the trendline options pop-up window, you have the option to display R-Squared value on chart. This R-Squared value is a number between 0 and 1 which indicates how closely the trendline fits with the data point. If your R-Squared value is 1, the trend line is most reliable. As it goes below towards 0, the trend line becomes more erroneous.
So, you have the clue. If you are not sure about the type of trend line, try with different ones and see the corresponding R-Squared value. You can go for the trend line where the R-Squared value is closer to 1. R-Squared value is also known as co-efficient of determination.
Displaying the Trendline Equation and R-Squared value
You have the option to display the equation of the trendline drawn and the R-Squared value in the chart. You may like to display it for forecast calculation and understanding the accuracy of trend line. The option to display trendline and/or R-Squared value is in ‘Trendline Options’ tab. Note however that this display is not available with moving average graph because there is no prediction here. This is reflecting the actual data just in another fashion.
If you know or estimate the initial value of Y axis, i.e. where the trendline crosses it, you can select ‘Set Intercept’ n the ‘Trendline Options’. For example, if you are plotting a distance covered by car every second after start; you know that the trendline crosses Y axis at zero. This is because the distance covered by the car at starting point is zero. This helps you to make your trend line more accurate. Note however that set intercept option is available only for linear, exponential and power trend lines.
Formatting a Trendline
Well, I am not going to deliberate it here assuming that you are an advanced user. As I mentioned earlier, you have the option to change the line color, line style and shadow in the ‘Format Trendline’ option box. Go on; format your trendline to make it look beautiful. In excel 2003 or earlier, right-click on the trendline and select ‘Format Trendline’ option.
Forecasting with Excel Trendline
You have already learnt how to insert a trendline in excel chart and know how to select the appropriate trendline type. Moving on next, let’s see how to do forecasting.
For forecasting with excel trendline, we need to do two extra things other than inserting trendline.
- Display the equation on the chart.
- In forecasting section, define the period for which you want forecasting in ‘Forward’ textbox.
You can preview in the extended trendline on the chart which indicates the forecasting. Now, if you use the same sample data (sales data), you can find that the equation displayed in chart is as follows:
y = 7.969x + 111.6
The R-Squared value in this case is 0.815 which indicates that the trendline is quite reliable. In the equation, y indicates the sales figure and x indicates the year number. Now for the next year the x value in this case is 11.
Sales for next year = 7.969 x 11 + 111.6 = 199.2 i.e. 199 Nos
Sales for next to next year = 7.969 x 12 + 111.6 = 207.2 i.e. 207 Nos
Similarly, you can do the forecasting for other types of trendlines. Just put the value of x in the equation for getting the forecasting.
Note: If you just want to do forecasting without using a trendline, in excel, you can use GROWTH() or TREND() functions. These functions allow you to forecast data based on linear and exponential lines.
Removing the Trend line from Chart
Removing the trendline from chart is super easy. Just select the trendline and press ‘Delete’. Otherwise, you can right-click on trendline and select ‘clear’ in Excel 2003 or earlier.
But excel 2007 and above also provides you another lengthy method to remove trendline. Click anywhere inside the chart. Go to the Layout tab and in the Analysis group, click Trendline. Now click ‘None’ to remove the trendline.