Simulate Christmas tree lights in Excel

This is the final result...

I will show you how to develop this in just 4 steps.

Step 1 - Paint your tree

Make columns narrow, add background colours then add a star using the character map and the font Wingdings.

Paste into the cell above the tree, it looks like this «.

Then change the font, size and colour of the "star cell" so it looks like a star.

Step 2 - Add formulas

Enter this formula in all tree cells:


This generates a random number between 1 and 3 every time a calculation is performed.
We will use these numbers to set the colours of the lights.

Step 3 - Format

Next add conditional formatting to all tree cells as follows.

Then "Manage Rules"...

If you also want to animate the star, add 3 conditional formats to the star cell as follows.
Select the star cell, then Conditional Formatting, New Rule…

Select "Use a formula…" and set the font colour based upon the value in a tree cell (say P8).

Repeat for the other two possible values with different font colours.

You can remove gridlines if you want a "cleaner" look for your sheet.

Step 4 - Animate

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

You can download the file with instructions under downloads.