Excel Clustered Bar Chart

Excel Clustered Bar Chart (Tables of Contents)

• What is Clustered Bar Chart in Excel?
• Types of Bar Chart in Excel
• How to Create Clustered Bar Chart in Excel?

Clustered Bar Chart in Excel

Clustered Bar Charts are “displaying more than one data series in a horizontal bar”.

For example: If you have one year as the main heading and under this heading, there are 4 subheadings Q1, Q2, Q3, Q4. In a normal bar graph only for main headings, we create bar charts. Since one year includes 4 more subheadings, we need to create clustered bar graphs.

Here we can compare minutely compare the subheadings performance against each other instead of only the main headings.

Typical clustered bar chart represents the Categories (headings and subheadings) vertically and all the numerical values (category values) horizontally.

The main difference between COLUMN CHART & BAR CHART is “column chart represent the categories horizontally and numerical values vertically”. However, a bar chart is just the vice versa of the column chart.

Below image shows the difference between COLUMN & BAR chart.

The main difference is showing the values horizontally and vertically at an interchanged axis.

Types of Bar Chart in Excel

There are totally 5 types of bar charts available in excel 2010.

• 2-D Bar and 2-D stacked Bar chart
• 3-D Bar and 3-d stacked Bar chart
• Cylinder Bar chart
• Cone Bar chart
• Pyramid Bar chart

How to Create a Clustered Bar Chart in Excel?

It is very simple and easy to use. Let us now see how to create a Clustered Bar Chart with the help of some examples.

Example #1

In this example, I have 4 years of data with a break up of each quarter in each year.

Step 1: Dataset should look like this.

Step 2: Select Data > Go to Insert > Bar Chart > Clustered Bar Chart. 

As soon as you insert the chart, it will look like this.

Oh! This looks ugly man. We need to beautify it.

Step 3: Right click on the bar and select format data series.

Go to fill and select Vary colors by point.

Step 4: As of now, it looks better than the previous. We need to format our vertical axis. We need to rearrange the data as shown in the below image.

Step 5: Once the data has rearranged your chart, looks like this.

Step 6: Right click on the bar and select format data series.

Make the Gap Width 0%. Series Overlap to 0%.

Ok, our clustered bar chart is ready and we can just read the numbers from the graph itself.

Interpretation of the Chart:

• Q1 of 2015 is the highest sales period where it has generated revenue of more than 12 lakhs.
• Q1 of 2016 is the lowest point in revenue generation. That particular quarter generated only 5.14 lakhs.
• In 2014 after a dismal show in Q2 & Q3, there is a steep rise in revenue. Currently, this quarter’s revenue is the second highest revenue period.

Our final looks like this.

Example #2

I am using Target vs Actual data for this example.

Step 1: Arrange the data in the below format.

Step 2: Insert the chart from the insert section. Go to Bar Chart and then select Clustered Bar Chart. 

Initially, your chart looks like this.

Do the formatting by following below steps.

• Right click on the chart and choose Select Data.

• Remove City & Year from the list.

• Click on EDIT option.

• And select City & Year for this series.

• So now, your chart will look like this.

• Apply to formate as we have done in the previous one and after that, your chart looks like this. Change the chart type for the Target series to present better. Select the target bar, go to design, and click on Change Chart Type.

• And select the Line Chart.

• So our chart is ready to convey the message now and it looks like this.

Advantages

• Easy to make and understand.
• We can compare the multiple categories subcategories easily.
• We can spot the changes very easily.
• Very useful if the data categories are small.

Disadvantages

• Creates confusions if the data is too large.
• There are chances of overlapping of many subseries.
• May not be able to fit everything in one chart.

Things to Remember About Clustered Bar Chart

• Arrange the data before creating a Clustered Bar Chart.
• If the data is too large chose different chart.
• Avoid 3D effects.
• Construct the chart carefully so that it will not overlap with other categories.

Recommended Articles

This has been a guide to Clustered Bar Chart. Here we discuss its types and how to create Excel Clustered Bar Chart along with excel examples and downloadable excel template. You may also look at these useful charts in excel –

1. How to Build a Pivot Chart in Excel?
2. Guide on VBA Charts in Excel
3. Exclusive PERT Charts for Dummies
4. Create Innovative Pie Chart In MS Excel