How to Plot Stacked Bar Chart from Excel Pivot Table (2 Examples)

Stacked charts are important to compare the contribution of each parameter in the total. In a stacked bar chart, all the parameter column values are stacked on top of each other in horizontal bars. Usually, the purpose is the same as stacked column charts- they help recognize patterns or trends more easily than a table containing raw data. But sometimes, a bar chart may be more presentable compared to column charts depending on the situation. This article will focus on creating a stacked bar chart from an Excel pivot table, all of which are also applicable if you want to create a stacked column bar chart.

Stacked Bar Chart from Excel Pivot Table: 2 Suitable Examples

For demonstration, we will mainly focus on two scenarios to plot stacked bar charts from an Excel pivot table. One is to plot a stacked bar chart only using the pivot table and the other one’s aim is to plot a clustered stacked bar chart.

1. Plot Stacked Bar Chart from Excel Pivot Table

For the first example, we are going to use the following dataset.

To plot a stacked bar chart using pivot tables in Excel, we first need to create a pivot table from this dataset. You can create a pivot table by following these steps.

Steps to Create Pivot Table:

First of all, select the whole dataset (the range B4:E10).
Then go to the Insert tab on your ribbon.
After that, select PivotTable from the Tables
Thus a drop-down list will appear. Now select the From Table/Range option from the list.

Next, select your preferred options from the PivotTable from table or range box and click on OK.

Now a window called the PivotTable Fields will appear on the right of the Excel spreadsheet- either on the existing page or on the new one depending on the option you selected from the previous box.
Next, click and drag the Month field to the Rows field and release it.

Similarly, click and drag Store 1, Store 2, and Store 3 to the Values field. The PivotTable Fields window will now look like this.

And the pivot table will now appear on the spreadsheet, like below.

Now follow these steps to plot a stacked bar chart using this pivot table in Excel.

Steps to Create Stacked Bar Chart:

First, either select the whole pivot table or any of the cells in the pivot table.
Then go to the Insert tab on your ribbon.
Now click on the Insert Column or Bar Chart icon from the Charts group.

At this instant, a drop-down menu will appear. Select the Stacked Bar option from it.

As a result, a stacked bar chart will appear on the Excel spreadsheet from the pivot table.

Now modify it to your liking to make it more presentable.

Thus you can create a stacked bar chart from a pivot table in Microsoft Excel.

2. Plot Clustered Stacked Bar Chart from Excel Pivot Table

As can be seen from the previous example, stacked bar charts stack the values of parameters on top of each other. On the other hand, a clustered bar chart has the same orientation but puts the horizontal columns beside their equivalent ones. These are two different graphs. But for some datasets, a combination of both can be helpful. For example, let’s take a dataset like the following one.

Creating a combination of clustered and stacked bar charts will be very helpful to illustrate the weekly sales in each month of each store. In this section, we will try to plot that.

But first, we need to create a pivot table from the dataset. We will be following a similar procedure to the previous example for this purpose.

Steps to Create a Pivot Table:

First, select the whole dataset (the range B4:E20).
Second, go to the Insert tab on your ribbon.
Then select PivotTables from the Tables
After that, select From Table/Range from the drop-down menu.

Now select your preferred options from the PivotTable from table or range box and then click on OK.

As a result, the PivotTable Fields window will appear on the right of either the existing worksheet or in a new spreadsheet depending on the option you have selected in the previous step.

Now click and drag the Stores and Month fields to the Rows area and release.

Next, click and drag the Week field to the Columns area and the Sales field to the Values The window should now look like this.

And the pivot table will automatically emerge on the spreadsheet.

Moving on, we are going to create a clustered stacked bar chart now using this pivot table in Excel. Excel doesn’t have the direct option to plot such a graph, so it can be a bit tricky. Follow these steps to easily plot a clustered stacked bar chart from this.

Steps to Create Clustered Stacked Bar Chart:

To begin with, select the whole pivot table or a cell from the table.
Then go to the Insert tab on your ribbon and select the Insert Column or Bar Chart icon from the Charts group.

Then select the Stacked Bar from the drop-down menu.

As a result, a stacked bar chart will appear on top of the spreadsheet.

Next, right-click on any of the columns. Then select Format Data Series from the context menu.

At this instant, the Format Data Series window will appear on the right side of the spreadsheet. Go to the Series Options tab in it.
Then change the Gap Width to a lower value like 50%. You can find this option under the Series Options section.

Once you have completed the previous step, the chart will automatically change from a stacked bar to a clustered stacked bar.

Finally, modify the graph to your liking and make it more presentable.

This way you can create a clustered stacked bar chart from a pivot table in Excel.

Download Practice Workbook

You can download the workbook with datasets, pivot tables, and the charts used in the demonstration from the download link below. Download and practice yourself while you go through the tutorials.

Stacked Bar Chart from Pivot Table.xlsx

Conclusion

These are the different situations where you can create a stacked bar from a pivot table in Excel and their tutorial. Hopefully, you will be able to plot both types of stacked bar charts in Excel from pivot tables. I hope you found this guide helpful and informative. If you have any questions or suggestions, please let us know in the comments below.

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

Get FREE Advanced Excel Exercises with Solutions!

How to Plot Stacked Bar Chart from Excel Pivot Table (2 Examples)

Stacked Bar Chart from Excel Pivot Table: 2 Suitable Examples

1. Plot Stacked Bar Chart from Excel Pivot Table

2. Plot Clustered Stacked Bar Chart from Excel Pivot Table

Conclusion

Related Articles

Leave a reply Cancel reply