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

Advertisements
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

Comments, suggestions, corrections are welcome.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s