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
20 Responses to “Partition Charts in Excel – alternative to pie charts [visualization hack]”
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
[...] Partition Charts in Excel - alternative to pie charts [visualization hack], Chandoo offers a “partition chart” as an alternative to a pie chart. We all know pie [...]
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.
[...] Original post by Chandoo [...]
[...] above is a very good example of partition chart in which spatial trends are meaningful. It shows preferences of several women plotted to reveal [...]
[...] Ineffective Chart - Partition Chart, I discussed the partition chart that Chandoo spoke of in Partition Charts in Excel - alternative to pie charts [visualization hack]. I felt that the partition chart hid the data it was meant to present. Jorge Camoes remarked that [...]
[...] Resource loading charts are a good way to show how busy the team members are in a project. At the outset the resource loading chart is nothing but a heatmap. [...]
You can achieve the same effect in a single step by turning your data into a 3D surface graph and then colouring the contours to match whatever it is you want to show and changing the "viewpoint" so you look vertically down on the chart. I have used this to show pricing patterns for electricity where you have 365 days by 48 half hour periods. This clearly shows the differences in seasons and time of day and the weekly pattern too. The smoothing in drawing the surface does make the pattern a bit more "blobby" but the whole point of these heat maps is showing the general shape of the data and not determining "what was the value at 1530 on Tuesday the 3rd of August".
The trick is to get the colouring right using the scales and then change the viewpoint at the end.
You can also achieve a similar effect if you have discrete binary data (like raining/not raining) rather than a continuous variable by using a 3D stacked column graph and closing the gaps between the ranges and the columns but that is more of a fiddle. You can also produce a precise heat map this way but it is a pain to do the data manipulation and what you get isnt that much better than the quick and dirty method above unless you only have a small number of points - in the pixel example I think my method will look indistinguishable from the precise version.
@Peter... Very interesting solution to the problem. If you do not mind, can you email me your file .. I never use contour charts so I would love to explore your example and write about it in future. Please send the file to chandoo.d @ gmail.com
I like your idea Peter have you save your result as in an EXCEL file??? Would you be so kind and share it with us??
Thank you very much
Chrisos
Just found this post and read to the bottom of the comments. Â I appreciate this is two years late but I am also intrigued by Peter's ideas, did an Excel example sheet ever turn up? Â If so, can someone point me in the right direction to find it?
Thanks all!Â
Hi chandoo,
Even though you claim it to be an easy step, I don't get how to put multiple conditions in CF. How to get that interface of condition1, condition 2 etc.
My excel 2007 just says,'Use a formula to show which cells to format'.
You have used such multiple formulas in CF in a few more examples and I have been able to recreate the effect by applying multiple CF rules on the same cells.
Hi Chandoo,
Your site is great and I have been visiting it for many years.
I have been using the heat map concept for the last 10 years. Specifically, I use it to show cross-correlation matrices.
Organising data produces a much better readable output, in my opinion. By sorting rows and columns to show high correlated entries on the top left, and "pushing" the less correlated ones towards the right and the bottom you get a kind "surface".
Take a look at these 200 Hedge Fund Indexes showing correlation for the last 12 months. http://qutopic.com/templates/crosscorrelation-template/
I used data from SQL Server, correlations calculated with a Store Procedure and called into Excel using my qUtopic functions. An array is returned, which is the coloured using conditional formatting. All the best! G.