Partition Charts in Excel - alternative to pie charts [visualization hack]
By Chandoo at 9 July, 2008, 5:50 pm
Next time you had to create a pie chart, consider building a partition chart as these charts can reveal trend information along with how much each pie is contributing. What more? They are as easy to create as eating a chocolate chip cookie dough ice cream 
I made a sample partition chart based on some random data, the trick lies in using conditional formatting to change the color of a rectangular range of cells based on our data. Just follow these 3 steps to create a neat looking partition chart.
1. First get the data you want to show in partition chart
The data should be in a table format. A sample dataset can be, your top selling product in each of the 100 cities for the last 48 months.
2. Create a grid where you can plat your partition chart
Now create an identical grid beneath the data table where we can plot our data chart. You can adjust column / row heights for this range until it looks like a blank canvas where you can plot the partition colors, something that looks like this.
Remember: the grid dimensions should be same as our data table in Step 1
3. Finally apply conditional formatting to the grid
This should be easy step, even if you are not a rock star of conditional formatting,
Specify conditions for each partition, Sample this:
That is all, you have a partition chart in front of you using which you can probe and analyze spatial trends.
Also: Art with charts, 73 FREE Downloadable Excel Chart templates
Tagged with: charting | cool | Excel Tips | howto | ideas | microsoft | technology | tricks | visualization
Did you enjoy reading this post? If so, give me some love
Consider subscribing to my newsletter every weekday I will send you one email with hot excel tips, technology tidbits or business insights that can make you more productive. Each mail will have unsubscribe link so you can stop receiving the mails at any time you wish.
Trackbacks & Pingbacks
- Pingback by Ineffective Chart - Partition Chart » PTS Blog on July 9, 2008 @ 8:15 pm
- Pingback by Partition Charts in Excel - alternative to pie charts … - Learn Excel on July 15, 2008 @ 3:22 am
Comments
RSS feed for comments on this post. TrackBack URI
While pie charts are somewhat ineffective at showing relative proportions, this partition chart is completely ineffective. If not for the legend, I would have guessed that the darkest color accounted for the largest, not the smallest, proportion. The pie at least puts all of its bits into one wedge per category, while the partition chart keeps all the bits separate, and they are scattered across the chart. It’s as if you spilled a package of M&Ms on the table and tried to determine the portions of each color.
As ever, a bar or column chart shows this proportion best of all:
http://peltiertech.com/WordPress/wp-content/img200807/NonPartition.png
@Jon .. thanks for the comments, you are right that this partition chart could be more confusing if you are just looking for what percentage is each portion type of info. The purpose of this chart is not to replace pie charts but to augment them with spatial trending info…
an example could be how your sales figures are on a map or a heat map of your initiatives…. but then, I take your point … this is more confusing than clarifying…
I watch this site just for the visualisation treats. I have been doing my charts in autocad as one off drawn, non-updatables. so the excel tips help a lot. I’m working towards keeping the data in excel and using the graphic freedom of cad to customise the finished product.
thanks Chandoo
Chandoo,
In your response to Jon Peltier, you said that your example of a partition chart reveals trend information. The presence of meaningful trend information isn’t obvious to me. What are the trends that you discern from this chart and what are the visual patterns that reveal them?
The term “trend” usually refers to the overall direction and amount of change through time, but your partition chart doesn’t appear to show change through time. If each cell represents a different city, as you have suggested, and the color represents which of three products sold best in each city, assuming that the cities are arranged alphabetically, the positions of each of the three colors are meaningless. There are not only no trends to observe, but there are no meaningful patterns of any kind.
@Dave … you are welcome, thanks for the comments
@Stephen … You have an awesome point, but I didnt bother to do any trend (not time series though, this is mostly spatial) because I used random data (generated a range of random numbers from 1-100 and assigned 3 colors based on the value, that is why it clearly looks like garbage or as Jon said, a bunch of M&M’s)
I think my example of city-wise top selling products is slightly misleading as it requires the data to be arranged based on the real lat / long of cities (which I didnt mention).
Let me give you a better / relevant example. Take the user mouse clicks data for a sample web page (1024*768) and suppose you have the click data for each pixel over some time period (lets say 24 hours) then this kind of chart can clearly reveal where user is clicking most - a la a heat map.
I guess, I have focused more on “How-to” part of the chart to the extent that I neglected the “why / what is” part of it. Its good to have this discussion so that I can learn / clarify these points.
and btw, welcome to PHD Blog, hope you like it here
Great work chandoo. I have the right kind of data that i used to make a heat map of the no of emails that were sent to my yahoo aerospace club. I have made the excel file and was wondering if you want to use it as the download sample for this exercise. Please let me. I will send it to your email and you can use and format as you want.
Thanks again. I love your excel tips. I am also kind of pro in excel but i tend more towards VBA. I guess that’s why i like your simple excel tricks.
keep up the good work!!
Chandoo -
I should say, the ability to use conditional formatting (CF) is important. You may not have met your objectives with this chart, because as you say, it was random data used to describe an approach. This approach would be reasonable as you said for click tracking, or for eye tracking studies that show where visitors to a site focus their attention.
Not too different from this approach is using CF to draw a simple Gantt chart. It’s the first technique I show on my tutorial on Gantt Charts. CF has many other uses as well, not just in presenting data, but providing feedback to the user, for example, when they’ve entered an invalid piece of data in a cell.
@aerogeek : thanks for the comments, I think the point has been made that these types of charts are good for spatial data, so I dont think I would need more data to create sample charts.
I generally try to avoid vba and try to stretch the limits of existing functionality to get the work done. thanks for the feedback, keep visiting
@Jon .. I totally agree, conditional formatting is a powerful tool. I have written about it here - http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/ where I also spoke about the gantt chart hack, providing the feedback etc. thanks for the discussion, I enjoy this
Chandoo -
I enjoy the interaction as well. I hope you still like it after you read my comment to your follow up post. The defragged partition chart reminds me of the square pie charts which were all the rage last year. Defragging the partition chart helps somewhat, but I think it’s still less effective than the pie I posted in my article (Ineffective Chart - Partition Chart ). And the pie was less effective than the bar chart, although a three-point pie chart isn’t terribly bad.