You are here
Home > Techtips

Clustered Stacked Column Chart in Excel

How to Create a Clustered Stacked Column Chart in Excel??

So we have seen in an earlier blogpost: Column Chart and its 7 sub-types!! that we have the Clustered Column, Stacked Column & 100% Stacked Column Chart Types in Excel… but it does not offer a Clustered Stacked Column Chart / Bar Chart Type… which is what… is sometimes required for in graphical representation through Excel Charts.
► So what exactly is a Clustered Stacked Column Chart??

Well, a Clustered Stacked Column Chart allows the user to group data (cluster the data points) and use it in combination with a Stacked Column Chart Type…

Let us understand this with an example:
► Consider the Quarterly – Class Unit Test Performance in Science, Maths & English as shown below:

Student Name Quarter English Maths Science
Sheetal Q1 – Unit Test 71 82 85
Sheetal Q2 – Unit Test 75 85 89
Ravi Q1 – Unit Test 62 71 70
Ravi Q2 – Unit Test 64 76 75
Neelam Q1 – Unit Test 79 85 90
Neelam Q2 – Unit Test 83 87 94

Now here are the steps to create a Clustered Stacked Column Chart:

1. Sep up the Chart Data Range – by sorting and arranging the records in the right sequence (here, in our case – doing a multi-level sort – first Student-wise and then Quarter-wise…)
2. Then, select the Chart Data Range (Table Above) to plot a Chart as shown below…aaaQuarterly – Class Unit Test Performance – Stacked Column Chart

  1. Ensure that the Major Category Name (here Student Name) is listed only once by removing / deleting the subsequent instances of the category name. The above Table now looks like this as shown below (Student Name appears only once)…
Student Name Quarter English Maths Science
Sheetal Q1 – Unit Test 71 82 85
Q2 – Unit Test 75 85 89
Ravi Q1 – Unit Test 62 71 70
Q2 – Unit Test 64 76 75
Neelam Q1 – Unit Test 79 85 90
Q2 – Unit Test 83 87 94
  1. Now with the Horizontal Category Axis of the Chart selected, Click on the context Menu Tab: Chart Tools >>> Format >>> Current Selection >>> Format Selection.

    5. Now from the Format Axis Panel that appears, click on Axis Options >>> Labels >>> Enable Multi-Level Category Labels… as shown below. This is the Key Step to create a Clustered Stacked Column Chart.bbbbEnable Multi-Level Category Labels

    1. Further fine-tuning of the Clustered Stacked Column Chart can be done as shown below…ccc

    Fine Tuning the Clustered Stacked Column Chart

Leave a Reply

Top
Please wait...

Subscribe to our newsletter

Want to be notified when our article is published? Enter your email address and name below to be the first to know.