Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

Create a multi-level category chart in Excel

A multi-level category chart can display both the main category and subcategory labels at the same time. When you have values for items that belong to different categories and want to distinguish the values between categories visually, this chart can do you a favor.

This tutorial demonstrates methods to create two kinds of multi-level category charts in Excel in details.

Create a multi-level category bar chart in Excel
Create a multi-level category column chart in Excel
Easily create a multi-level category chart with an amazing tool
Download the sample file
Video: Create band chart in Excel


Create a multi-level category chart in Excel

Please do as follows to create a multi-level category chart in Excel.

1. Firstly, arrange your data which you will create a multi-level category chart based on as follows.

1.1) In the first column, please type in the main category names;
1.2) In the second column, type in the subcategory names;
1.3) In the third column, type in each data for the subcategories.

2. Select the data range, click Insert > Insert Column or Bar Chart > Clustered Bar.

3. Drag the chart border to enlarge the chart area. See the below demo.

4. Right click the bar and select Format Data Series from the right-clicking menu to open the Format Data Series pane.

Tips: You can also double click any of the bars to open the Format Data Series pane.

5. In the Format Data Series pane, change the Gap Width to 50%.

Now the chart is displayed as follows.

6. Insert two blank rows before each main subcategory (except the first one) in the data range. Then you can see categories and data bars in the chart are separated with blank spaces as well.

7. Double click the first cell of the first row of each pair of blank rows you have inserted in step 6, type in a blank space by pressing the Space key once in the keyboard. Then you can see black outlines are added to the blank areas in the vertical axis fields.

8. Click the vertical axis, go to the Format Axis pane, and then check the Categories in reverse order box.

9. Select the chart title and then press the Delete key to remove it from the chart. Do the same to remove the horizontal axis and the gridlines.

10. Now you need to specify different colors for each main category.

10.1) Click on the first bar twice in the first main category to select it only;
10.2) Go to the Format Data Point pane, click the Fill & Line icon, select the Solid fill option in the Fill section, and the specify a color as you need;

10.3) Press the Right key on the keyboard to select the next bar, and then press the F4 key to specify the same fill color to it.
10.4) Repeat the step 10.3) to specify the same fill color to all bars of the first main category.

10.5) Repeat the above step 10.1) – 10.4) to specify another fill color for the bars of the second main category.

After specifying fill colors to distinguish the categories, you can see the chart is displayed as the below screenshot shown.

11. Now change the outline colors of the vertical axis and bars to black.

11.1) Select the chart title and press the Delete key to remove it from the chart. Do the same to remove the gridlines and horizontal axis;
12.2) Select the vertical axis, go to the Format Axis pane, click the Fill & Line icon, select the Solid line option in the Line section, and then select Black from the Color drop-down list.

12.3) Click on any one of the bars and then press the F4 key to apply the same outline color to all the bars.

Now the chart is displayed as follows.

13. Now you need to add data labels to the data bars. Please select the chart, click the Chart Elements button, and then check the Data Labels box.

Now the multi-level category chart is complete as the below screenshot shown.


Create a multi-level category column chart in Excel

In this section, I will show a new type of multi-level category column chart for you. As the below screenshot shown, this kind of multi-level category column chart can be more efficient to display both the main category and the subcategory labels at the same time. And you can compare the same subcategory in each main category vertically.

You can follows the below guidance to create this kind of multi-level category column chart in Excel.

1. As the above chart shown, there are three-level category columns, and each level of columns is separated by spacing, so the first step is to calculate the spacing between each level of columns based on the original data as the below screenshot shown.

1.1) Copy the original data range and paste into a new range. Insert a blank column next to each column, and then fill in the column headers. See screenshot:

1.2) In the first blank cell of the first blank column, enter the below formula and press the Enter key. Then select the result cell, drag the AutoFill Handle down to get the other results.
=MAX(B2:B6)*1.1-B2
Note: In the formula, 1.1 is a variable value. If you want the spacing become higher, this value should be bigger than 1.1. It depends on your needs.

1.3) Apply the below formula in the first blank cell of the second blank column. Then drag the AutoFill Handle down to get the other results.
=MAX(C2:C6)*1.1-C2

1.4) In the first blank cell of the third blank column, apply the below formula, then drag the AutoFill Handle down to get the other results.
=MAX(D2:D6)*1.1-D2

2. Select the whole helper range (A9:G14), click Insert > Insert Column or Bar Chart > Stacked Column.

3. Then a stacked chart is inserted into current worksheet. Keep the chart selecting, click Switch Row/Column under the Design tab (Chart Tools).

Then the chart is displayed as follows.

4. Double click any series in the chart to open the Format Data Series pane. In the pane, change the Gap Width to 0%.

5. Select the spacing1 data series in the chart, go to the Format Data Series pane to configure as follows.

5.1) Click the Fill & Line icon;
5.2) Select No fill in the Fill section.

Then these data bars are hidden.

6. Select the spacing2 data series, press the F4 key to hide it in the chart.

7. Then hide the spacing3 data series as the same operation as above.

8. Remove the chart title and gridlines.

Then the chart is displayed as the below screenshot shown.

9. Select the top data series and go to the Format Data Series pane to configure as follows.

9.1) Click the Fill & Line icon;
9.2) Select the Solid line option in the Border section;
9.3) Choose the White color from the Color drop-down list;
9.4) Change the border width to 1.5pt.

10. Repeat the step 9 to add the same border styles to the other two rows. Then you will get the following chart.

Now you need to add the main category values to the right side of the plot area as the below screenshot shown.

11. For adding these values, firstly, we need to get the certain positions of these values in the chart with some helper data.

11.1) In a new range, type in three 6 in a column, and in the next column, type in number 0 in the first cell.
Notes:
  • 1) As we can see, there are five columns represent the subcategories in the chart. In order to locate the main category values to the right side of the plot area, we need to place the main category values in the sixth column of the chart. So the first column data of the new helper range is 6. And as there are three main categories, here we need three 6.
  • 2) The number 0 represents the position of the first main category.
11.2) In the next cell, apply the below formula to get the position of the second main category.
=MAX(B2:B6)*1.1

11.3) Apply the below formula into the last cell to get the position of the last main category.
=MAX(C2:C6)*1.1+C18

12. Right click the chart and click Select Data from the right-clicking menu.

13. In the Select Data Source dialog box, click the Add button.

14. In the Edit Series dialog box, select the position data you have calculated for the main categories in the Series value box, and then click the OK button.

15. Then, it turns back to the Select Data Source dialog box, you can see a new series “Series7” is added in the Legend Entries box, click OK to save the changes.

16. Right click the chart and select Change Series Chart Type in the right-clicking menu.

17. In the Change Chart Type dialog box, specify the Chart Type as “Scatter” for the new series you added in step 15, and then click OK.

Now the chart is displayed as the below screenshot shown.

18. Right click the chart and click Select Data in the context menu.

19. In the Select Data Source dialog box, select the series you have added in step 15, and then click the Edit button.

20. In the Edit Series dialog box, select the number 6 cells in the Series X values box and then click OK.

21. Click OK to save the changes went it returns to the Select Data source dialog box.

22. Now the new series is shown as scatter dots and displayed on the right side of the plot area. Select the dots, click the Chart Elements button, and then check the Data Labels box.

23. Right click the data labels and select Format Data Labels from the right-clicking menu.

24. In the Format Data Labels pane, please do as follows.

24.1) Check the Value From Cells box;

24.2) In the popping up Data Label Range dialog box, select the range containing the main category values, and then click OK.

24.3) Uncheck the Y Value box;
24.4) Select the Above option in the Label Position section.

25. Select the dots in the chart, and then go to the Format Data Series pane to configure as follows.

25.1) Click the Fill & Line icon;
25.2) Click the Marker tab;
25.3) Expand the Marker Options and then select None. See screenshot:

26. Remove the legend from the chart by selecting and pressing the Delete key in the keyboard.

27. You can format the maximum of the vertical axis to make the chart compact by double clicking the vertical axis, then entering a new value into the Maximum box in the Format Axis pane.

28. Add data labels to the series by selecting the series, clicking the Chart Elements button and then checking the Data Labels box. See screenshot:

Now a multi-level category column chart is complete.


Easily create a multi-level category chart in Excel

The Multi Layer Column Chart utility of Kutools for Excel provides three multi-level category chart types. With this feature, you can easily create one of the multi-level category chart in Excel with several clicks only as the below demo shown.
Download and try it now! 30-day free trail

Download the sample file


Video: Create a multi-level category chart in Excel

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.