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

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

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

**Similar Readings**

**How to Find the Equation of a Trendline in Excel (3 Suitable Ways)****Find Slope of Polynomial Trendline in Excel (with Detailed Steps)****Add Multiple Trendlines in Excel (With Quick Steps)****How to Make a Polynomial Trendline in Excel (2 Easy Ways)**

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

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

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

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

`=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.

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