Durjoy Paul
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.
Download Practice Workbook
Download this practice workbook below.
2 Suitable Examples to Create Stacked Bar Chart with Line in Excel
To create a stacked bar chart with a line in Excel, we have found two different examples from which you can get the proper overview of this topic. Our two examples mainly create a stacked bar chart with a line chart and 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 basically 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 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.
- 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.
- As a result, you will get the following stacked bar chart. See the screenshot.
- 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.
- Write down the following formula to calculate the average value using the AVERAGE function.
=AVERAGE(C6:E6)
- Then, press Enter to apply the formula.
- After that, drag the Fill Handle icon down the column.
- Then, we need to add this data to the stacked bar chart which 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.
- 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 treat as a bar chart and create another bar for this column.
- You will get the following result after adding new series. See the screenshot.
- Then, right-click on any of the bars.
- It will open up a new Context Menu.
- From there, select Change Series Chart Type option.
- 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.
- It will make our required stacked bar chart with a line chart. See the screenshot.
- 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.
Read More: How to Create Stacked Bar Chart with Dates in Excel (3 Examples)
Similar Readings
- How to Make a Simple Bar Graph in Excel (with Easy Steps)
- How to Make a Double Bar Graph in Excel (with Easy Steps)
- Excel Stacked Bar Chart with Subcategories (2 Examples)
- How to Make a Bar Graph in Excel without Numbers
- How to Plot Stacked Bar Chart from Excel Pivot Table (2 Examples)
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 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.
- 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.
- Finally, we get the following result. See the screenshot.
- 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 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.
- It will automatically create a series of lines on your chart. See the screenshot.
- 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.
Read More: How to Create Stacked Bar Chart for Multiple Series in Excel
Things to Remember
- To create a stacked bar chart with a line chart, we need to create an extra column which is basically 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.
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. Don’t forget to visit our Exceldemy page.
Related Articles
- How to Color Bar Chart by Category in Excel (2 Easy Methods)
- How to Show Number and Percentage in Excel Bar Chart
- Excel Bar Chart with Line Overlay (Create with Easy Steps)
- How to Make a Bar Graph in Excel with 2 Variables (3 Easy Ways)
- How to Show Percentage in Bar Chart in Excel (3 Handy Methods)
- Reverse Legend Order of Stacked Bar Chart in Excel (With Quick Steps)
- How to Create Construction Bar Chart in Excel (With Easy Steps)
