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? 😉
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.
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.