Updated June 12, 2023
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 Chart in Excel is shown as horizontal bars parallel to X-Axis, also used for comparing the values across different categories. We can access It from the Insert menu under the Charts section in the Bar Chart Section, available in both 2D and 3D types of charts. To create Clustered Bar, we must have 2 values for a one-parameter to compare those values before and after or in a different time frame.
The below image shows the difference between the COLUMN & BAR charts.
The main difference is showing the values horizontally and vertically at an interchanged axis.
Types of Bar Charts in Excel
A total of 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.
In this example, I have 4 years of data with a break-up each quarter each year.
Step 1: The 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 one. 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 been 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, generating 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 was a steep rise in revenue. Currently, this quarter’s revenue is the second-highest revenue period.
Our final looks like this.
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 the below steps.
- Right-click on the chart and choose Select Data.
- Remove City & Year from the list.
- Click on the EDIT option.
- And select City & Year for this series.
- So now, your chart will look like this.
- Apply to formate as we did in the previous one, and 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.
- 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.
- It creates confusion if the data is too large.
- There are chances of overlapping many subseries.
- I May not be able to fit everything in one chart.
Things to Remember
- Arrange the data before creating a Clustered Bar Chart.
- If the data is too large, choose a different chart.
- Avoid 3D effects.
- Construct the chart carefully so that it will not overlap with other categories.
This has been a guide to Clustered Bar Charts. Here we discuss its types and how to create Excel Clustered Bar Chart, Excel examples, and a downloadable Excel template. You may also look at these useful charts in Excel –