42% of the world goes to polls around a pie chart – Like it or hate it?

Today let’s have a poll. Lets debate if this pie chart about world elections in 2014 is good or bad.

First lets take a look at the chart

This chart, published by The Economist talks about how 42% of the world population is going to vote this year. Take a look:

My thoughts on this chart

Usually I try to avoid pie and donut charts. But this combination works. I like the simplicity of the chart and the clear message. Although rotated labels around the chart are tricky to read, you get the message quickly.

Even though a typical time line chart or map chart can convey the same information, they do not look as exciting and attention grabbing this one. Also, depicting the time line of events on a map is really tricky (unless you go for animated chart or use too many labels).

Can this be made in Excel?

Gerald, One of our fans on Facebook asked if this chart can be recreated in Excel? That got me thinking… why not?

Steps for creating this chart in Excel

1. First organize the data. You can download election data from electionista. Since electionista site only showed data for next 6 months, I gathered rest of the data from Economist’s chart.

2. Make the 12 sliced donut

In a blank range, type the names of all 12 months and enter 1 in adjacent column for all of them. Now select everything and insert a donut chart.

3. Format the donut chart

• Select the slices of donut and fill all of them with same color.
• Select second slice of the donut. You have to click twice on the second slice, once to select all slices, again to select the particular slice.
• Fill it with another color.
• Repeat the process for every other month (April, June… December). You can just select each of these month’s slices and press F4 to repeat last action (which is filling the color)
• Finally fill the plot area & chart area of donut chart with no color. This makes the donut chart transparent so that anything behind it shows thru.
• Remove any chart titles, legends.

4. Calculations for the pie charts

As per the data, there are 48 elections in 2014. But if you plot these 48, the pie slices will not properly align with the months to which they belong. So we need to insert some gap slices so that each month’s elections are plotted in that wedge.  Do not understand.. see this:

To fix this, we need to insert a few gap slices between elections. How many gap slices ?!?

Well, to answer that question, we first need to find out what is the maximum number of elections in any month?

The answer is 11.

So, we are going to create a pie chart of 12 x 11 = 132 slices. Then, for each month, we just put the names of countries that have election in that month. If a month has only 5 elections (February for example), then we leave the rest of 6 cells in Feb empty. All of this can be done with lookup formulas. INDEX + MATCH to be exact. I am leaving how part of this to your imagination.

Once we have mapped the 48 election dates to 132 cells, we need to separate the country names to 3 columns depending on type of election. Legislative in first column, Presidential in 2nd column and Referendums in 3rd column.

The final calculations look like this:

5. Make pie chart for legislative election dates

Select legislative & dummy columns. Insert a pie chart.

Now format the pie chart.

• Fill color for slices = no color
• no line
• Set data labels to category
• Set the location of data labels to “outside edge”
• Color data labels.
• Set fill color for plot area & chart area to no color as well.
• Remove any chart titles, legends.

6. Repeat the process and create 2 more pie charts

One for Presidential & one for Referendum election dates.

7. Insert another pie chart

For 42% center pie chart. Just type 42% in a cell, 58% in another. Select both, insert a pie chart. Format it.

8. Align all charts

1. Select all charts (by holding ctrl key or using Home ribbon > find & select > select objects tool
2. Go to Format ribbon > Align
3. Select Align in the middle
4. Select Align Center

This ensures that everything is aligned properly.

Now adjust the sizes of these charts as needed until you get desired effect.

Since all charts are transparent, they work like layers to give you the desired effect.

How does the layering work?

This is how the layering works.

Download World Elections in 2014 Excel chart

Click here to download the chart workbook. Examine the charts, formulas and formatting to understand this better.

Our own election – Like it or hate it? World Elections in 2014 chart

Lets have our own election. What do you think about the world elections in 2014 chart? Do you like it or hate it?  Please vote using comments.

Discussion on pie charts

At Chandoo.org parliament, we debate pie charts (and donuts, bars, spiders and scatter plots too) often. Here are some important bills we passed,

• Nightmarish pie charts
• Alternatives to pie charts
• Plot your data around the clock
• CNN’s tax burden pie chart – a good example
• Pie of a pie of a pie chart – good, bad or ugly?
• 3D pie charts are evil