How to Create Trend Chart in Excel (4 Easy Methods)

Charts that display the general pattern of data over time are trend charts. Trendlines are a useful tool for analyzing data in Excel. Excel users use this feature mainly for forecasting by analyzing the data. If you are looking for special tricks to create a trend chart in Excel, you’ve come to the right place. There are two ways to create a trend chart in Excel. This article will discuss every step of these methods to create a trend chart in Excel. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.


Overview of Trend Chart

A trend chart represents the general pattern of data over time. The trendline represents the future of the data. It can be a straight or curved line showing the direction of the usual values. It can be expressed through column charts, line charts, scattered charts, etc. The number of trendlines depends on the number of data types selected in excel. Trendlines are very useful for investors or traders to give them a good direction for their business evaluation. Often traders or investors draw trendlines on their graphs and try to relate them with a particular equation or series within a certain price range evaluation in the market. The resulting line gives them the proper evaluation of the market price range or investment direction for their business growth. In the following picture, we can see a trend chart.

Overview of Trend Chart


4 Easy Methods to Create Trend Chart in Excel

In the following section, we will use four effective and tricky methods to create a trend chart in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Applying FORECAST.LINEAR Function

Here, we will demonstrate how to create a trend chart in Excel. To do this we will use the FORECAST.LINEAR function. The FORECAST.LINEAR function provides the future values along with a linear trendline. For demonstration purposes, we take a dataset containing months and their corresponding sales. Here, we have sales for 9 months. After using the FORECAST.LINEAR function, we will predict the future sales along with a linear trendline. Let’s walk through the following steps to create a trend chart in Excel.

Applying FORECAST.LINEAR Function

📌 Steps:

  • First of all, create a new column where we want to predict future sales.
  • Next, set the sales value of month 9 into cell D9. Then, select cell D10.
  • After that, write down the following formula.

=FORECAST.LINEAR(B14,$C$5:$C$13,$B$5:$B$13)

  • Then, press Enter to apply the formula.

How to Create Trend Chart in Excel

  • After that, drag the Fill Handle icon to fill the other cells with the formula.

  • Then, select the range of cells B4:D16.
  • Go to the Insert tab in the ribbon.
  • Then, from the Charts group, select Insert Scatter or Bubble chart.
  • It will give us several options.
  • Select Scatter with Straight Lines and Makers.

How to Create Trend Chart in Excel

  • Therefore, it will give us the following chart as shown below.

  • Afterward, select the Plus (+) icon on the right side of the chart.
  • From there, click on Trendline.

How to Create Trend Chart in Excel

  • Then, the Add Trendline dialog box will appear.
  • Select the Sales option from the Add a Trendline based on Series.
  • Finally, click on OK.

How to Create Trend Chart in Excel

  • As a result, a linear trendline will occur.
  • To change the Chart Style, click on the Brush icon on the right side of the chart.
  • Then, select any of the chart styles.

  • Finally, we’ll get the following trend chart in Excel.

How to Create Trend Chart in Excel

Read More: How to Add Trendline in Excel Online (with Easy Steps)


2. Using FORECAST.ETS Function

Here, we will use another quick and effective way to create a trend chart in Excel. Here, we are going to use the FORECAST.ETS function. In this method, the FORECAST.ETS provides the future values by using exponential triple smoothing.  For demonstration purposes, we take a dataset containing months and their corresponding sales. Here, we have sales for 9 months. The FORECAST.ETS function will be used along with exponential triple smoothing to predict future sales. Let’s walk through the following steps to create a trend chart in Excel.

Using FORECAST.ETS Function

📌 Steps:

  • First of all, create a new column where we want to predict future sales.
  • Next, set the sales value of month 9 into cell D9. Then, select cell D10.
  • After that, write down the following formula.

=FORECAST.ETS(B14,$C$5:$C$13,$B$5:$B$13,1)

  • Then, press Enter to apply the formula.

How to Create Trend Chart in Excel

  • After that, drag the Fill Handle icon to fill the other cells with the formula.

  • Then, select the range of cells B4:D16.
  • Go to the Insert tab in the ribbon.
  • Then, from the Charts group, select Insert Scatter or Bubble chart.
  • It will give us several options.
  • Select Scatter with Straight Lines and Makers.

How to Create Trend Chart in Excel

  • Therefore, it will give us the following chart as shown below.

  • Afterward, select the Plus (+) icon on the right side of the chart.
  • From there, click on Trendline.

How to Create Trend Chart in Excel

  • Then, the Add Trendline dialog box will appear.
  • Select the Sales option from the Add a Trendline based on Series.
  • Finally, click on OK.

  • As a result, a linear trendline will occur.
  • To change the Chart Style, click on the Brush icon on the right side of the chart.
  • Then, select any of the chart styles.

How to Create Trend Chart in Excel

  • Finally, we’ll get the following trend chart in Excel.

Read More: How to Create Monthly Trend Chart in Excel (4 Easy Ways)


Similar Readings


3. Use of TREND Function

Here, we will use another method to create a trend chart in Excel. The TREND function is mainly used to calculate the linear trendline. Using this formula we will create a trend chart. For demonstration purposes, we take a dataset containing months and their corresponding sales. We need to calculate the trend using the TREND function. After that, we will create a line chart with this. Let’s walk through the following steps to create a trend chart in Excel.

Use of TREND Function

📌 Steps:

  • First, create a new column named Future Sales.
  • Then, select the range of cells D5 to D16.
  • Write down the following formula in the formula box.

=TREND(C5:C16,B5:B16)

How to Create Trend Chart in Excel

  • As this is an array formula, so, to apply the formula, you need to press Ctrl+Shift+Enter.
  • It will give us the following result.

  • Then, select the range of cells B4 to D16.
  • Go to the Insert tab in the ribbon.
  • Then, from the Charts group, select Recommended Charts.

How to Create Trend Chart in Excel

  • The Insert Chart dialog box will occur.
  • From there, select Line chart.
  • Finally, click on OK.

  • As a consequence, it will give us the following chart.

How to Create Trend Chart in Excel

  • To change the Chart Style, click on the Brush icon on the right side of the chart.
  • Then, select any of the chart styles.

  • Finally, we’ll get the following trend chart in Excel.

How to Create Trend Chart in Excel

Read More: How to Calculate Trend Analysis in Excel (3 Easy Methods)


4. Utilizing Line Chart with Excel Shapes

Here, we will demonstrate another way to create a trend chart in Excel. We can create a trend chart in Excel using a line chart with Excel shapes. Here, we basically create an up, down, and equal trend chart. To show this method, we take a dataset that includes several months and their sales percentage. We want to calculate how the sales percentage behaves over the 12 months. Let’s walk through the following steps to create a trend chart in Excel.

Utilizing Line Chart with Excel Shapes

📌 Steps:

  • First of all, create some new columns with some random values.
  • In essence, this is used to modify charts.

How to Create Trend Chart in Excel

  • Then, select the range of cells E4 to I16.
  • Go to the Insert tab in the ribbon.
  • Then, from the Charts group, select Insert Line or Area Chart drop-down option.
  • From the Line or Area Chart, select the Line with Markers chart option.

  • It will give us the following chart.

How to Create Trend Chart in Excel

  • Then, we need to create some shapes for up, down and an equal amount of sales.
  • Go to the Insert tab in the ribbon.
  • Then, select the Illustrations drop-down option.
  • From the Shapes drop-down option, select the up arrow for sales up and select the down arrow for sales down.
  • Then, for the equal percentage of sales, select the oval sign.

  • It will give us the following results.

  • Then, select any shape, and it will open up the Shape Format tab in the ribbon.
  • Go to the Shape Format tab in the ribbon.
  • Then, from the Size group, change the size of the shape.
  • It’s essential because we’re going to use it in our chart.

How to Create Trend Chart in Excel

  • After that, go to the Shape Format tab in the ribbon
  • Then, from the Shape Style group, select Shape Fill.
  • For the up arrow, set Shape Fill as green.
  • For the down arrow, set Shape Fill as red.
  • For the oval shape, set the Shape Fill as yellow.

  • Then, copy the up arrow shape.
  • After that, click on the markers for the up column. It will select the markers.
  • Then, press Ctrl+V to paste the up arrow.
  • It will give us the following results.

  • Then, do the same thing for the down arrow and oval shape.
  • It will give you the following result.

How to Create Trend Chart in Excel

  • Then, remove the line from the Up, Down, and Equal series.
  • To remove the line, double on the line.
  • It will open the Format Data Series dialog box.
  • Then, from the Line section, select No Line.

How to Create Trend Chart in Excel

  • Do it for the other two, you’ll get the following result.

  • Now, we want to remove the markers from the Sales series.
  • Double-click on the sales line with markers.
  • Then, it will open up the Format Data Series dialog box.
  • Select the Marker.
  • After that, in the Marker Options section, click on None.

How to Create Trend Chart in Excel

  • It will give us the following result.

  • Then, change column F and set the value of column C.
  • After that, delete the values of column G, column H, and column I.

How to Create Trend Chart in Excel

  • In the first month, we set the sales percentage as up. So in cell G5, we set 40%.
  • For the other 11 months, we need to apply some conditions.
  • first, select cell G6.
  • Write down the following formula using the IF and NA functions.

=IF(F6>F5,F6,NA())

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon to fill the other cells with the formula.

How to Create Trend Chart in Excel

  • As we set the first month as up sales, so, the down sales will be blank.
  • Select cell H6.
  • Write down the following formula.

=IF(F6<F5,F6,NA())

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon to fill the other cells with the formula.

How to Create Trend Chart in Excel

  • As we set the first month as up sales, so, the equal sales will be blank.
  • Select cell I6.
  • Write down the following formula.

=IF(F6=F5,F6,NA())

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon to fill the other cells with the formula.

How to Create Trend Chart in Excel

  • It will give us the following solution in the chart.

  • Then, right-click on the markers.
  • A Context Menu will occur. From there, select Add Data Labels.

  • Then, you have to customize the chart based on your preference. Finally, you’ll get the following result.

How to Create Trend Chart in Excel

🔎 How Does the Formula Work?

  • IF(F6>F5,F6,NA())

 It indicates that if cell F6 is greater than cell F5, then, it will return the value of cell F6. Otherwise, it will return that no value is available. It will return the sale of this month if the sales are higher than the previous month, or nothing if the sales are lower.

  • IF(F6<F5,F6,NA())

It indicates that if cell F6 is lower than cell F5, then, it will return the value of cell F6. Otherwise, it will return that no value is available. The sale of this month will be returned if it is lower than the previous month, otherwise, it will not be returned.

  • IF(F6=F5,F6,NA())

It indicates that if cell F6 is equal to cell F5, then, it will return the value of cell F6. Otherwise, it will return that no value is available. In other words, it will return the sales of this month if the sales of the previous month are equal, or nothing if the sales are lower or higher than the previous month.

Read More: How to Calculate Trend Percentage in Excel (With Easy Steps)


Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to create a trend chart in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo