Google Sheets Advent Calendar

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.

1. Settings

On a separate tab, I created a settings table to hold key variables:

Google Sheets Advent Calendar Settings

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:

Advent Calendar in Sheets Drop Down

This was translated into a number by this SWITCH function:

=SWITCH(B4,

"None",1,

"Light",0.75,

"Medium",0.5,

"Heavy",0.25,

"Total",0)

I have called this number “snowfallParameter”, which is used in the snowfall formulas (more on them below).

settings Named Range

2. Google Sheets Advent Calendar Doors

Advent calendar in Google Sheets

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:

=IF(AND(TODAY()>triggerDate,D6),IMAGE("https://cdn.pixabay.com/photo/2017/11/28/17/01/merry-christmas-2984138_960_720.jpg",2),1)

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

snowfall in Google Sheets

This formula for the snowfall in the Google Sheets Advent Calendar is in every blank cell:

=IF(RAND()>snowfallParameter,CHAR(CHOOSE(RANDBETWEEN(1,3),10052,10053,10054)),)

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.

If it is, then the CHAR function and the CHOOSE function are used to show a snowflake. The numbers 10052, 10053, and 10054 generate different snowflakes with CHAR, e.g. try

=CHAR(10052)

snowflake CHAR function

Note: the snowfall will update every minute. The GIF images in this post have been sped up to show the effect.

4. Formatting

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:

Shadow Effect

Finally, remove those gridlines!