Christmas candles

It's Christmas time again, the season of cheer. You can bring a little festive merriness to your PC by simulating Christmas advent candles using the magic of random numbers combined with conditional formatting.

Why not have a go yourself? I will show you how to develop two versions in just a few steps each. Alternatively you can download the finished version here: Christmas candles v3.

Basic version

Explanation: All four candles are always on.

Step 1 - Create your candles

Make columns narrow, then add background colours for the base, the candles and the wicks. Then add a message at the bottom plus festive icons and shapes (using the Insert ribbon), if you wish.

Step 2 - Add formulas

Enter these formulas in cells that should show flames:

=RANDBETWEEN(50, 100) for lighter flame colours
=RANDBETWEEN(1, 50) for darker flame colours in the centre, above the wick

These formulas generate random numbers every time a calculation is performed.  We will use these numbers to set the colours of the flames.

Step 3 – Add conditional formats

Select a block of cells containing the “flame cells” and add conditional formatting as follows.

Step 4 – Tidy up

For a cleaner look…

  • Select then hide unused rows and columns (right click, hide)
  • Remove the gridlines (View ribbon, Show section, deselect Gridlines box)
  • Protect sheet (Review ribbon) and deselect all options to hide the cell selection

Step 5 - Animate

Press F9 to change the colours.  Or press and hold F9 to animate.


Advent Sunday version

Explanation: Each candle only lights up when the related advent Sunday is reached.

Step 1 – Create or copy the basic version

If necessary, unprotect the sheet (Review ribbon) and select and unhide all columns.

Step 2 – Add dates

  • Today – enter formula =TODAY()
  • Advent Sundays - input the four dates

Step 3 – Amend flame formulas

The formulas must now test if the relevant advent Sunday date has been reached. So each flame tests a different advent date, for example:

=IF($AR$10>=$AR$11; RANDBETWEEN(50;100); 0) for lighter flame colours in the first flame

If the relevant date has not been reached, the cell value is now set to zero.

Step 4 – Amend conditional formats

Add a second conditional format (tip: duplicate the first rule and amend it).

If the cell value is zero, format the colour to match the background so that no flame is visible.

Tidy up and animate

Same as the basic version.


Burning down candles version

In the the download file you will also find another version in which the candles gradually burn down!

I created this based upon (a copy of) the advent Sunday version and used the OFFSET function to simulate the burning-down based upon the number of days each candle has been burning and an input of how many days each row of candle picture lasts (e.g. 4 days), i.e. the "standard candle" offset by a number of rows.