How to Create Bar Chart with Multiple Categories in Excel

STEP 1 – Create Dataset for Bar Chart with Multiple Categories

Create a dataset for the bar chart that includes categories, sub-categories, and items in three separate columns.
In the sample box, we have included a category column Product Type, sub-categories column Product, and items column Quantity.

excel bar chart with multiple categories

Note: Leave 2 blank rows in between each category and insert a space in the first empty cell of the blank row for better visualization of the chart.

STEP 2 – Create Bar Chart Using Insert Tab of Excel

Select the whole dataset to create a bar chart with it.

Create Bar Chart Using Insert Tab of Excel

Go to the Insert tab and select Insert Column or Bar Chart > 2-D Bar > Clustered Bar.

A bar chart with multiple categories is created in the worksheet.
Double-click on the chart title and give it a name.

STEP 3 – Give Different Colors to Bar Chart

Click the chart. It will select all the chart items.
Click on a single item and it will be selected as shown in the image

Give Different Colors to Bar Chart

Go to the Format tab and select a color from the Shape Fill option.

Follow the same steps for different colors to items from different categories.

excel bar chart with multiple categories output

STEP 4 – Do Some Additional Formatting

Select the bar chart.
From the upper right corner of the chart select Chart Elements.
Select Data Labels. It will attach data labels to the chart elements.
Unselect Gridlines and the gridlines from the graph will vanish.

Do Some Additional Formatting

Double-click on the chart.
The Format Chart Area pane will appear.
Select Border from the pane. You can choose the borderline type from here.
In the sample image, we selected Solid Line. Different colors and line widths for the border line can be chosen from this section.