Want some pies?

First of all, this blogpost is about Pie Chart, but not about how to make a Pie Chart.  Indeed, I am not going to show the steps of making Pie Chart here.   Also I am not intended to discuss whether Pie Chart is good or not to visualize data here. 🙂

One day my brother asked me how to show the data of “Jun” for the below Pie Chart:Excel Tips - Stacked Pie 1.png

As you can see from the above screenshot, the data contains 6 series.  My question to him  was:  “How are you going to plot that into Pie Chart by using Paper and Pencil?”

 

A flash in my mind is something like this:Excel Tips - Stacked Pie 2.png

Seriously??? How are we supposed to visualize the data?

Joke aside, he just wanted something as simple as below:

Excel Tips - Stacked Pie 3.png

BUT with interactivity.

And you know what?  His chart was indeed a Pivot Chart.  I told him to use a slicer and simply select the month he needed.  Very simple.

Nevertheless, that simple request inspired me to create a combination of Stacked (3D) Pies and 2D Pie to cater for his requirement.

Excel Tips - Stacked Pie 4.png

 

So do you want some pies?

The Recipe:

  • Ingredients: Pie Charts, Option button x 6, INDEX function, Icing sugar (Conditional Formatting), a little time and a little patience.
  • Tools: Excel, what else?

The Making:

Stacked Pies

  1. Start with the top-layer Pie: First to create a 3D Pie using Jan data
  2. Trim all the ugly elements on your pie… e.g Title, legend, border, chart area and plot area (both set to no-fill)…
  3. Duplicate the Pie using Feb data, make it a bit larger than the 1st Pie
  4. Repeat above steps with Mar data, then Apr, May, Jun data one by one
  5. Stack them with patience

Option Button x 6

  • Go to Developer Tab –> Insert, Option Button.Display Percentage Row or Column 3

Let’s try to make 2 buttons by following screenshots below to get a feel of it first.

Display Percentage Row or Column 4(Right-click it)

Display Percentage Row or Column 5 (Set Cell link to A1)

Show percentage Row or Column 6 (Try and observe)

  • Got it?
  • Repeat the steps to make 6 option buttons please.

After it is done, please set the cell link to $M$1, where underneath we are going to put the dynamic data to. (Tip: You may make one first, then set the cell link to $M$1, and then copy the Option button and paste 5 times)

The Dynamic data range

  • In M2, input
=INDEX(C2:H2, $M$1) 'Copy down to M6

Now you should have your data set up as below:excel-tips-stacked-pie5

(Note: The value in M1 depends on which option button is selected… try it out)

The Dynamic 2D Pie

  • Simply create a 2D Pie using the data from M2:M6

Excel Tips - Stacked Pie6.png

Tidy up the dish

  • Now we have all the key elements.  Just make them look pretty
  • This should you how it looks like…

Excel Tip - Stacked Pie 7.png

 

Final touch – Putting the icing sugar

  • Select the cells in between the 3D pie and the option button
  • Set Condition Format based on cell value in $M$1

excel-tips-stacked-pie-8

  • Repeat for 2nd to 6th buttons. (Note: change the formula for conditional formatting accordingly).

The final product

Excel Tips - Stacked Pies.gif

Taste good!?  I hope you enjoy it.

You may download the sample file to follow through.

Guess if had done this for my brother finally?

No.  I didn’t.

Instead I propose the simple stacked column (shown below) to him and he seemed to be happy with that.

excel-tips-stacked-pie-end

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Chart and tagged , , . Bookmark the permalink.

2 Responses to Want some pies?

  1. Michael (Micky) Avidan says:

    Hi, Wong,
    Could it be that I’m the first to wonder why don’t you put a link for downloading the file you are working in each ands every tip ?
    The present one is a simple table, but I saw some very heavy and complicated tables that could be uploaded by you for the benefit of your followers who want to practice your tips.
    Well…?
    Thanks,
    Michael

    Like

Leave a Reply to Michael (Micky) Avidan Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.