Pinterest Pixel

How to add Trendline in Excel Charts

With Excel Charts, it is very easy to create Trendlines for your data.  Trendlines show which direction the trend of... read more

Download Excel Workbook
Bryan
Posted on

Overview

How to add Trendline in Excel Charts | MyExcelOnline How to add Trendline in Excel Charts | MyExcelOnline

With Excel Charts, it is very easy to create Trendlines for your data.  Trendlines show which direction the trend of your data is going, and gives you the trajectory as well.

In this tutorial, you will cover all of these sub-topics in details:

How to add Trendline in Excel

*** Watch our video and step by step guide below on Trend Chart in Excel with a free downloadable workbook to practice ***

Watch on YouTube and give it a thumbs up 👍

How to add Trendline in Excel Charts | MyExcelOnline

 

In this example, you will learn how to insert a trendline in Excel using a Line Chart.

download excel workbookHow-to-add-trendline.xlsx

 

STEP 1:Highlight your table of data, including the column headings:

How to add Trendline in Excel Charts

Go to Insert > Recommended Charts (Excel 2013 & 2016)

How to add Trendline in Excel Charts

Go to Insert > Line > 2-D Line (Excel 2010)

How to add Trendline in Excel Charts

 

STEP 2: Select All Charts > Line > OK (Excel 2013 & 2016)

How to add Trendline in Excel Charts

 

STEP 3: Right-click on the line of your Line Chart and Select Add Trendline.

How to add Trendline in Excel Charts

 

STEP 4: Ensure Linear is selected and close the Format Trendline Window

See also  61 Excel Charts Examples!

How to add Trendline in Excel Charts

Now you have your Trendline in your chart, and you can predict where the trajectory is going in the succeeding periods:

How to add Trendline in Excel Charts

Different types of Trendline

There are different types of trendlines available to be added to the Excel Charts:

  • Linear – It is a straight line that shows the increase/decrease in the value of data over time at a steady rate.
    How to add Trendline in Excel Charts
  • Exponential – It shows the increase/decrease in the value of data at an increasingly higher rate. It is usually more curved on one side.
    How to add Trendline in Excel Charts
  • Logarithmic – This trendline should be used when the data increase/decrease quickly and then levels out.
    How to add Trendline in Excel Charts
  • Polynomial – It is a curved line that should be used when data fluctuates more than one rise and fall.
    How to add Trendline in Excel Charts
  • Moving Average –  It is used to smooth the extreme fluctuations of data and show a clearer direction to data trends.

    How to add Trendline in Excel Charts

Format the Trendline

Now that you know how to add trendline in Excel, let’s move forward to understand how to format them. You can change the color, transparency, width, dash type, compound type, cap type, and more for your trendline.

In the example below, you want to change the color of the trendline to blue, increase the width to 2.5, and change the dash type.

See also  Create Pie Chart in Excel Like a Pro: Fast & Simple Tutorial

STEP 1: Right-click on the Trendline.

How to add Trendline in Excel Charts

STEP 2: Select the Format Trendline option.

How to add Trendline in Excel Charts

Or, you can skip STEP 1 & 2 and simply double click on the trendline to open the Format Trendline pane.

STEP 3: From the Format Trendline pane, click the Fill & Line category.

How to add Trendline in Excel Charts

STEP 4: Select color – BLUE

How to add Trendline in Excel Charts

STEP 5: Change the width to 2.5

How to add Trendline in Excel Charts

STEP 6: Change the dash type

How to add Trendline in Excel Charts

Your formatted trendline is ready!

How to add Trendline in Excel Charts

Use Trendline to Forecast Future Data

In Excel, you can extend your data and project the data trend into the future or past using the features of Trendline.

In this example, you want to forecast sales for the next 6 periods. Follow the steps below to understand how to forecast data trends.

STEP 1:Double click on the trendline to open the Format Trendline pane.

How to add Trendline in Excel Charts

STEP 2: Select the Trendline Options tab.

How to add Trendline in Excel Charts

STEP 3: Under forecast, type “6” in the forward box.

How to add Trendline in Excel Charts

Your project data trend for the next 6 periods is created.

How to add Trendline in Excel Charts

You can use trendline to extrapolate the trend of the data into the past. Simply type the period in the backward box instead of the forward box.

See also  Create Impactful Clustered Column Charts in Excel - Step by Step Guide

Add multiple trendline to same chart

Until now, you have learned how to add trendline in Excel. But you can add multiple trendlines to the same chart. Let’s see how it can be done.

Say, you want to add both linear and moving average trendline to the existing data. Add the first linear trendline as discussed above and then follow the steps below to add another trendline.

STEP 1: Click on the Chart Elements (“+” icon) on the top-right corner of the chart.

How to add Trendline in Excel Charts

STEP 2: Click on the arrow next to Trendline

How to add Trendline in Excel Charts

STEP 3: Select Two Period Moving Average from the list

How to add Trendline in Excel Charts

STEP 4: Select the series SALES.

How to add Trendline in Excel Charts

Excel will show both the trendlines on the same chart.

How to add Trendline in Excel Charts

How to remove trendline in Excel

Removing the trendline in Excel Chart is extremely easy and a quick process. Let’s see how it is done.

STEP 1: Click on the Excel Chart.

How to add Trendline in Excel Charts

STEP 2: Click on the Chart Elements (“+” icon) on the top-right corner of the chart.

How to add Trendline in Excel Charts

STEP 3: From the dropdown, uncheck Trendline.

How to add Trendline in Excel Charts

Or, you could simply right click on the trendline and click on delete.

How to add Trendline in Excel Charts

Conclusion

In this tutorial, you have covered how to add trendline in Excel, the different types of trendlines, formatting the trendline, extending the trendline into future or past periods, adding multiple trendlines to the same chart, and finally how to remove them.

See also  Stock Line Chart Using Excel

You can learn more about Excel Charts by going through these blogs!

Further Learning:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

How to add Trendline in Excel Charts | MyExcelOnline

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, and Macros & VBA!

If you like this Excel tip, please share it
How to add Trendline in Excel Charts | MyExcelOnline How to add Trendline in Excel Charts | MyExcelOnline
How to add Trendline in Excel Charts | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...