- Megan Johanson
Visualizing 81 Delivery Date Predictions
Is due date an accurate prediction of when a baby will be born? Can my family, friends, and coworkers do a better job at predicting when baby will arrive?
My due date is May 1st and I couldn’t miss the opportunity to poll my friends, family, and coworkers on their predictions, as well as try out a new-to-me type of visualization.
I solicited birth date predictions from my family, friends, and coworkers over the past few months and turned the data into a heat map calendar. Some people made guesses based on my mom’s delivery timelines, some based on how I was carrying the baby, and some based on the closest new moon to my due date.
Follow along as I transform the predictions into a visualization and then stay tuned to see if their most frequent guess was right!
Setting Up the Excel Calendars
First, I set up three calendar tables in Excel for the few weeks before and after my due date. I wanted to know whether predictions differed between family/friends and coworkers, so one table was for family and friends, one was for coworkers, and one was for everyone combined.
Calendar table with dates for a few weeks before and after my due date.
Setting Up the Calendar of Votes
Next, I copied each calendar to the right of the original one and deleted the dates. I entered the numbers for how many votes each date got into the relevant cell. So, for my family and friends calendar, it looked like this:
Table showing the number of votes for each day surrounding my due date.
For the combined calendar, I created a formula that would add together the votes from each of the two calendars for each day. There were a total of 81 guesses.
Formatting the Calendar for Visual Insights
Then, I applied conditional formatting for the new tables, where the cells with the higher numbers/votes were filled with a darker blue.
Table of votes conditionally formatted with darker cells indicating more votes.
Finally, I made a few formatting tweaks:
1. I reduced the width of the columns and made them uniform by going to Home – Cells – Format, and choosing Column Width. There I set the width to 6.
2. I hid the vote counts in the conditionally formatted tables by highlighting each table of votes, right clicking, and selecting Format Cells. Under the Number tab I selected Custom and deleted what was in the Type: box (see image below). Instead I entered three semicolons “;;;” which serves to hide the numbers while keeping the formatting visible. Then I clicked OK. If you click in a cell you can still see the number in the formula bar.
Formatting the data cells to hide the number of votes.
After this formatting, the table looked like this:
Conditionally formatted table of votes with no visible numbers.
Layering the Calendar Dates and Votes
Here is where I ran into a challenge. I wanted the vote counts to be represented by the color of the cell, but I also wanted the date to appear in the cell. After some googling I didn’t see a straightforward way to do that in Excel without complex formulas or coding, so I did a simple workaround.
I made sure the columns for the original calendars with dates were the same width as the colored calendars and then carefully copied and pasted into PowerPoint *Keeping Source Formatting* first the colored calendar and then the calendar with dates.
I moved the calendar with dates over top of the colored calendar until the dates were aligned under the correct days of the week. I also added a title and expanded the subtitles to create a standalone visualization. To group everything together I selected all the tables and copied them, then I pasted them as a picture.
The end result looks like this:
Final visualization showing birth date predictions for each day.