For many people when they think of a chart that shows parts to a whole, a pie chart is the first choice. And if you’ve known me for any length of time you’ll know I pretty much despise data in a pie.
Don’t get me wrong, I love pies. It would be un-Australian not to like pie (Australians eat between 270M and 300M pies per year. That’s 12 each). I just think pies are for eating, not data (most of the time).
Parts to a Whole Excel Charts
So if Pie Charts are evil then what’s the alternative?
I’m glad you asked. And before you jump in with ‘Stacked Column Charts’, the answer is no, definitely not. They’re second on my bad chart list, but that’s a rant for another day.
The alternative is almost always a bar chart, but let’s start with our example data:
Yes, your eyes don’t deceive you. It’s the consumption of pies broken down by pie category, and within that the type of pie 🙂 Oh, and if you’re wondering what a ‘floater’ is, it’s this.
Download the Workbook
Parts to a Whole Bar Chart
Let’s say I want to see what the overall split is by Category, and then the breakdown within those Categories. I could use 3 separate pie charts, or even a really bad pie chart like this:
But why would I when I can use a bar chart that’s quick and easy to interpret like this:
The key to this chart is all in the layout of your data. There are 4 series in the bar chart above and you can see them in columns D:G of my data table below:
Recreating the Parts to a Whole Bar Chart
To recreate my Parts to a Whole Excel chart:
- Insert chart: Select the data > Insert tab > Bar Chart
- Categories in Reverse Order: Double click/right-click the vertical axis > Format Axis > Axis Options > check the box for Categories in reverse order. This will sort the data in the same order as the source data, which is in descending order by pie type within each category.
- Set Overlap and Gap Width: Edit the series; right-click/double click one of the bars to open the Format Data Series dialog box > Series Options > set series overlap to 100% and the Gap Width to 0%
- Rearrange series order: Right-click the chart > Select Data > rearrange the series using the up/down arrows so that your ‘Total..’ series are at the top of the list:
- Format colours: With the bars in the chart selected add definition to them with a white outline (Format tab > Shape Outline), and set your colours for the bars (Format tab > Shape Fill):
- Labels: Add labels to the bars if you wish, but make sure you remove the horizontal axis (click once to select it > Delete), otherwise you’re just duplicating information. You will also have to delete the extra labels for the ‘Total…’ series so you just have one label for each. You’ll see what I mean if you try it yourself.
- Title: Give your chart a title. Don't forget you can make your title more than just a heading.
Job done. Time for a Pie Floater, anyone? 😉
Want More?
The bar chart above wasn't particularly complicated, but because I know a few tricks for formatting charts I was able to create something very effective from the basic charts available on the Insert Chart menu.
And it's skills like this that can make the difference between being able to quickly and clearly communicate your message, or burying it in a pie poor chart.
If you'd like to learn more charting tips and techniques like these, check out my Excel Dashboard course.
Please Share
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
Jagan Mohan Raghu says
3 words – Fantastic , Mind blowing & Superb.
You are simply Genius
I have used this technique of yours. My colleagues are just amazed
Thanks a ton 🙂
Mynda Treacy says
That’s great, Jagan, thanks! Glad you were able to use it.
Mynda
John Moore says
No doubt Pie Charts have their uses in Excel, especially in the earlier days of Excel Charting and data metrics, but with the advances in Excel charting it seems that pie charts haven’t really moved that much ( there is only so much you can do with a circle ). The more advanced Excel has become and the users of Excel have increased their knowledge and understanding what else can be done, the less we tend to use pie charts … I actually use one pie chart in all the dashboards I create for others … even then I did not want to use it, but the client wanted it, so I made it dynamic in that it changes with the click of a dropdown list ( makes me feel better about using one ). In today’s analytics driven business world there is a need to utilise the best tools we can, most of the time, that doesn’t include pie charts.
Mynda Treacy says
Well said, John.
Mandeep says
The data in the example is static. Can it be used for dynamic data?
How to use this type of a chart be used for data that keeps changing every month.
Mynda Treacy says
Hi Mandeep,
Sure, you can link any chart to dynamic data with dynamic named ranges.
Mynda
Col Delane says
Hi Mynda
Without suggesting pie charts are better overall, the only “criticism” (??) I offer is that a pie chart (being a circle) gives a definite view of what the whole is and displays the correct proportionality, whereas a bar chart does not – unless the scale of the horizontal axis is fixed from 0 to 1 and there’s a border around the plot area to visually define the extent of the whole.
Other than that, your reasoning for bar over pie makes a lot of sense.
Mynda Treacy says
Hi Col,
Interesting points you make. For me the purpose of representing items as a proportion of a whole is to make comparisons from one item to the next and understand which items are significant/insignificant etc.
Unlike a Pie chart, the bar chart isn’t an area chart so the overall size of the chart area isn’t required to make comparisons between the data (this means they can take up a small space and still convey the message). This also means that fixing the axis at 1 doesn’t change how the lengths of the bars compare to one another (see below), however the scale should always begin at zero.
An interesting point Ziggy made is that the representation of the categories (Savoury and Sweet) with columns that take up the whole width of the items may lead people to interpret the size of the area, as opposed to just the length of the bars. A solution to this may be to include a horizontal axis to make it clearer that this is a bar chart, not an area chart.
Cheers,
Mynda
Michael Pierce says
Nice approach to showing two levels of data in an easy-to-read chart! If I wanted my headline/message to be: “Aussies love their savory pies, but apple is a strong contender,” how would you format the chart differently?
Mynda Treacy says
🙂 in that case, Michael, I’d sort the chart by pie type and omit the category bars since it would appear from the headline that the pie type comparison is more important than the category.
Mynda