This year, I created a Google Sheets Advent Calendar, which you can see in action here:
It was a fun project with some interesting techniques, which are explored below.
You could easily modify it for your own example, or use these techniques in different scenarios.
Plus, if you’re too cheap to buy a physical advent calendar, this lets you enjoy the fun of opening a door each day to reveal something, but for free!
How To Create An Advent Calendar In Google Sheets
There are essentially four parts to this advent calendar:
- A settings sheet
- The formulas for the doors
- The formulas for the snowfall effect
- The Sheet formatting
Let’s look at each of these sections in turn.
On a separate tab, I created a settings table to hold key variables:
Instead of hardcoding the year into the formulas, I put it into a separate cell with the named range “year”.
Then I added a date cell, with the name “triggerDate” to hold the last day of November. This is used as a check date to compare with today’s date, so that the formulas for each “door” only work when it’s the correct date. I made this a variable so that I could change it and check the logic of all the formulas without having to wait for December.
Next, the snowfall variable is on row 4. I used a human-friendly drop-down menu (using data validation) to choose the snow conditions:
This was translated into a number by this SWITCH function:
I have called this number “snowfallParameter”, which is used in the snowfall formulas (more on them below).
2. Google Sheets Advent Calendar Doors
You can only open the door for that day, not the ones in the future. So, for example, on the 1st of December, I can only “open” door 1. Clicking the other checkboxes will not work yet.
The formula for the first door is:
where D6 is the checkbox above the door that will show the image when checked.
However, the formula will only let you see the image for that day (and days before). Clicking on checkboxes for days in the future won’t show the images because the AND function checks whether today’s date is in range.
The images come from Pixabay, and are used under the Pixabay license.
I’ve merged cells — ooh, controversial! — to get the square shapes for the doors.
3. Creating The Snowfall With Formulas
This formula for the snowfall in the Google Sheets Advent Calendar is in every blank cell:
where “snowfallParameter” is the named range that points to the cell with a number between 0 and 1 (see Settings above).
An IF function checks whether a randomly generated number, generated by RAND(), is greater than the “snowfallParameter” number.
The title was generated by this formula, using the “year” named range to link it to the settings page:
="ADVENT CALENDAR "&year
I used the Mountains of Christmas font, which I added through the More fonts menu.
The classic red and green Christmas colors were used to give the calendar a Christmas theme.
I used narrow rows and borders to create a shadow effect behind the title and the doors. It’s easier to see in this image with the gridlines showing:
Finally, remove those gridlines!
Google Sheets Advent Calendar Template
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it might be because of your organization’s Google Workspace settings.
In this case, right-click the link to open it in an Incognito window to view it.