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.

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.

Watch Video – Create Monthly Trend Chart in Excel

1. Applying FORECAST.LINEAR Function to Create Monthly Trend Chart in Excel

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)

Then, press Enter to apply the formula.

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

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.

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

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.

Read More: How to Create Trend Chart in Excel

2. Using Excel FORECAST.ETS Function to Create Monthly Trend Chart

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

Then, press Enter to apply the formula.

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

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.

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

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.

3. Using TREND Function to Create Monthly Trend Chart in Excel

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)

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.

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.

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.

4. Combining Line Chart with Excel Shapes to Make Monthly Trend Chart

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

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.

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.

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

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.

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.

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 down the column.

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.

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.

Then, drag the Fill Handle icon down the column.

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

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

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

Download the Practice Workbook

Download the practice workbook below.

Create Monthly Trend Chart.xlsx

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.

<< Go Back To Trendline in Excel | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

1. Applying FORECAST.LINEAR Function to Create Monthly Trend Chart in Excel

2. Using Excel FORECAST.ETS Function to Create Monthly Trend Chart

3. Using TREND Function to Create Monthly Trend Chart in Excel

4. Combining Line Chart with Excel Shapes to Make Monthly Trend Chart

Conclusion

Related Articles

Leave a reply Cancel reply