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
- Select all charts (by holding ctrl key or using Home ribbon > find & select > select objects tool
- Go to Format ribbon > Align
- Select Align in the middle
- 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,
15 Responses to “A Gantt Chart Alternative – Gantt Box Chart”
That's a great idea.
Maybe the planned End Date should be highlight more.
I don't know how it would look like (nor how to do it yet), but what if instead of finishing the bold line to the best case End Date, it finishes to the realistic End Date?
The idea is ok, I think other project management tools have this, already? Maybe not.
Gantt charts in my view are about the signal most unless thing in the world, theres no way you can look at one thats more that a little complex and understand what it's telling you. I'm going to write a diatribe on project management at some point, its one of my pet areas I think!! 😉
The issue I have with this chart Chandoo, is that Tasks need to be linked to each other, so they should inherit the uncertainty, which would mean the as you moved down chart the lines would be miles apart for later tasks, and you might have to add lots of lines for subsequent tasks to cover the various outcome of it's parents.
Having said that, for the high level board summary, it's a nice way to go, it it appeals to the management 😉
thanks Chandoo, great post.
Ross
Whoooa !!! That's a very clever idea Chandoo. I really love it.
I think i'll update my gantt project sheet with that idea soon (remember my template ?)
@ross : you can link start date to the end date of the previous task in your data. The only problem I still se is to which end date (real ? planned ? best ?) in order to have average amount of information.
If best end date, you'll tend to increase uncertainty at the end of chain, although if you link to real end date, uncertainty will be decreased too much, leading in both cases to wrong management direction.
Maybe planned till the task is finished then real will do the job ?
Hey chandoo, this looks good and this would definite add value in production planning / scheduling. Uncertainity in finishing a task is very high in production scheduling and this could give an insight or a bird eye view of possible shipments we can have....
I've always been frustrated by the limitations of gantt charts. Will definitely use this, I've always struggled with how to succinctly communicate the uncertainty of certain tasks without confusing stakeholders.
I like this, I think it's a very effective way of showing how a timeline can change and which parts of a project need close attention.
@Cyril / @Ross: I would intially link the the start date to the planned end date of the previous task, with the chart updating when a task has been completed to reflect the true end date.
Or what about giving a drop-down selection box to allow the user to see the chart based on planned/best-case/worst-case end dates?
Like the idea. Have found that Excel is more flexible than MS Project for graphical solutions. The "Best Case"\"Worst Case" metrics are theoretically appealing but once the project and\or phase commences their reliability diminishes. A chart like the above that showed Planned Start, Planned End, Replan End Start, Replan End Date, Number of Replans the Start and End Dates, and Actual would provide an active, actionable view of each task\phase. It would also highlight the areas which are riskiest.
It is always amazing how flexible excel can be.
My question is how would the chart show a scenario where the date moved up? If a task is dropped or the duration of the task is significantly reduced by applying more people or machinery to the task, the dates will move up.
The gantt chart has been around for a long time, but it is still quite useful to show progress.
Cheers,
B
I like the idea but seems bit complicated in case of long projects involving numerous activity.
Also, reading and explaining is required hence not feasible where plans are just send to audience for approval.
Cheers
SY
Great idea Chandoo,
When I was reading this idea regarding delivery dates, another thought popped into my mind, how can you show the uncertainty with MONEY!!
In this case, applies to cost management or even a normal budget, you think?
Would Box Chart and Gannt Chart help to understand the best case, middle case and worst case when money is spend or planned with these three risks are involved?
I imagine that this chart could help people who write their budgets get a better understanding of risks affecting their spending.
Peter
Chandoo,
I like it. How would you display an entry once it has been completed (actual)?
Thank you,
Matt
From what you have shown so far I think that this box Gantt chart is awesome! I think that this could be an extremely useful tool.
I can't wait to learn how to make my own charts in Excel.
Will the methods that you are going to teach us work in 2003 as well?
[...] Firday, we proposed a new chart for showing project plans. I chose an ugly name for it and called it Gantt Box [...]
You need to read Eli Goldratt's Critical Chain. The uncertainty you are looking for should be accounted for in a project buffer. Not at each task level.
Further you should spend time understanding Agile Development. This would have you plan only in 1-3week iterations. This allows you to embrace changes to work not yet started, and for your customer to re-direct your course at regular intervals (after each iteration) throughout your project. keyword search: Agile Scrum
These items will show you that you are solving a tracking problem for something that you can entirely avoid!
[…] Chandoo.org’s Gantt Box Chart. […]