Excel Trend Line
A trend line often called as “the best fit line” is a line that shows the trend of the data. As you have seen in many charts it shows the overall trend or pattern or direction from the existing data points. Excel provides the option of plotting the trend line to the chart. When we add this trend line to the chart it looks like a line chart but without any ups and downs.
How to Add and Insert Trend Line in Excel?
To add trend line in excel first we need to insert the chart for the available data. Before we add a trend line just remember what are the charts that support the trend line in excel. We can add a trend line to a column chart, line chart, bar chart, scattered chart or XY Chart, Stock Chart, Bubble Chart.
But we cannot add a trend line to 3-D or Stacked Charts, Radar Charts, Pie Charts, and similar kinds of charts.
The best example of plotting a trend line is monthly sales numbers. Below are the monthly sales numbers to create a chart.
To add an excel trend line first we need to create a column or line chart for the above data, I am going to insert a column chart for this data.
Once the chart is inserted adding the trend line is easy in Excel 2013 and the above versions. Select the chart it will show the PLUS icon on the right side.
Click on this PLUS icon to see various options related to this chart. At the end of the options, we can see the “Trend Line” option, click on this to add a trend line.
So, our trend line is added to the chart. If you have inserted the LINE CHART in place of COLUMN CHART all the steps are the same as inserting the Trend Line in excel.
We can also add trend lines in multiple ways. Another way is to select column bars and right-click on the bars to see options. From the options choose “ADD TREND LINE”.
This will add the default trend line of “Linear Trend Line”. This is the best-fit trend line to show whether the data is trending upwards or downwards.
We have several trend line types, below are the types of the trend line.
- Exponential Trend Line
- Linear Trend Line
- Logarithmic Trend Line
- Polynomial Trend Line
- Power Trend Line
- Moving Average Trend Line
All these trend lines are part of the statistics. One of the other popular trend lines is the Moving Average Trend Line.
Moving Average Trend Line shows the trend of the average of a specific number of periods, for example, the quarterly trend of the data. To apply moving average trend line right click on column bars and choose Add Trend Line, this will open up the Format Trend Line window to the right end of the worksheet.
In the above window choose the “Moving Average” option and set the period to 2. This will add the trend line for the average of every 2 periods.
How to Format the Trendline in Excel Chart?
The default trend line doesn’t come with any special effects on the trend line. We need to format the trend line to make it more appealing.
Select the trend line and press Ctrl +1. In the formatting trend line window choose “FILL & LINE”, make width 2 pt, and color to dark blue.
Now from this excel trend line, we can forecast the sales numbers for the next months as well. To do this go to TREND LINE Options “Forecast” and Forward 3.
As we can see in the above image we have added a forward trend as 3 periods and this increased the trend line for 3 months.
From this chart, our trend line shows a continuous decline in sales numbers.
Things to Remember
- Trend Line is a built-in tool in excel.
- Moving Average Trend Line shows the average trend line of the mentioned periods.
- Always format the default trend line to make it more appealing.
This has been a guide to Trend Line in Excel. Here we learn how to add and insert the trend line in excel along with examples and downloadable excel template. You may learn more about excel from the following articles –