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

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.


Download Practice Workbook


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.

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 Calculate Trend Analysis in Excel (3 Easy Methods)


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: Create Monthly Trend Chart in Excel (4 Easy Ways)


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


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.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo