When you think of visualizing parts to a whole in Excel charts the first thing that’s likely to come to mind is the pie chart, or if you’re Homer Simpson then you might think of doughnut charts!
Being outnumbered 3:1 by football (soccer) fans in my household, I get to see my fair share of “the beautiful game” on TV. To pass the time I often find myself looking at the match statistics posted on the screen.
Football stats typically focus on possession, shots, corners, fouls and other comparisons that often bear no correlation to the outcome of the game, although I’m not complaining as they help to pass the time 😊
Using football as inspiration, let’s look at our options for visualizing parts to a whole data using this standard set of football statistics:
Pie Charts
We’ll start with the obvious and most favored among data visualization novices; the pie chart.
If you know me then you’ll know that I’m not a fan of pies, unless they contain apple and are edible 😊
That said, recently I’ve relaxed a little and used the occasional 2 or 3 segment (max) pie chart, but in doing so it only reinforces my reasons for disliking them.
My biggest gripe with pie charts, aside from when used poorly, is that they’re space hogs. Just take a look at the examples below (note: I would normally use colors from the football club logos in my charts, but the red is a bit harsh and the other colors clashed, so I abandoned them. If in doubt, go neutral.):
I even simplified them by extracting the legend and placing it at the top of the charts. After all, I don’t need to repeat it in each pie.
Sure, I could try to make them even smaller, but that’s just more fiddling about and I’m way too busy for that. I need my pies like fast food, ready to consume with one or two clicks.
The other downside of pies when used in multiples is they make it difficult to compare one metric to the next, not that it’s always relevant to do so, but if it was then pies make it hard work.
Doughnut Charts
Doughnut charts aren’t any better. Like their namesake they're certainly not slimming; they're big, chunky, space hogs:
100% Stacked Column/Bar charts
My preferred way to visualize this type of data is using a 100% stacked bar chart. You can also use column charts, but if your category axis labels are long then you’ll want to stick with bars.
Advantages of Bar Charts over Pie Charts
- The main advantage of the bar chart is the ability to convey a lot of information in a very small space, which is essential when building Dashboards.
- If you’re wanting to compare multiple items, then the bar chart makes is a lot quicker.
- In Excel charts the bars are contained in one chart object, so if you want to make formatting changes you only have to do it once. For example, I could easily make those bars even smaller with a simple left click and drag of the corner.
However, if you or your boss insists on using pie or doughnut charts, then you should follow some rules:
- No more than 3 segments. Anymore and the comparison ability is lost, plus your reader will spend too much time trying to map the legend to the segments to understand the components.
- Make sure your segments are ordered from largest to smallest starting at 12 o’clock. You’ll notice that Excel doesn’t do this for you, you must sort your data. Case in point; the Tackles pie and doughnut charts.
- Don’t use labels on sticky-outy leader lines. If the labels don’t fit in the segments, use a different chart.
If you run into problems 1 or 3 above, use a clustered (not 100% stacked) bar chart instead.
Oh, and if you're ever tempted to create an exploding pie chart like this:
Don't! Instead consider a layered bar chart like this:
Other Parts to a Whole Visualizations
We're not limited to pies, doughnuts and bars for visualizing parts to a whole. Here are some other options available in Excel.
Stacked area charts – show data over time:
Although I think a good-old line chart is better for allowing comparisons in the data:
Treemaps (available in Excel 2016 Office 365) – use with hierarchical data
Sunburst (available in Excel 2016 Office 365) – also use with hierarchical data:
Note: Treemap and Sunburst charts aren’t available as Pivot Charts, yet!
The issue I have with Treemaps and Sunbursts, aside from their sheer size, is that small segments are lost. If you’re not interested in the small segments then that’s ok, but if you want to know what they are, then use a bar chart:
Or a layered bar chart :
Marimekko – categorical data with two variables.
Image credit; www.peltiertech.com
There’s no built-in Excel chart type for the Marimekko, but Jon Peltier of Peltier Tech has an Excel add-in that can do it:
Peltier Tech Chart Utility – available for PC and Mac
Labels – don’t feel that you must always display proportions visually. If you only have one headline figure to highlight, why not just show the number in a label:
Download the Workbook
Enter your email address below to download the sample workbook.
Please Share
If you liked this please click the buttons below to share.
