Contextures

Excel Advent Calendar

Use one of these free Excel Advent Calendar workbooks to count down the days until Christmas. Choose a macro or no-macro version.

Introduction - Excel Advent Calendars

When our kids were young, we always bought Advent calendars, filled with little chocolates. Every morning in December, they would look for a door with that day's number, and open it. It was a fun way to count down the days until Christmas! You can learn more about Advent Calendars on Wikipedia.

Now, the kids are grown, so I've made Excel Advent Calendars instead. The "treats" behind each door are little pictures, instead of chocolates. They're not as yummy, but they have fewer calories!

Here's a quick list of the calendars, so you can go to the one that you're interested in. The download links are at the end of the page.

With Macros
  • Macros 1 - Red background, click shapes to show the treats
  • Macros 2 - Blue background, formulas on calendar doors
  • Macros 3 - Reset button, Test Mode, month check
No Macros
  • No Macros 1 - Numbers and Pictures from Windings 2 Font
  • No Macros 2 - Background image, conditional formatting

Advent Calendar - Macros 1

This was the first Excel Advent Calendar that I built, way back in 2009. It has a red background, with green shapes as the 24 doors.

Red background

To open a door, click on it.

Note: The workbook has a bit of cheat protection, so you can't uncover the days ahead of time

click on shape

A macro runs, and changes the shape's fill colour to "No Fill", so you can see the picture behind the shape. You can cover the shape again, by clicking on the border of the numbered shape.

click on shape border

Advent Calendar - Macros 2

My second Excel Advent Calendar, built in 2010, was more challenging. It has a dark blue background, with blue shapes as the 24 doors.

Instead of numbers on the doors, there are simple formulas. Each formula calculates to a number between 1 and 24.

Blue background

Each day, click on the shape with the formula that calculates to that day number, to reveal the Christmas picture behind it.

For example, on December 1, click the shape at the top left, with the formula: = 2 – 1

click the right formula

A macro runs, and changes the shape's fill colour to "No Fill", so you can see the picture behind the shape. To cover the shape again, click the border of the revealed shape.

click the shape border

This Excel Advent calendar has cheat protection too, and shows a warning message.

click the shape border

If you'd like to make the formulas harder, or change them to numbers, go to the Lists sheet.

In column B, change any of the formulas, or type numbers in column B, to make a simple Advent calendar. Then, after you change the formulas, click the "Update Formulas" button, to update the formulas in the Advent Calendar.

click the shape border

Advent Calendar - Macros 3

This version of the calendar is from 2015, and was based on the 2010 version.

Red background

There are a few new features:

  • A month check, just in case you open the file in a month other than December
  • A test mode cell, where you can select Yes, to test in other months
  • A Reset Calendar button, so you can close all the windows again, after testing

Red background

Advent Calendar - No Macros 1

Some people prefer Excel workbooks that don't have macros, so in 2018 I created a no-macros Excel Advent Calendar. This version uses formulas and conditional formatting.

The main page uses Wingdings font, because it has both numbers and pictures. (WingDings 2 also has numbers and pictures) The cells have red fill colour, with white font colour.

Conditional formatting changes the fill colour to green.

Advent Calendar

On another sheet, the CHAR Function pulls the number and picture options for each square, based on the character numbers. This formula is in cell C3, to create the one or two digit number.

=CHAR(D3)&IFERROR(CHAR(E3),"")

Then, the calendar shows the number or picture, depending on the current date. This formula is in cell B3, the Show column.

=IF(CurrMth<>AdvMth,[@Num],IF(CurrDay<A3,[@Num],[@Pic]))

  • If the current month (CurrMth) is not 12 (AdvMth), result is number from column C (@Num)
  • If the current day (CurrDay) is less than the value in column A, result is number from column C (@Num)
  • Otherwise, result is picture from column F (@Pic)

CHAR formulas for Advent Calendar

Advent Calendar - No Macros 2

This was the second no-macros Excel Advent Calendar that I build in 2018. The WingDings version seemed a bit drab, so I wanted to see if there was a no-macros way to make a prettier calendar.

For this version, I added a Background image on the worksheet, with a 6 x 4 grid of Christmas pictures.

Red background

Then, I filled all the cells with dark blue, except the cells over the grid -- those have no fill.

Next, I added conditional formatting, to cover the cells with green fill, until the number matches the current date. After you reach that date, the picture is automatically revealed.

Red background

Download the Calendars

To download any of the Excel Advent Calendars, use the links below.

Macro Versions

The zipped files for these downloads are in xlsm format, and contain macros. Enable macros when you open the workbooks.

No Macro Versions

The zipped files for these downloads are in xlsm format, and DO NOT contain macros.

Related Links

Holiday Dinner Planner

Excel Gift Ideas

Excel Advent Calendar

Chicken Dinner Planner

Excel Christmas Tree - scroll bar

Excel Christmas Tree - icons

 

 

Get weekly Excel tips from Debra

 

 

30 Excel Functions in 30 Days

 

 

Excel Tools add-in

 

Last updated: November 28, 2018 2:05 PM