Clustered Column Chart in Excel

Clustered column chart in excel is a column chart which represents data virtually in vertical columns in series, though these charts are very simple to make but these charts are also complex to see visually, if there is a single category with multiple series to compare then it is easy to view by this chart but as the categories increases it is very complex to analyze data with this chart.

What is the Clustered Column Chart in Excel?

Before straight head into the “Clustered Column Chart in Excel,” we just need a look into the simple column chart first. The column chart represents the data in vertical bars looking across the chart horizontally. Like other charts, the column chart to a has X-axis and Y-axis. Usually, X-axis represents the year, periods, names, etc. and Y-axis represents numerical values. Column charts are used to display a wide variety of data to exhibit the report to the top management of the company or to an end-user.

Below is a simple example of a column chart.

Clustered Column Chart

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Clustered Column Chart in Excel (wallstreetmojo.com)

Clustered Column vs Column Chart

The simple difference between the Column chart and Clustered Chart is a number of variables used. If the number of variables is more than one, then we call it a “CLUSTERED COLUMN CHART”; if the number of variables is limited to one, then we call it a “COLUMN CHART.”

One more major difference is in the column chart; we are comparing one variable with the same set of another variable. However, in the clustered column excel chart, we compare one set of a variable with another set of variables as well as within the same variable also.

Therefore, this chart tells the story of many variables, while the column chart shows the story of only one variable.

How to Create a Clustered Column Chart in Excel?

The clustered Column excel chart is very simple and easy to use. Let us understand the working of with some examples.

You can download this Clustered Column Chart Excel Template here – Clustered Column Chart Excel Template

Example #1 Yearly & Quarterly Sales Analysis

Step 1: The dataset should look like this.

clustered Column Chart Example 1

Step 2: Select the dataGo to Insert > Column Chart > Clustered Column Chart.

clustered Column Chart Example 1-1

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

clustered Column Chart Example 1-2

Step 3: Do the formatting to arrange the chart neatly.

Select the bars and click Ctrl + 1 (do not forget Ctrl +1 is the shortcut to format).

clustered Column Chart Example 1-3

Click on fill and select the below option.

clustered Column Chart Example 1-4

After varying, each bar with a different color chart will look like this.

clustered Column Chart Example 1-5

Formatting Chart:

  • After this make, the gap width of the column bars to 0%.
clustered Column Chart Example 1-6
  • Click on Axis and choose major tick mark type to none.
clustered Column Chart Example 1-7

Therefore, finally, our clustered chart will look like this.

clustered Column Chart Example 1-8

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.

Example #2 Target vs Actual Sales Analysis across Different Cities

Step 1: Arrange the data in the below format.

clustered Column Chart Example 2

Step 2: Insert the chart from the insert section. Follow the steps of the previous example to insert the chart. Initially, your chart looks like this.

clustered Column Chart Example 2-1

Do the formatting by following the below steps.

  • Right-click on the chart and choose Select Data.
clustered Column Chart Example 2-2
  • Remove CITY & YEAR from the list.
clustered Column Chart Example 2-3
  • Click on the EDIT option and select CITY & YEAR for this series.
clustered Column Chart Example 2-4
clustered Column Chart Example 2-5
  • So now, your chart will look like this.
clustered Column Chart Example 2-6
  • Apply to format as we have done in the previous one, and after that, your chart looks like this.
clustered Column Chart Example 2-7
  • Now change the TARGET bars chart from the Column chart to the line chart.
  • Select Target Bar chart and go to Design > Change Chart Type > Select Line Chart.
clustered Column Chart Example 2-9
  • Finally, our chart looks like this.
clustered Column Chart Example 2-8

Interpretation of the Chart:

  • The blue line indicates the target level for each city, and Green bars indicate the actual sales values.
  • Pune is the city where no of the year it has achieved the target.
  • Apart from Pune, Bangalore & Mumbai cities achieved the target more than once.
  • Kudos! To Delhi for achieving the target 3 years out of 4 years.

Example #3 Region-wise Quarterly Performance of Employees

Note: Let’s do it on your own, and the chart should look like the below one.

  • Create the data in the below format.
clustered Column Chart Example 3
  • Your chart should look like this.
clustered Column Chart Example 3-1

Pros of Clustered Column Excel Chart

  • The clustered chart allows us to directly compare multiple data series of each category.
  • It shows the variance across different parameters.

Cons of Clustered Column Excel Chart

  • Difficult to compare a single series across categories.
  • It could be visually complex to view as the data of the series keeps adding.
  • As the dataset keeps increasing, it is very confusing to compare more than on data at one time.

Things to Consider Before Creating Clustered Column Chart

  • Avoid using a large set of data, as it is very hard to understand for the user.
  • Avoid 3D effects in your clustered chart.
  • Play smartly with your data to arrange the chart beautifully, like how we inserted one extra row between cities to give some extra spacing between each bar.

Recommended Articles

This has been a guide to Clustered Column Chart in Excel. Here we discuss its uses and how to create Clustered Column Chart along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>