The other day I was asked to help with creating a list in which to record birthdays of employees in a department. The idea behind this was that everyone knows when your birthday is and that your work colleagues can then congratulate you. If you just want the finished list, scroll down to the bottom for the download!
Keeping a list in excel and updating it is a dead simple activity you might think… and mostly that is true, unless you are required to produce that list in a certain format, which then makes each change a bit of annoying task that you would rather offload on someone else.
The birthday list reminded me of exactly this problem.
It was expected to have a clean look and feel so that it is easy to read (thus not just standard Excel cell formatting) as well as meet these three other requirements:
- Peoples Names and their birthday had to be visible…
- …birthdays were to be grouped by month…
- …and they had to appear in the order in which the birthdays happen!
Creating the List
This problem called out to me to be solved in a pivot table, because it neatly demonstrates just of how versatile of a tool pivot tables are!
- The first step, as always when working with a list, was to create a table (List Object). Like this, we ensure that when data is added to the table, that it will automatically be included in the pivot table that we will create.
The table will look like in the screenshot above.
- Next was generating a pivot table. Unique about this pivot table is that we place both of the two fields in the table in the “Row Labels” section.
- We then group the birthdays automatically using the pivot table “grouping” function. We only need to select months, and excel does the rest, neatly returning a table with all names displayed in the month where the persons birthday is.
- In order to actually show the date of birth per person, we need to use a small trick. We take the birthday field, a second time, and use it in the “values” section of the pivot table. Excel automatically recognizes the information as dates. Therfore it will display it as “Count of Bday”. Since excel saves dates as numbers (in the background), and since each row only contains one birthday, we can change the pivot table to show the Sum of the value (i.e. 42678) which we then just need to reformat as a date. Like this, we can display the birthday in the values field!…at this point we are almost done. We have already achieved the first to requirements. Now for the last one…
- The last task requires that we insert a helper column in the source table. In this column we calculate on which day of the year the birthday will fall, and thereby produce a list that can be sorted from 1 to 365. I used the follwoing formula to calculate these values…=DATE(YEAR(TODAY());MONTH(B2);DAY(B2))-DATE(YEAR(TODAY());1;0)
We then place this new field in the “Values” section of the pivot table. Placing the cursor on one of the values, we can use the “sort ascending” button. The values now appear in the order that we wanted, meeting the 3. and final requirement.
- Because this additional helper column doesn’t really need to be seen by a user, we can simply hide it by hiding the column. When the list gets updated, it will continue to work in the hidden column.
Now we have a finished birthday list that can easily be updated and maintained!
You can downloaded and used this template for your own company if you like: Birthday List