• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Parts to a Whole Excel Charts

You are here: Home / Excel Charts / Parts to a Whole Excel Charts
Part to a whole Excel Charts
May 18, 2016 by Mynda Treacy

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:

Parts to a whole Excel charts 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

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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:

really bad pie chart

But why would I when I can use a bar chart that’s quick and easy to interpret like this:

Parts to a whole Excel charts

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:

Part to a whole Excel data

Recreating the Parts to a Whole Bar Chart

To recreate my Parts to a Whole Excel chart:

  1. Insert chart: Select the data > Insert tab > Bar Chart
  2. 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.
  3. 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%
  4. 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:

    Excel chart source data

  5. 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):

    Format Excel chart series

  6. 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.
  7. 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.

Part to a whole Excel Charts

More Excel Charts Posts

picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.


Category: Excel Charts
Previous Post:Interactive Excel ChartsInteractive Excel Charts
Next Post:Power Query and Power Pivot Definitive GuidePower Query vs Power Pivot

Reader Interactions

Comments

  1. Yve

    February 10, 2021 at 11:38 am

    This is informative but I wish there were more instructions 🙁 I’m someone who needs this kind of information to be very explicit and detailed step by step.

    Would you consider doing this with a simple example? Would you consider something like years 2008 to 2013, total SUVs purchased, and then just 2 brands eg. Suzuki and Honda.

    So column A would have the years, column B-the percentage of Suzukis bought each year, column C-the percentage of Hondas bought each year and column D, the total percentage [but have the total percentage NOT sum to 100]. Eg. in 2008, 15% Suzuki, and 20% Honda, so total for 2008 is 35% etc.

    I searched on YouTube and checked out some other websites as well but they don’t seem to explain what you have here, which is a layered bar chart. Your tutorial is exactly what I want but I can’t seem to follow it. Don’t get me wrong; many people understood by looking at the comments but for me, I’m not totally getting it 🙁

    Reply
    • Mynda Treacy

      February 10, 2021 at 12:26 pm

      Hi Yve,

      There are explicit steps listed under the heading “Recreating the Parts to a Whole Bar Chart” and you can download the file and replace my data with yours. At which point are you getting different results? Perhaps you can post your question and Excel file in our forum where we can see what you’ve attempted based on the instructions in this tutorial and we can help you further.

      Mynda

      Reply
      • Katie

        October 5, 2021 at 12:43 am

        >There are explicit steps listed under the heading “Recreating the Parts to a Whole Bar Chart”

        Except you skipped MANY steps to get to this point. How did you format the data in the PivotChart Fields in the first place in order to have a populated Axis to click on in Step 2? This information would actually be super helpful to new users.

        Reply
        • Mynda Treacy

          October 5, 2021 at 9:46 am

          Hi Katie,

          It’s not a Pivot Chart. If you download the file you can see how the data is laid out.

          Mynda

          Reply
  2. Sergey

    August 26, 2018 at 7:59 pm

    Good Day, Mynda! Thank for this great post!))
    “6. 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.”

    I selected only one Total Savoury/Total Sweet bar (the middle one, I guess, that’s better) by double clicking on it,Right Mouse, Add Labels, just entered the text needed.

    Spared myself several clicks)))))

    Reply
    • Mynda Treacy

      August 26, 2018 at 8:23 pm

      Good tip, Sergey! Thanks for sharing.

      Reply
  3. Jagan Mohan Raghu

    August 7, 2017 at 1:52 am

    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 🙂

    Reply
    • Mynda Treacy

      August 7, 2017 at 8:45 am

      That’s great, Jagan, thanks! Glad you were able to use it.

      Mynda

      Reply
  4. John Moore

    May 27, 2016 at 6:44 pm

    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.

    Reply
    • Mynda Treacy

      May 27, 2016 at 7:50 pm

      Well said, John.

      Reply
  5. Mandeep

    May 19, 2016 at 7:17 pm

    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.

    Reply
    • Mynda Treacy

      May 19, 2016 at 8:13 pm

      Hi Mandeep,

      Sure, you can link any chart to dynamic data with dynamic named ranges.

      Mynda

      Reply
  6. Col Delane

    May 19, 2016 at 7:10 pm

    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.

    Reply
    • Mynda Treacy

      May 19, 2016 at 8:08 pm

      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.

      part to whole chart

      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

      Reply
  7. Michael Pierce

    May 19, 2016 at 4:35 am

    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?

    Reply
    • Mynda Treacy

      May 19, 2016 at 9:48 am

      🙂 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

      Reply
      • John Lane

        January 20, 2018 at 5:27 am

        I am looking for a chart of other visual where the parts are actually greater than the whole. Example my budget is $100 but my 6 variables equal $125.

        Reply
        • Mynda Treacy

          January 20, 2018 at 6:54 am

          Hi John,

          I’d use a different chart to plot variances against budget. Here is an example: https://www.myonlinetraininghub.com/charting-variances-in-excel

          Mynda

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.