Partition Charts – Continuation – [More visualization fun]

Jon @ Peltiertech has taken a critical look at the partition charts suggested yesterday. You can read his review of the partition charts here. One of the commenters on his site said,

Jon, the partition chart is not that bad. It just needs to be defragmented… 🙂

partition-charts-in-excel-revisitedSo I de-fragmented my partition chart, this time it no longer provides any spatial trend cues or anything, instead its a cool little replacement for a pie chart, more so when you have just 3-4 pies and got bored of looking at pies.

Here is how in just 3 steps you can do this type of de-fragmented partition chart 😉

1. First create a grid where the chart can appear


This is the easy step, select a range of cells (preferably in multiples of 100, I took 300 cells, spaced over 25 columns, 12 rows) and adjust the row-height and column-width till the range looks fine enough. Take a look at this one on the right.

2. Now set the Conditional Format over the range

Lets say you want to show 3 pies, one for 30%, another for 48% and the last one for 32% (well, just kidding, you can only 110% of your work, not 110% of your pies) so 22%. The conditional formatting formulas can look like this:


Your formulas should look like this:

if cell's row-number * width + column-number is with in pie-1's range, turn the cell on to color-1
else if cell's rownumber * width + column-number is with in pie-2's range, turn the cell on to color-2
else turn the color-3 on

3. Remember to trash your pie charts

Well, not really, save the good old pies. There is no step 3. So I saved you … umm… 33% of work. So why dont you leave a comment and tell me what you think about this ?


Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Written by Chandoo
Tags: , , , , , , , ,
Home: Main Page
? Doubt: Ask an Excel Question

6 Responses to “Partition Charts – Continuation – [More visualization fun]”

  1. Jon Peltier says:

    I had a few words to say about this chart type (the defragged partition chart). It seems that WordPress has eaten my comment, but that's okay. I took the gist of it and wrote another post:

    Ineffective Chart - Partition Chart Revisited (Defragged)

    The defragmented version of the chart is better, but still not as good as other types. For example, it's still not easy to eyeball which of the light orange and brown regions is different, without having the legend to tell you which is 22% and which is 30%.

  2. Chandoo says:

    @Jon .. thanks for the post, I agree with your point that a pie or bar is any day better than the cryptic looking de-fragged partition chart or square pies. But they are reasonable alternatives to pie charts and make people to sit up and take notice of the chart in front of them. How many times we see news papers /websites / articles with pie charts / bar charts and promptly ignore what it is telling us because we are too used to those patterns?

  3. [...] super-intelligent shade of color blue from the Hitchhiker’s guide to galaxy, is essentially a square pie chart with colors based on the emotions contained in the text. [via Information [...]

  4. HJ says:

    How about a chart for this:
    I'd like to have show the 3x3 combination of these two characteristics with count of data points.
    It is like on Y-axis, chacteristic A: High, Low, Mid
    X-axis, chacteristic B: High, Low, Mid
    And inside the chart it shows dots of all data points people can get a general idea of density in each combination of  chacteristics A and B.

  5. […] Partition Charts – Continuation – [More visualization fun] Chandoo has reworked his partition chart, defragging it to bring all same-colored squares together. […]

Leave a Reply

« »