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!

There are essentially four parts to this advent calendar:

1. A settings sheet
2. The formulas for the doors
3. The formulas for the snowfall effect
4. 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:

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:

```=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).

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

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)`

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:

Finally, remove those gridlines!

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.

1. Bailey says:

SWEET! Thanku so much for the clever holiday gift 🙂 I’m excited to share it with other Excel nerds. Cheers!

2. Kathy A Burnett says:

How cool is this! Thank you for the complete instructions on how you did this! I’m new to spreadsheets and enjoy learning how to navigate them!

3. David Barker says:

The Advent Calendar is a wonderful, creative way to play with Google Sheets.

However there doesn’t appear to be a way to get the snowflakes to recalculate more frequently than every minute. Has something changed in sheets to prevent this?

1. Ben says:

Thanks, David.

Unfortunately, the most frequent spreadsheet refresh (to recalculate the random number) is every minute. I sped up the GIFs in the post to show the snowfall. I will go back and mention that though!

1. David Barker says:

Thank you for the explanation. … And thanks again for your weekly tips! I look forward to them every week.