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

A trend chart is a chart that shows the general pattern of data over time. The trendline is used to represent the future of the data. In Microsoft Excel, you can add trendlines to your chart. The trendline can be a straight or curved line showing the direction of the usual values. This article will show you how to create a monthly trend chart in Excel. I hope you find this article informative and gain much knowledge regarding the trend chart.


Download the Practice Workbook

Download the practice workbook below.


4 Easy Methods to Create Monthly Trend Chart in Excel

To create a monthly trend chart in Excel, we have found four different methods through which you can have explicit knowledge of creating a monthly trend chart in Excel. While creating a monthly trend chart in Excel, we cover several Excel functions and also utilize a line chart with Excel shapes. All of these methods are fairly easy to understand and far easier to use.


1. Applying FORECAST.LINEAR Function

Our first method is to use the FORECAST.LINEAR function. The FORECAST.LINEAR function provides the future values along with a linear trendline. To show the method properly, we take a dataset that includes 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.

To apply this formula, follow the steps properly.

Steps

  • First, create a new column where we want to predict future sales.

  • Then, select cell D10.

  • After that, write down the following formula.
=FORECAST.LINEAR(B14,$C$5:$C$13,$B$5:$B$13)

How to Create Monthly Trend Chart in Excel

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon down the column.

How to Create Monthly Trend Chart in Excel

  • Before using the scatter chart, set the sales value of month 9 into cell D9.

  • Then, select the range of cells B4 to 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.

  • As a result, it will give us the following result. See the screenshot.

How to Create Monthly Trend Chart in Excel

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

How to Create Monthly Trend Chart in Excel

  • Then, the Add Trendline dialog box will appear.
  • Select the Sales option from the Add a Trendline based on Series section.
  • 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.

  • Finally, we’ll get the following result. See the screenshot.

How to Create Monthly Trend Chart in Excel

Read More: How to Extrapolate Trendline in Excel (4 Quick Methods)


2. Using FORECAST.ETS Function

Our next method is to use the FORECAST.ETS function. In this method, the FORECAST.ETS provides the future values by using exponential triple smoothing.   To show the method properly, we take a dataset that includes months and their corresponding sales. Here, we have sales for 9 months. After using the FORECAST.ETS function, we will predict the future sales along with exponential triple smoothing.

To apply this formula, follow the steps properly.

Steps

  • First, create a new column where we want to predict future sales.

  • Then, select cell D10.
  • After that, write down the following formula.
=FORECAST.ETS(B14,$C$5:$C$13,$B$5:$B$13,1)

How to Create Monthly Trend Chart in Excel

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon down the column.

How to Create Monthly Trend Chart in Excel

  • Before using the scatter chart, set the sales value of month 9 into cell D9.

How to Create Monthly Trend Chart in Excel

  • Then, select the range of cells B4 to 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.

  • As a result, it will give us the following result. See the screenshot.

How to Create Monthly Trend Chart in Excel

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

How to Create Monthly Trend Chart in Excel

  • Then, the Add Trendline dialog box will occur.
  • Select the Sales option from the Add a Trendline based on Series section.
  • 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.

  • Finally, we’ll get the following result. See the screenshot.

How to Create Monthly Trend Chart in Excel


Similar Readings


3. Using TREND Function

The TREND function is mainly used to calculate the linear trendline. Using this formula we will create a monthly trend chart. To show this method, we take a dataset that includes sales for 12 months. We need to calculate the trend using the TREND function. After that, we will create a line chart with this.

Steps

  • First, create a new column named Trend.

  • 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 Monthly 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.

How to Create Monthly Trend Chart in Excel

  • 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.

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

  • As a result, it will give us the following result. See the screenshot.

How to Create Monthly 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 will get the following results. See the screenshot.

How to Create Monthly Trend Chart in Excel

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


4. Utilizing Line Chart with Excel Shapes

We can create the monthly 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.

Follow the steps carefully.

Steps

  • First, create some new columns with some random values.
  • Basically, this is created for the modification of the chart.

How to Create Monthly 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 result. See the screenshot.

How to Create Monthly 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. See the screenshot.

  • 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 is a must because we need to use this shape in our chart.

  • 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.

How to Create Monthly Trend Chart in Excel

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

How to Create Monthly 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.

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

  • 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 Monthly Trend Chart in Excel

  • It will give us the following result.

How to Create Monthly Trend Chart in Excel

  • Then, change column F and set the value of column C.

  • After that, delete the values of column G, column H, and column I.

  • 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())

How to Create Monthly Trend Chart in Excel

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon down the column.

How to Create Monthly 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.

  • Then, drag the Fill handle icon down the column.

How to Create Monthly 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())

How to Create Monthly Trend Chart in Excel

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

How to Create Monthly Trend Chart in Excel

🔎 Breakdown of the Formula

⟹ IF(F6>F5,F6,NA()): It denotes 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 means that if the sales are higher than the previous month, it will return the sale of this month, or else it will return nothing,

⟹ IF(F6<F5,F6,NA()): It denotes 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. It means that if the sales are lower than the previous month, it will return the sale of this month, or else it will return nothing,

⟹ IF(F6=F5,F6,NA()):  It denotes 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. It means that if the sales are equal to the previous month, it will return the sale of this month, or else it will return nothing

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

How to Create Monthly Trend Chart in Excel

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

How to Create Monthly Trend Chart in Excel

  • Finally, you’ll get the following result. See the screenshot.

How to Create Monthly Trend Chart in Excel

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


Conclusion

We have shown four different approaches through which you can have a proper overview of how to create a monthly trend chart in Excel. In these four methods, we utilize three Excel functions. All of these methods give a fruitful result on the trend chart. I hope you find this article really interesting and gather more knowledge on this topic. We try to cover all possible questions, if you have any further questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo