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,
14 Responses to “How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]”
in C3=2016
in C4=3
in C5=1 (the first next year with three Friday the 13ths)
=SMALL(IF(MMULT(--(MOD(DATE(C3+ROW(1:1000),COLUMN(A:L),13),7)=6),ROW(1:12)^0)=C4,C3+ROW(1:1000)),C5)
formula check in the next 1000 years
This will generate a table of counts of Friday the 13th's by year. If I didn't screw it up the next year with three is 2026.
I created a simple parameter table with a start date and end date that I wanted to evaluate. That calculates the number of days and generates a list of those days. Then filter and group. The generation of the list in power query (i.e. without populating a date table in excel) is pretty cool, otherwise this isn't really doing anything than creating a big date and filtering/counting.
let
Source = List.Dates(StartDateAsDate, Days2, #duration(1,0,0,0)),
ConvertDateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddDayOfMonthColumn = Table.AddColumn(ConvertDateListToTable, "DayOfMonth", each Date.Day([Column1])),
AddYearColumn = Table.AddColumn(AddDayOfMonthColumn, "Year", each Date.Year([Column1])),
AddDayOfWeekColumn = Table.AddColumn(AddYearColumn, "Day of Week", each Date.DayOfWeek([Column1])),
FilterFriday13 = Table.SelectRows(AddDayOfWeekColumn, each ([DayOfMonth] = 13) and ([Day of Week] = 5)),
Friday13thsByYear = Table.Group(FilterFriday13, {"Year"}, {{"Number of Friday the 13ths!", each Table.RowCount(_), type number}})
in
Friday13thsByYear
With the parameters replaced by values should you want to play along at home. This runs for 20 years starting on 1/1/2016.
let
Source = List.Dates(#date(2016,1,1), 7300, #duration(1,0,0,0)),
ConvertDateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddDayOfMonthColumn = Table.AddColumn(ConvertDateListToTable, "DayOfMonth", each Date.Day([Column1])),
AddYearColumn = Table.AddColumn(AddDayOfMonthColumn, "Year", each Date.Year([Column1])),
AddDayOfWeekColumn = Table.AddColumn(AddYearColumn, "Day of Week", each Date.DayOfWeek([Column1])),
FilterFriday13 = Table.SelectRows(AddDayOfWeekColumn, each ([DayOfMonth] = 13) and ([Day of Week] = 5)),
Friday13thsByYear = Table.Group(FilterFriday13, {"Year"}, {{"Number of Friday the 13ths!", each Table.RowCount(_), type number}})
in
Friday13thsByYear
=MATCH(3,MMULT(N(WEEKDAY(DATE(C3+ROW(1:100)-1,COLUMN(A:L),13))=6),1^ROW(1:12)),)+C3-1
It should be pointed out that Alex's solution, unlike some others, has the additional advantage of being non-array. My solution was nearly identical but with -- and SIGN instead of N and 1^.
=C3-1+MATCH(3,MMULT(--(WEEKDAY(DATE(C3-1+ROW(1:25),COLUMN(A:L),13))=6),SIGN(ROW(1:12))),0)
Sub Friday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim r As Long
Range("C7:C12").ClearContents
StartDate = CDate("01/01/" & Range("C3"))
EndDate = CDate("31/12/" & Range("C3"))
r = 7
For x = StartDate To EndDate
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Cells(r, 3) = Month(x)
r = r + 1
End If
Next
End Sub
Calculate next year with 3 Friday 13th. Good for 100 years different from year entered in cell C3
Sub ThreeFriday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim WhatYear As Integer
Dim Counter As Integer
Range("E7").ClearContents
StartDate = CDate("01/01/" & Range("C3") + 1)
EndDate = CDate("31/12/" & Range("C3") + 100)
Counter = 0
For x = StartDate To EndDate
If WhatYear Year(x) Then
WhatYear = Year(x)
'Different year so reset counter
Counter = 0
End If
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Counter = Counter + 1
If Counter = 3 Then
WhatYear = Year(x)
Exit For
End If
End If
Next
Range("E7") = WhatYear
End Sub
*RE-POST as not equal did not show earliuer
Calculate next year with 3 Friday 13th. Good for 100 years different from year entered in cell C3
Sub ThreeFriday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim WhatYear As Integer
Dim Counter As Integer
Range("E7").ClearContents
StartDate = CDate("01/01/" & Range("C3") + 1)
EndDate = CDate("31/12/" & Range("C3") + 100)
Counter = 0
For x = StartDate To EndDate
If WhatYear NE Year(x) Then
WhatYear = Year(x)
'Different year so reset counter
Counter = 0
End If
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Counter = Counter + 1
If Counter = 3 Then
WhatYear = Year(x)
Exit For
End If
End If
Next
Range("E7") = WhatYear
End Sub
earlier*
I've a doubt with using array formula here.
In sample workbook, I tried to replicate the formula again.
=IFERROR(SMALL(IF(WEEKDAY(DATE($C$3,ROW($A$1:$A$12),13))=6,ROW($A$1:$A$12)),$B7),"")
For this I selected C7 to C12, and typed the same formula and pressed ctrl+alt+Enter. But in all cells it is taking $B7 (and not $B7, $B8, $B9.... etc)
and since it is array formula I can't edit individual cell.
Please guide.
Thanks
Hi Chandoo,
Cool stuff. You need to clarify that the answer of 5 represents the 1st month in the year that has a Friday the 13th, and not the number of Fridays the 13th in the year. Subtle, but important difference.
Thanks,
Pablo
I like the MMULT() function far more, but here's how I would have tackled it. It uses an EDATE() base and MODE() over 100 years. I'm assuming that 100 years is enough time to catch the next year with 3 friday 13th's. Array entered, of course.
{=MODE(IFERROR(YEAR(IF((WEEKDAY(EDATE(DATE(C3, 1, 13), ROW(INDIRECT("1:1200"))))=6), EDATE(DATE(C3, 1, 13), ROW(INDIRECT("1:1200"))), "")), ""))}
Finding all the Friday the 13ths in a Year:
=SUMPRODUCT((DAY(ROW(INDIRECT(DATE(C3,1,1)&":"&DATE(C3,12,31))))=13)*(TEXT(ROW(INDIRECT(DATE(C3,1,1)&":"&DATE(C3,12,31))),"ddd")="Fri"))
{=sum(if(day.of.week(DATe($YEAR;{1;2;3;4;5;6;7;8;9;10;11;12};13);1)=6;1;0))}
just list the years