## 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:

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:

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

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

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.

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

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

(Right-click it)

(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:

(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

### Tidy up the dish

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

## 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

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

### The final product

Taste good!?  I hope you enjoy it.

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

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

• MF says:

This is actually a very good suggestion. I’ve added the sample file to the post. Thanks Micky. Cheers,

Like

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