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.
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
2. Select the data that you will use to create a 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.
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.
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.
- In the opened submenu, click on More options.
- This opens the Format Axis task pane. By default, the horizontal axis is selected. Click on the vertical axis to select it.
- In the Axis Options section, tick the Categories in reverse order option.
6. Remove the Axes, the Chart title and the Gridlines by unticking these options in the Chart Elements menu.
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.
8. Finally, add data labels to the chart by ticking the Data Labels option in the Chart Elements
menu.
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.