How to Create Stacked Bar Chart with Line in Excel (2 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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. This article will focus on creating a stacked bar chart with a line in Excel. I hope you find this article informative and gather much knowledge regarding stacked bar charts in Excel.


Excel Stacked Bar Chart with Line: 2 Suitable Examples

To create a stacked bar chart with a line in Excel, we have found two different examples from which you can get a proper overview of this topic. Our two examples mainly create a stacked bar chart with a line chart and a series chart. The basic difference between them is that for the line chart we need to have an extra column of data whereas, for the series line, we don’t need any more columns.


1. Create Stacked Bar Chart with Line Chart

Our first example is based on creating a stacked bar chart with a line chart. Here, we create a line chart with a new column. Then, use the combo chart option to convert this new column into a line chart. To create a stacked bar chart with a line chart, we take a dataset that includes some salesperson and their sales amount in three different months.

To understand this example properly, you need to follow the following steps properly.

Steps

  • At first, select the range of cells B6 to E12.

  • Then, go to the Insert tab in the ribbon.
  • After that, from the Charts group, select the Recommended Charts option.

  • The Insert Chart dialog box will appear.
  • From there, select the Stacked Bar chart.
  • Finally, click on OK.

  • It will give us the following result. See the screenshot.

Excel Stacked Bar Chart with Line

  • Then, if you want to change the Chart Style, click on the Brush sign on the right side of the chart.
  • After that, select your preferred chart style.

Excel Stacked Bar Chart with Line

  • As a result, you will get the following stacked bar chart. See the screenshot.

Excel Stacked Bar Chart with Line

  • Next, we need to turn our focus toward creating a line chart.
  • Before creating any line chart, you need to add a new column in your dataset.
  • This column will be the required line chart.
  • As we take three months’ sales amounts as our dataset, now, we want to find out the average sales amount in our new column.
  • First, select cell F6.

=AVERAGE(C6:E6)

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon down the column.

Excel Stacked Bar Chart with Line

  • Then, we need to add this data to the stacked bar chart that was previously created.
  • To add these data to the stacked bar chart, first, right-click on the stacked bar chart.
  • A Context Menu will appear.
  • From there, click on the Select Data option.

Excel Stacked Bar Chart with Line

  • Then, the Select Data Source dialog box will appear.
  • From the Legend Entries(Series) section, select Add.

  • Then, the Edit Series dialog box will appear and set the Series name.
  • After that, select the newly created column reference in place of the Series values section,
  • Finally, click on OK.

  • After that, you’ll come to the Select Data Source box again.
  • Finally, click on OK.

  • The stacked bar chart will be treated as a bar chart and create another bar for this column.
  • You will get the following result after adding a new series. See the screenshot.

  • Then, right-click on any of the bars.
  • It will open up a new Context Menu.
  • From there, select the Change Series Chart Type option.

Excel Stacked Bar Chart with Line

  • Then, the Change Chart Type dialog box will appear.
  • Set all the series as stacked bars whereas for the Average series, change the chart type from stacked bar to the line chart.
  • Finally, click on OK.

Excel Stacked Bar Chart with Line

  • It will make our required stacked bar chart with a line chart. See the screenshot.

Excel Stacked Bar Chart with Line

  • To have a better view of the chart, we can format the axis.
  • Then, 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.

  • Finally, you’ll get the following solution. See the screenshot.

Excel Stacked Bar Chart with Line

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


2. Create Stacked Bar Chart with Series Lines

Our second example is mainly focused on creating a stacked bar chart with series lines. Here, the series line means those series through which we create the stacked bar chart. In this example, we just join those series points and create lines. To create a stacked bar chart with a series line, we take a dataset that includes some salesperson and their sales amount in three different months.

To create a stacked bar chart with the series lines, follow the following steps carefully.

Steps

  • At first, select the range of cells B6 to E12.

  • Then, go to the Insert tab in the ribbon.
  • After that, from the Charts group, select the Recommended Charts option.

  • The Insert Chart dialog box will appear.
  • From there, select the Stacked Bar chart.
  • Finally, click on OK.

  • It will give us the following result. See the screenshot.

Excel Stacked Bar Chart with Line

  • Then, if you want to change the Chart Style, click on the Brush sign on the right side of the chart.
  • After that, select your preferred chart style.

Excel Stacked Bar Chart with Line

  • Finally, we get the following result. See the screenshot.

Excel Stacked Bar Chart with Line

  • Then, click anywhere on the chart, and it will open up the Chart Design option on the ribbon.
  • Select the Chart Design option in the ribbon.
  • Then, from the Chart Layouts group, select the Add Chart Element drop-down option.

  • There you have lots of options, from there, select Lines.
  • After that, from the Lines section, select Series Lines.

Excel Stacked Bar Chart with Line

  • It will automatically create a series of lines on your chart. See the screenshot.

Excel Stacked Bar Chart with Line

  • Then, to change the series lines color, click on the lines.
  • It will open up the Format option on the ribbon.
  • After that, from the Shape Style group. Click on the small down arrow option.

  • It will open up several Theme Styles options.
  • From there, you can select your preferred theme.

  • Finally, 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, we need to create an extra column which is the line chart.
  • To create a stacked bar chart with a line chart, we need to utilize the combo chart where one column is a line chart and the others are the stacked bar chart.

Download Practice Workbook

Download this practice workbook below.


Conclusion

To create a stacked bar chart with a line in Excel, we have shown two different examples through which you can understand the whole scenario quite easily. Both of the examples are fairly easy to use in Excel. Moreover, it produces a proper overview of this topic. I think this article covers every possible detail to create a stacked bar chart with a line chart. If you have any questions, feel free to ask in the comment box.


Related Articles


<< Go Back to Stacked Bar Chart in Excel | Excel Bar Chart | 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.
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