Clustered Column Chart in Excel

0018
What is the Clustered Column Chart?

The clustered column chart is a column chart that visualizes the magnitude of data using vertical bars. It is majorly used to show multiple variables of data in one chart. It is easy to compare two variables using the Clustered Column Chart.

How a Clustered Column Chart Different From Column Chart?
Although the clustered column chart is a column chart, it is slightly different from a simple column chart. A column chart is used to represent one variable against one other variable. For example, total sales in each month of the year. While Clustered Chart is used visualize multiple variables of data. For example, total sales of two or more cities in each month of the year.
0019
When to Use Clustered Chart?
Use Clustered Column Chart when you need to compare multiple variables. Try to keep only a few variables. Too many variables will make clustered chart too dense and it will not be easy to make sense out of it.
How To Create a Clustered Chart in Excel?

It’s easy to create a clustered chart in excel. First prepare your data. Here I have prepared sales data of two cities by month.
0021
Now I want to compare both cities sales in each month. And the best way to compare data is using clustered chart column chart or bar chart. Follow these step to plot clustered chart in excel.

    1. Select relevant data. Here I have selected month, Delhi, and Mumbai column. Excluded total because I don’t want it to be in my chart.
    2. Go to Insert tab? Charts? Column Or Bar Chart? Clustered Column Chart.

022

  1. The clustered chart is plotted in excel.

0024
The two cities are shown in two different colours. But still, we can’t clearly see the month grouping. It looks to crowded. Let’s make some space among the months so it easy to differentiate the months. We need to group the columns by month.

How to Insert space between groups of columns?
To make space between groups of columns in chart, follow these steps.

    1. Right click on chart and click on select data.

0025

    1. Select Data Source dialog will open. In Legend Entries, click on the Add button.

0026

    1. Edit Series Dialog will open. Leave the Series name blank. In Series values: type 0 in curly brackets. Hit the OK button.

0027

  1. Now you can see that each month is grouped and separated.

0028
To make it even better, reduce the gap width and Series Overlap to 0%. To do so..

  1. Right Click on any column and click on Format Data Series. In Excel 2016, on the right side of screen, you’ll see formatting options. In 2010 and older versions, you’ll see a dialog box for same.
  2. Reduce the gap width and Series Overlap to 0%.

0029
Rename the chart title to “Sales”.
Add Data Labels to Clustered Chart

Well, it’s not required since you can visually see the difference but still, If you want to see the magnitude of each column then you should add data labels to the chart. To add data labels to the clustered chart, follow these steps:

  1. Click on the chart and go to design tab.
  2. In the left most corner, you can see Add Chart Element options. Click on it.
  3. Click on Data Labels. Select which over format you like. I choose outside end.

0030
Your clustered column chart is ready. To beautify this clustered chart, you can use formatting option or just use pre-designed chart style provided in Excel 2016. Choose the one you like.

0031

Finally, you are ready to rock the presentation with your self-explanatory clustered charts.
Include Total Column Clustered Charge Creatively

pasted image 0

In the above chart, we don’t have a total column. Because it is foolish to compare variables to their sum. But you may want to compare total sales of different months. In that case, this kind of clustered chart will be a good option.

Isn’t it beautiful and creative? We can compare each city's data in separate months and totals of months too in one single chart. To make this kind of chart click here.

So yeah guys, this is how you plot a cluster column chart in excel. In the same way, you can plot a clustered bar chart. The only difference would be the orientations of the bar. Here the bars are vertical and called columns. In the bar chart, they are horizontal and called clustered bar chart.

If you have any query regarding charts in excel or anything related to excel and VBA, feel free to ask me in the comments section below.

Related Article:

Creative Column Chart that Includes Totals

Create Overlay Chart in Excel 2016

Pareto Chart and Analysis

Waterfall Chart

Excel Sparklines : The Tiny Charts in Cell

Speedometer (Gauge) Chart in Excel 2016

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube