How to Create Multi-Category Charts in Excel?

Last Updated : 24 May, 2021

The multi-category chart is used when we handle data sets that have the main category followed by a subcategory. For example: "Fruits" is a main category and bananas, apples, grapes are subcategories under fruits. These charts help to infer data when we deal with dynamic categories of data sets. By using a single chart we can analyze various subcategories of data.

In this article, we will see how to create a multi-category chart in Excel using a suitable example shown below :

Example: Consider the employees from our organization working in various departments. The main categories under departments are "Marketing", "Sales", "IT". Now under every department, there are multiple employees which is a subcategory.

Now, we would like to create a Multi-Category chart having the age details of employees from multiple departments. Consider the table shown below :

Table :

Implementation :

Step 1: Insert the data into the cells in Excel. Now select all the data by dragging and then go to "Insert" and select "Insert Column or Bar Chart". A pop-down menu having 2-D and 3-D bars will occur and select "vertical bar" from it.

Select the cell -> Insert -> Chart Groups -> 2-D Column

An important thing to note is that sometimes few subcategory data may be missing in the chart after insertion. It is not an error and just by increasing the size of the chart the data will be shown.

The Multi-category chart is ready. Now we can perform various modifications to it to make the chart more insightful by adding titles, changing the width and gap, assigning different colors to different sub-categories.

Step 2: For formatting, select the chart and use the "+" button beside it to add Title, Data labels, Axes Titles, and others or right-click and then select Format option.

Adding Title: Check the "Chart Title" box from the Chart Elements pop down and add a suitable title.
Adding data labels: Check the "Data Labels" from the Chart Elements pop down.

Assigning a color to bars: Select the individual bar and add colors as shown below:

Gap Width: Assign a suitable gap width as per requirements. Select all the bars, right-click on it and select "Format Data Series."

Formatting Axis: Select the chart and then click on the "+" button and under "Axes" select "More Options". Another way is to select the axis in the chart and then right-click and click on "Format Axis".

Changing the chart style: Click on the Chart Styles beside the chart and select a suitable chart style.

Changing the Chart Type: Suppose now we want the above chart in the form of horizontal bars.

Select chart -> Right Click -> Change Chart Type