Multi-category chart or multi-level category chart is a chart type that has both main category and subcategory labels. This type of chart is useful when you have figures for items that belong to different categories.

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 multi-category chart in Excel. If you prefer written instructions, then continue reading.

Creating a multi-category chart in Excel

To create a multi-category chart in Excel, take the following steps:

1. Arrange the data in the following way: Enter main category names in the first column, subcategory names in the second column and the figure for each subcategory in the third column in the format shown below. For the purpose of this tutorial, suppose you have sales figures for different products of an online store for a month. The store’s products fall under 3 main categories: clothing, shoes and accessories.

2. Select the data and 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 first option, which is Clustered Bar, among the 2-D Bar charts.

This inserts a multi-category chart into the worksheet.

Note: If some subcategory labels are missing, increase the height of the chart until it shows all subcategory labels.  

3. Decrease the gaps between the bars. To do that:

• Double-click on the bars to open the Format Data Series task pane.

• In the Format Data Series task pane, change the gap width to 50% by either typing 50 in the Gap Width box and pressing Enter on the keyboard or moving the slider to the left.

4. Add data labels to the chart by checking the Data Labels option in the Chart Elements menu.

5. Remove the Horizontal Axis, the Chart Title and the Gridlines by unchecking these options in the Chart Elements menu.

6. Finally, to make the chart more readable, add some blank space between the categories in the chart, give different colors to the bars of each category and change the outline colors of the chart area, the vertical axis and the bars and the text color of the chart to black.

To add blank space between the categories in the chart:

• Insert two blank rows between the data for each category.

• In the first cell of the first row of each inserted pair of blank rows, type a space character by pressing the Spacebar on the keyboard.

To give different colors to the bars of each category:

• Click on the bars to select them and then click on the first bar of the second category.

• On the Format tab under Chart Tools choose a color for that bar in the Shape Fill drop down menu. Continue to select each next bar of the second category by pressing the Right key on the keyboard and then press F4 to repeat the fill action. Alternatively, click on each next bar and change its fill color manually.

• Repeat the process for all remaining categories until the bars of each category have different colors.

To change the outline colors of the chart area, the vertical axis and the bars to black:

• With the chart area, the vertical axis and the bars selected one by one, on the Format tab under Chart Tools choose Black in the Shape Outline drop down menu.

To change the text color of the chart to black:

• With the chart selected, on the Format tab under Chart Tools choose Black in the Text Fill drop down menu.

Converting a multi-category bar chart into a multi-category column chart in Excel

The created chart is a multi-category bar chart. You can create a multi-category column chart the same way. Simply click on the Insert Column Chart button instead of the Insert Bar Chart button in the Charts group of the Insert tab of the Ribbon after selecting the data in the second step and in the opened menu, click on the first option, which is Clustered Column, among the 2-D Column charts.

You can convert the already created multi-category bar chart into a multi-category column chart as well. To do that:

• With the chart selected, click on the Design tab under Chart Tools and then click on the Change Chart Type button.

• In the Change Chart Type dialog box, in the left pane, click Column and then click OK.

Converting a multi-category chart into an ordinary chart in Excel

You can convert a multi-category chart into an ordinary chart without main category labels as well. To do that:

• Double-click on the vertical axis to open the Format Axis task pane.

• In the Format Axis task pane, scroll down and click on the Labels option to expand it.

• In the Labels section, uncheck the Multi-level Category Labels option.
  

To convert it back into a multi-category chart, simply check the Multi-level Category Labels option again.

So, this is how you create a multi-category chart in Excel.

Which multi-category chart do you prefer – multi-category bar chart or multi-category column chart and why? Write in the comment section below.