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 a 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 in excelBubble Chart In ExcelIn Excel, a bubble chart is a type of scatter plot that uses bubbles to display values and comparisons. Like scatter plots, bubble charts compare data on both horizontal and vertical axes..
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 a trend line first, we need to create a column or line chart in excelLine Chart In ExcelLine Graphs/Charts in Excels are visuals to track trends or show changes over a given period & they are pretty helpful for forecasting data. They may include 1 line for a single data set or multiple lines to compare different data sets. for the above data. I am going to insert a column chart in excelColumn Chart In ExcelColumn chart is used to represent data in vertical columns. The height of the column represents the value for the specific data series in a chart, the column chart represents the comparison in the form of column from left to right. 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 the 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 –