How to Add a Trendline to a Stacked Bar Chart in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

The article will show you how to add a Trendline to a Stacked Bar Chart in Excel. We often use trendlines to forecast sales or predict business progress. In Excel, we can easily use trendlines for Clustered Charts. However, there is no trendline option for the Stacked Charts in Excel. In that case, we have to use Series Lines which can be used as modified trendlines.

Table of Contents hide

Download Practice Workbook

2 Ways to Add a Trendline to a Stacked Bar Chart in Excel

1. Using Series Lines Feature to Add a Trendline to a Stacked Bar Chart

2. Applying VBA to Add a Trendline to a Stacked Bar Chart

Practice Section

Conclusion

Download Practice Workbook

Add Trendline to Stacked Bar Chart.xlsm

2 Ways to Add a Trendline to a Stacked Bar Chart in Excel

In the dataset, we have sales data of three shops for the first six months of the year.

I’ll show you two procedures for adding a stacked trendline to the Stacked Bar Chart that we will make based on these data.

1. Using Series Lines Feature to Add a Trendline to a Stacked Bar Chart

Usually, trendlines are available for the Clustered Charts. You cannot add a Trendline directly from the options for the Stacked Charts. Fortunately, Excel has some other cool and dynamic features to add Series Lines for Stacked Bar Charts which we can improvise as trendlines. Let’s go through the procedure below for a better understanding.

Steps:

First, we need to create the Stacked Bar Chart using our data. So we select the range of data which is B4:E10 and then we choose the Insert Tab.
After that, select Chart >> 2-D Bar >> Stacked Bar Chart.

After that, you will see the Stacked Bar Chart. Format the chart a little bit so that it looks neat and clean. In my case, I stretched its border to make it bigger.
You can see that the sales amount of these shops are classified by a particular set of colors: Blue, Red, and Gray are for Shop A, B, and C

Next, select the chart and then go to Chart Design >> Add Chart Element >> Lines >> Series Lines.

Thereafter, you will see the Series Lines in the Stacked Bar Chart. Also, I removed gridlines for a better view.

You may format these lines if you want. Right-click on any of the Series Lines and select Format Series Lines…

Thereafter, select the options from the Format Series Lines window to change the appearance of the Series Lines. In my case, I chose Solid Line as the Line option, increased the width to 25 pt and changed the Dash type.

Finally, our Stacked Bar Chart looks like the following image.

Thus you can add trendlines to a Stacked Bar Chart by using the Excel Series Lines feature.

2. Applying VBA to Add a Trendline to a Stacked Bar Chart

We can also add a Trendline to a Stacked Bar Chart using Excel VBA. Let’s go through the process below.

Steps:

First, go to Developer >> Visual Basic.

After that, select Insert >> Module.

Thereafter, the VBA Module will open. Type the following code in the Module.

Sub StackedBarChartTrendline()
    Range("B4:E10").Select
    ActiveSheet.Shapes.AddChart2(297, xlBarStacked).Select
    ActiveChart.SetSourceData Source:=Range("'vba'!$B$4:$E$10")
    ActiveChart.ChartGroups(1).HasSeriesLines = True
End Sub

The code uses the Range.Select property and xlBarStacked Enumeration to make a Stacked Bar Chart based on the range B4:E10.

Next, go back to your sheet and run the Macro.

After that, you will see the Stacked Bar Chart with Series Lines.

We format these lines following the procedure described in the previous method. We can consider them as Stacked Trendlines for this chart.

Thus you can add trendlines to a Stacked Bar Chart by using Excel VBA.

Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

Conclusion

Suffice to say, you will learn the basic tips and tricks to add a Trendline to a Stacked Bar Chart in Excel after reading this article. If you have any better or innovative methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.