How to create a combined clustered and stacked bar chart in Excel

When visually comparing the values of multiple items, if the value of one item is greater than the total values of all the other items, you may want to emphasize it. You can do so by displaying the largest value in one bar and grouping the smaller values together in another bar in a combined clustered and stacked bar chart.

Combined clustered and stacked bar chart

Note: This tutorial uses Excel 2013. In other Excel versions, there may be some slight differences in the described steps.

Watch the video below to see how to create a combined clustered and stacked bar chart in Excel. If you prefer written instructions, then continue reading.

To create a combined clustered and stacked bar chart in Excel, take the following steps:

1. Firstly, arrange the data in a way in which:

  • It is sorted from largest to smallest
  • The largest value that will be in a separate bar and the smaller values that will be grouped in a stacked bar are in two different columns

Arranging the data for combined clustered and stacked bar chart

2. Select the data that you will use to create a combined clustered and stacked bar chart.

Selecting the data for combined clustered and stacked bar chart

3. On the Insert tab of the ribbon, in the Charts group, click on the Insert Bar Chart button and in the opened menu, click on the second option, which is a Stacked Bar, among the 2-D Bar charts.

Inserting Stacked Bar chart among the 2-D Bar charts

4. Click Switch Row/Column in the Data group of the Design tab under Chart Tools to convert the inserted chart into a combined clustered and stacked bar chart.

Clicking Switch Row/Column in the Data group of the Design tab under Chart Tools to convert the inserted chart into a combined clustered and stacked bar chart

5. Reverse the order of the bars. To do that:

  • Click on the Chart Elements button. In the Chart Elements menu, hover your cursor over the Axes option and click on the arrow next to it.

Clicking on the arrow next to the Axes option in the Chart Elements menu in the combined clustered and stacked bar chart

  • In the opened submenu, click on More options.

Clicking on More Options for Axes in the Chart Elements menu in the combined clustered and stacked bar chart

  • This opens the Format Axis task pane. By default, the horizontal axis is selected. Click on the vertical axis to select it.

Clicking on the vertical axis to select it in the combined clustered and stacked bar chart

  • In the Axis Options section, tick the Categories in reverse order option.

Ticking the Categories in reverse order option in the Format Axis task pane in the combined clustered and stacked bar chart

6. Remove the Axes, the Chart title and the Gridlines by unticking these options in the Chart Elements menu.

Unticking the Axes, the Chart title and the Gridlines options in the Chart Elements menu in the combined clustered and stacked bar chart

7. Change the legend position to the right. To do that, hover your cursor over the Legend option in the Chart Elements menu and click on the arrow next to it. In the opened submenu, choose Right to place the legend at the right of the chart.

Clicking on the arrow next to the Legend option in the Chart Elements menu and choosing Right in the combined clustered and stacked bar chart

8. Finally, add data labels to the chart by ticking the Data Labels option in the Chart Elements
menu.

Ticking the Data Labels option in the Chart Elements menu in the combined clustered and stacked bar chart

So, these are all the steps you need to create a combined clustered and stacked bar chart in Excel.

What other combination charts do you use? Write in the comment section below.

Leave a Reply