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

What is a Stacked Bar Chart?

A 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

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

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.