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

A stacked bar chart can be defined as a chart that shows a comparison between the data. It has a unique ability to compare parts of a whole.

Method 1 – Create the Stacked Bar Chart with 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.

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

• You will get the following stacked bar chart.

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:

• Press Enter.

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

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

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

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

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

• 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.
• Then, set the Minimum value for your axis.

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

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

Method 2 – Create Stacked Bar Chart with Series Lines

In this example, we’ll join the series points to create lines within the stacked bar chart. Follow these steps carefully:

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.

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

• Finally, we get the following result.

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

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

• • To change the series lines’ color, click on the lines and use the Format option on the ribbon.
  • Then, 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.

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

• How to Make a Stacked Bar Chart in Excel
• Excel Stacked Bar Chart with Subcategories
• How to Create Stacked Bar Chart with Negative Values in Excel
• How to Plot Stacked Bar Chart from Excel Pivot Table
• How to Create Bar Chart with Multiple Categories in Excel
• How to Ignore Blank Cells in Excel Bar Chart

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