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.


How to Add a Trendline to a Stacked Bar Chart in Excel: 2 Easy Ways

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

how to add a trendline to a stacked bar chart in excel

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

how to add a trendline to a stacked bar chart in excel

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

how to add a trendline to a stacked bar chart in excel

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

how to add a trendline to a stacked bar chart in excel

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.

Read More: How to Create Trend Chart in Excel


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.

how to add a trendline to a stacked bar chart in excel

  • 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

how to add a trendline to a stacked bar chart in excel

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.

how to add a trendline to a stacked bar chart in excel

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.

Read More: How to Create Monthly Trend Chart in Excel


Practice Section

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

how to add a trendline to a stacked bar chart in excel


Download Practice Workbook


Conclusion

Suffice it to say, that 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, questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo