How to Create a Stacked Bar Chart with a Line in Excel (2 Methods)

What is a Stacked Bar Chart?

stacked bar chart is a type of bar chart that displays multiple data points on top of each other within the same bar. This allows you to compare the total values across different categories as well as the individual components that make up those totals.

Here’s a breakdown of its key features:

  • Primary Categorical Variable: Each bar represents a category of the primary variable.
  • Secondary Categorical Variable: Each bar is divided into segments that represent different subcategories of the secondary variable.
  • Comparison: You can compare both the total length of the bars (overall totals) and the individual segments within each bar (subcomponent contributions).

Method 1 – Create a Stacked Bar Chart with a Line Chart

  • Select the range of cells B6 to E12.

  • Go to the Insert tab in the ribbon.
  • From the Charts group, select the Recommended Charts option.

  • In the Insert Chart dialog box, choose the Stacked Bar chart.
  • Click OK to generate the chart.

  • It will give us the following result.

Excel Stacked Bar Chart with Line

  • If you want to change the Chart Style, click on the Brush sign on the right side of the chart and select your preferred chart style.

Excel Stacked Bar Chart with Line

  • You will get the following stacked bar chart.

Excel Stacked Bar Chart with Line

Create the Line Chart:

  • Add a new column to your dataset (cell F6).

  • In cell F6, calculate the average sales amount for the three months using the AVERAGE function:
=AVERAGE(C6:E6)

  • Press Enter.

  • Drag the Fill Handle down the column to apply the formula.

Excel Stacked Bar Chart with Line

  • Right-click on the stacked bar chart and choose Select Data.

Excel Stacked Bar Chart with Line

  • In the Select Data Source dialog box, click Add from the Legend Entries (Series) section.

  • Set the Series name and select the newly created column reference for the Series values.
  • Click OK.

  • The Select Data Source box will appear again, click OK.

  • The stacked bar chart will be regarded as a standard bar chart, creating an additional bar for this column.
  • You will get the following result after adding a new series.

  • Right-click on any of the bars and choose Change Series Chart Type.

Excel Stacked Bar Chart with Line

  • Set all series as stacked bars, except for the Average series, which should be changed to a line chart.
  • Click OK.

Excel Stacked Bar Chart with Line

  • The stacked bar chart with a line chart will be created.

Excel Stacked Bar Chart with Line

  • Format the axis for a better view.
    • Double-click on the right of the chart axis.
    • It will open up the Format Axis dialog box.
    • Set the Minimum value for your axis.

  • This will give you the required stacked bar chart with a line.

Excel Stacked Bar Chart with Line

Read More: How to Create Stacked Bar Chart for Multiple Series in Excel


Method 2 – Create a Stacked Bar Chart with Series Lines

In this method, we’ll join the series points to create lines within the stacked bar chart.

Steps

  • Create the Stacked Bar Chart with Series Lines:
    • Select the range of cells B6 to E12.

    • Go to the Insert tab in the ribbon.
    • From the Charts group, choose the Recommended Charts option.

    • In the Insert Chart dialog box, select the Stacked Bar chart.
    • Click OK to generate the chart.

It will give us the following result.

Excel Stacked Bar Chart with Line

    • If you want to change the Chart Style, click the Brush sign on the right side of the chart and select your preferred style.

Excel Stacked Bar Chart with Line

  • We get the following result.

Excel Stacked Bar Chart with Line

  • Add Series Lines:
    • Click anywhere on the chart to open the Chart Design option on the ribbon.
    • Select the Chart Design option.
    • From the Chart Layouts group, choose the Add Chart Element drop-down.

    • Select Lines.
    • From the Lines section, choose Series Lines.

Excel Stacked Bar Chart with Line

    • This will automatically create a series of lines on your chart.

Excel Stacked Bar Chart with Line

    • To change the series lines’ color, click on the lines and use the Format option on the ribbon.
    • To change the series lines color, click on the lines.

    • You can select your preferred theme from the Theme Styles options.

    • You will get your required stacked bar chart with series lines.

Excel Stacked Bar Chart with Line

Read More: How to Create Stacked Bar Chart with Dates in Excel


Things to Remember

  • To create a stacked bar chart with a line chart, add an extra column for the line chart.
  • Utilize a combo chart where one column represents the line chart and the others represent the stacked bar chart.

Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Stacked Bar Chart in ExcelExcel Bar ChartExcel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo