How to Add a Trendline in MS Excel

by

This tutorial will guide you through the sequential steps of adding a trendline to an MS Excel chart. A trendline shows a pattern or trend on a graph. 

Trendlines are used in many real-world situations. They are popular with investors, sales professionals, traders, and other related professionals. Trendlines analyze financial and sales information to show upward or downward trends. This helps with data visualization. 

Before explaining step-by-step how to add a trendline, you should  know how to create a line chart in Excel. 

3 Methods of adding a trendline in Microsoft Excel: 

  • Use the Chart Design Group
  • Use Add Charts Elements
  • Right-click Menu

Let us begin.

Now you are ready to add a trendline to your Line chart. 

Method #1: Use the Chart Design Group

Adding a trendline to a chart will help you understand the overall trend, including ups and downs in data. In the Chart Design Group, there are many options to create and change charts.

  1. Select the Line chart by clicking on it.
  2. In the top menu bar, click on the Chart Design tab.
  3. At the top left corner of the Charts Layout group, click Add Chart Elements.
  4. From the drop-down options, move your mouse down to Trendline. This will display the trendline options.
  1. Select Linear. The linear trendline is added to the chart. 

The Linear option is a straight line that shows the increase or decrease in the sales
            over a period of 12 months at a steady rate. 

In the image above you will note the fluctuating data points, where some months show an increase in sales.

To change the linear trendline, click on More Trendline Options from the Add Chart Elements.

To format the trendline, refer to the section Format the Trendline.

Method #2: Use Add Chart Elements

In this section, we will plot a line graph using single column data – Sales, and add a trendline to it.

This example uses the same sales spreadsheet as described initially. Follow the steps as explained in the Preparation section, except only select Column B (B2:B14) to plot the Line chart as displayed below. 

The X-axis displays numbers 1 to 12 because we have 12 rows in our source data table.

The Y-axis displays the sales value.

  1. Click on the chart.
  2. To the right, three icons will appear.
  3. Click the top plus icon.
  4. A selection of Chart Elements will appear.
  5. Select Trendline.
  6. The trendline is added, as illustrated below.

Note: The trendline has been formatted with solid style and color for a distinct visual display.

To format the trendline, refer to the section Format the Trendline.

Method #3: Right-click Menu

In Excel, the right-click menu always provides quick access to functionalities needed.

This example uses the same sales spreadsheet as described initially. Follow the steps as explained in the Preparation section, except only select Column B (B2:B14) to plot the Line chart as displayed below. 

  1. Select the Line graph, and right-click on the series.
  2. From the drop-down menu that opens, choose Add Trendline.
  3. The Linear trendline option is selected by default as illustrated below.

To format the trendline, refer to the section Format the Trendline.

Format the Trendline

The Format Trendline pane on the right displays formatting options. Use this pane to change the appearance of your trendline. All options are self-explanatory, but we will mention a few here:

  • To change the name, select the radio button Custom under Trendline name, and type a new name in the text box.
  • Select the Fill & Line option to format the line of your trendline. 

You can make it a Solid or Gradient line, and change the Color, Transparency, Width, and other components.

  • Special effects can be added by selecting Shadow, Glow, or Soft Edges.
  • Trendline Options change the type of trendline. 

The options include the extra styles – Polynomial, Power, and Logarithmic not found in the previous sub-menu. Test the different options to see the changes on your trendline.

  • The Forecast option is used to predict future values. 

Conclusion

Whatever your business type, data visualization is a must. A well-planned graph and a perfect trendline provide a powerful visual for a data trend. To make your graph stand out, you must identify your goals to present the data, collect data and choose the right type of chart.

Play around with the different styles and formatting options to perfect your trendline. 

Leave a Comment