Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Understanding Trendline
2.1.
Adding a Trendline
2.1.1.
Adding Multiple Trendlines
2.2.
Formatting a Trendline
2.3.
Displaying Trendline Equation
2.4.
Deleting a Trendline
3.
FAQs
4.
Key Takeaways
Last Updated: Mar 27, 2024
Easy

Trendline in Excel

Author Pakhi Garg
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

While plotting data in a chart, you may have visualized some kind of trends in it. What will you do if you want to show these trendlines in your chart? Well, the answer is simple, by using trendlines. Microsoft Excel provides a facility for adding a trendline to a chart. 

In this article, we will explore the trendline. 

Understanding Trendline

A trendline, also known as a line of best fit, is a straight or a curved line in a chart that depicts the data’s overall pattern or direction. It is an analytical tool used frequently to display data movement overtime or a correlation between two variables. It illustrates the general trend in the data while accounting for statistical mistakes and minor outliers. It can also be used to predict trends in some circumstances.   

We can add a trendline to various Excel charts like XY scatter, bubble, stock, unstacked 2D bar, column, area, and line graphs. However, we cannot add a trendline to 3D or stacked charts, pie, radar, and similar charts.

You might be confused between a trendline and a line chart due to their resemblance in appearance. However, a trendline does not link the data points as a line chart does. 

Next, we will see how to add a trendline in excel.

Adding a Trendline

Consider the following chart depicting the sales of an item in a company in the 12 months of 2021.

For the Excel versions 2019, 2016, and 2013, we just need to follow the following steps to add a trendline in excel.

  1. Select the chart for which you want to draw a trendline.
  2. On the right-hand side of the chart, you can see three buttons: 
    Chart Elements
    Chart Styles and
    Chart Filters
    Among them, select the Chart Elements button.

3. A list will appear. From the list, select the Trendline box to insert the default linear trendline.

4. If you want to add a different kind of trendline, then click on the arrow next to the Trendline box in the Chart Elements list and select the type of trendline you want. Like here, we have inserted the exponential trendline in the chart.

5. Further, if you want to explore more kinds of trendlines available in excel, click on More Options present in the list of Trendline and choose the one you want. By default, the linear trendline option will be selected.

Next, we will see how to add multiple trendlines to a chart.

Adding Multiple Trendlines

Here, we will see two cases to add multiple trendlines in the chart.

Case 1: Adding a trendline for each data series

Consider the following line chart depicting the sales of two items in a company.

Follow the step to insert a trendline for each data series, i.e., for item 1 and item 2.

  1. Select the chart.
  2. Click on the Chart Elements button on the right side of the chart → Trendline → Trendline type (Linear/ Exponential etc.) → Data series (Item 1/ Item 2).


 

If we select Item 1, it will give us a trendline as a result. 

Similarly, we can generate a trendline for Item 2.

Case 2: Adding different trendlines for the same data series.

To add more than one trend line in the data series,

  1. Select the chart.
  2. Click on the Chart Elements button on the right side of the chart → Trendline → Trendline type. 

For example, the below chart depicts two trendlines for the sales of Item 1 in a company. The trendline denoted by orange shows a linear trendline, and the trendline by green shows a two-period moving average trendline.

Next, we will see how to format a trendline.

Formatting a Trendline

If you want to make your chart more understandable and interpretable, you can change the appearance of your chart’s trendline.

To format a trendline,

  1. Select the trendline.
  2. Right-click → Format Trendline.
  3. A pane will appear on the right side of the screen. Go to Fill & Line tab. You can format your trendline's color, transparency, width, dash type, line type, etc.

Next we will see how to extend a trendline.

Extending a Trendline

We can also extend a trendline to determine data trends in the past or future. To extend a trendline,

  1. Select the trendline.
  2. Right-click → Format Trendline.
  3. A pane will appear on the right side of the screen. Go to the Trendline Options tab . At the bottom, there will be two labels under Forecast, Forward, and Backward. Type the desired value in forward to project the future data trends and backward to project the backward data trends.

For example, the below chart depicts a trendline predicting future trends for the next 5 months of the sales of item 1 in a company. 

 

Next, we will see how to display a trendline equation on the chart.

Displaying Trendline Equation

We can also display the trendline and R-squared value equation in the chart. To do so, follow these steps-

  1. Select the trendline.
  2. Right-click → Format Trendline.
  3. A pane will appear on the right side of the screen. Go to the Trendline Options tab . At the bottom, there will be two labels Display Equation on chart and Display R-squared value on chart. Check them to display the equation and R-squared value. 

For example, the below chart depicts a trendline with the trendline equation and R-squared value.

Next, we will see how to delete a trendline.

Deleting a Trendline

There are two ways to delete a trendline.

Method 1: 

  1. Select the trendline.
  2. Right-click → Delete.

Method 2:

  1. Select the chart.
  2. Click on the Chart Elements button.
  3. Unselect the Trendline box.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

FAQs

  1. Define chart.
    Excel's most basic technique of visualizing data is through charts. It provides a variety of graphic representations of our data to make it easier to understand. Charts in Excel are a visual tool to see trends in your worksheet's data.
     
  2. Define trendline.
    A trendline, also known as a line of best fit, is a straight or a curved line in a chart that depicts the data’s overall pattern or direction. It is an analytical tool used frequently to display data movement overtime or a correlation between two variables. 
     
  3. What is the difference between a trendline and a line chart?
    A trendline does not link the data points as a line chart does. 
     
  4. What are the different types of trendlines one can make?
    The different trendlines one can make are linear (default), linear forecast, exponential, logarithmic, polynomial, power, and moving average. 
     
  5. How can we project future and past data trends in a trendline?
    On the right-hand side, go to the Trendline Options tab. At the bottom, there will be two labels under Forecast, Forward, and Backward. Type the desired value in forward to project the future data trends and backward to project the backward data trends.

Key Takeaways

In this article, we have studied trendline in excel. We went through the concept thoroughly, discussing adding, formatting, extending, and deleting a trendline. We also discussed how to display the trendline equation in a trendline.

We hope this blog has helped you enhance your knowledge regarding trendline in excel. If you want to learn more, check out our article on Bar ChartPie ChartLine Chart, and Area Chart. Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow.

Happy Learning!

Previous article
Chart Sheet
Next article
Error Bars
Live masterclass