How to Make a Grouped Bar Chart in Excel (With Easy Steps)

What Is a Grouped Bar Chart?

A grouped bar chart is also known as a clustered bar chart. It displays the values of various categories in different time periods, and is useful for representing data after comparing it in multiple categories.

Making a Grouped Bar Chart in Excel

We will use the following dataset to derive our final bar chart.

The dataset contains the value of sales in different cities in particular years. But the data has been recorded randomly. We need to first group the data, then use that grouped data to create our grouped bar chart.

STEP 1 – Insert Clustered Column Chart

Select the data range (B4:D13).
Click the Insert tab.
Select the Clustered Column option from the Chart option.

A chart like the following image is created.

STEP 2 – Modify Chart by Sorting Column Data

To create a grouped bar chart, we need to sort the column data.

Sort the Year and City columns like the following image.

On our chart, the bars are grouped the same way.

STEP 3 – Group Similar Data Together

Now we will create groups with similar types of data.

Insert a blank row after each group of data (between the different cities).

On our chart, extra blank spaces between the groups are added too.

Interchange the Year and City columns.
Keep only the top City name in each group and delete the others.

The names of the Cities and Years now appear on the x–axis of our chart.

STEP 4 – Format Grouped Chart

Let’s apply some formatting to our chart.

Select the chart and click on the bars.

Press ‘Ctrl + 1’.

The ‘Format Data Series’ box opens to the right of the chart.

Set the value of ‘Gap Width’ to 0%, which will combine the bars of the same category in one place.

Under Fill, check ‘Vary colors by point’.

On our chart, bars of the same category are now not only combined, but also in different colors.

STEP 5 – Convert Clustered Column Chart to Clustered Bar Chart

In the final step, we will convert our clustered column chart into a clustered bar chart.

Select the chart.
From the top menu, select Chart Design > Change Chart Type.

A dialogue box named ‘Change Chart Type’ will appear.

Click the ‘All Charts’ tab.
Select chart-type Bar.
Select the ‘Clustered Bar’ option from the available chart types for Bar.
Click OK.

Our clustered column chart is converted into the following clustered bar chart:

Things to Remember

Charts are the best way to analyze data since graphs portray data more precisely.
Data must be arranged in a particular order to make a grouped bar chart.
One can change the chart type after making a grouped bar chart.
The effectiveness of the grouped bar chart depends on the arrangement of the data.

Download Practice Workbook

Make Grouped Bar Chart.xlsx

<< Go Back to Excel Bar Chart | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

joost Aug 13, 2024 at 3:15 PM

what is missin gis the final step: giving all months the same colour within the various groups. Excell wont do that for you….
- Reply
  Shamima Sultana Aug 14, 2024 at 11:21 AM
  
  Hello Joost,
  
  Hope you are doing well. Thank you for your feedback! Applying the same color to all bars in a group might not be ideal if they represent different years or categories. It’s better to use different colors or shades for clarity. This helps in distinguishing the data within the group more effectively. That’s why we avoided same color across the grouped bar chart.
  
  But if it is helpful to you to visualize the bars then you can do it.
  As Excel doesn’t automatically apply the same color across the grouped bar chart categories. You can manually adjust the colors by selecting each data series and applying the same color.
  Follow the steps to do so:
  1. Click on one of the bars you want to change.
  2. Right-click and select Format Data Series.
  3. Choose Fill and pick your desired color.
  Repeat for each data series to maintain consistent coloring across all groups.
  
  Regards
  ExcelDemy