fbpx

Partition Charts – Continuation – [More visualization fun]

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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

defrag-partition-excel-chart-pie-alternative-fun

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:

conditional-formatting-more-visualization-fun-defragmented-chart

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 ?

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

100 Excel Formulas List

From simple to complex, there is a formula for every occasion. Check out the list now.

20 Excel Templates

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Extract words from sentence using FILTERXML - formula tip

#awesome trick – Extract word by position using FILTERXML()

This is CRAZY!!!. I stumbled on a weird use for FILTERXML() while reading a forum post earlier today. So I couldn’t wait to test it. I am happy to share the results.

Say you have some text (sentence / phrase / keyword etc.) in a cell and you want to extract the nth word. Unfortunately Excel doesn’t have SPLIT() formula. So we end up writing obscenely long array formulas or use gazillion helper columns.

Here is the super sneaky trick. Use FILTERXML() instead.

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