How to Create an Animated Christmas Tree in Excel 🎄
Christmas Day is here, so what better way to celebrate the big day than creating something Chrimbo-themed (in Excel, of course). I am going to show you how to create a pixel art Christmas tree with flashing lights that switch on at the click of a button.
This won’t be the most graphically advanced piece of artwork you’ll make, but it’s a good bit of fun. For you newbies, it’s also a useful introduction to understanding how certain functions can be used, as well as conditional formatting and VBA.
After you’ve finished, you might want to send it to that spreadsheet nut in the office, or friends and family.
I’ve included a finished copy of the workbook at the end of this article, but I will run through the steps from the beginning.
Let’s dive straight into it.
1.
Open a new Excel workbook.
2.
To set the size and shape of the tree, hold down Ctrl whilst selecting cells to form a pyramid. Start from cell P15 and ensure each of the 12 layers is two rows deep. Finish at row 38.
3.
At this point, don’t click away otherwise you’ll lose your cell range selection.
Save it by going to Formulas (tab) > Define Name (button).
Enter ChristmasTree as the name and click OK.
Now, whenever you want to reference the Christmas tree, you can just use the name instead of going through the tedious process of selecting the cells each time.
4.
With the ChristmasTree range selected, type =RAND() and it will slot into cell E37. Then press Ctrl + Return to populate the entire range.
The RAND function returns a random number that is greater than or equal to 0, but less than 1.
5.
Click on the Conditional Formatting dropdown, hover over Icon Sets and choose the 3 Traffic Lights (Unrimmed) option.
6.
In the same menu, go to More Rules.
7.
Tick the Show Icon Only checkbox to hide the values.
Click OK.
8.
At this stage, you will have a pyramid with random static ‘lights’.
To get this looking more like a tree, carry out the following formatting tasks:
- Press Center to centrally align the icons.
- Set the Fill Color to Green.
- Change the width of columns E:AA to 20 pixels.
9.
With the ChristmasTree range active, go to Home (tab) > Conditional Formatting (dropdown) > New Rule (button).
Select the Use a formula to determine which cells to format option and type =$A$1=0 into the text field. Then press Format and set the Font and Fill colours to Green.
This ensures that when cell A1 is 0 or blank, the icons do not appear. (It will make more sense once the VBA has been implemented.)
Click OK.
10.
Go back inside the Conditional Formatting dropdown and press Manage Rules….
You’ll see the two rules created. Ensure the order matches what’s shown in the image, and then enable Stop If True for the first.
Click Apply to see the changes applied on the worksheet—the tree lights should become invisible.
Press OK to exit the window.
11.
The tree needs a trunk!
In the range, O39:Q42, set the Fill Color to Orange, Accent 2, Darker 50%.
12.
Some presents are also needed to sit under the tree!
In the Fill Color menu, click on More Colors… and then go into the Custom tab. Use the HEX codes shown in the image to colour the cells.
Cell ranges of presents from left to right:
Present 1—E40:G42
Present 2—I40:M42
Present 3—S40:Y42
13.
Let’s complete the tree by adding the c̶h̶e̶r̶r̶y star on top. ⭐️
Go to the Insert tab to find the Shapes dropdown. Here, you’ll find a 5-point star you can click to draw onto the worksheet.
In the Shape Format contextual tab, set the Shape Fill to Gold, Accent 4 and the Shape Outline to No Outline.
14.
With the Christmas tree design complete, there are just a couple of other things to add: a Merry Christmas sign and a Santa Claus. The latter will be the button that users click to turn the tree lights on.
Download the Merry Christmas sign:
https://www.stickpng.com/img/holidays/christmas/merry-christmas-sign
Download the Santa Claus image:
https://www.stickpng.com/img/holidays/christmas/merry-christmas-sign
Go to Insert (tab) > Picture (dropdown) > This Device (button) and select both images.
Position them as shown in the image.
Add a speech bubble as well to let people know Santa is a button!
Go to Insert (tab) > Shapes (dropdown) > Speech Bubble: Oval (button) and draw it next to him.
Include the text: “Click me to turn the Christmas tree lights on!”
Set the speech bubble’s formatting to the following:
Shape Fill: Red
Shape Outline: No Outline
Font: Old English Text MT
Font Size: 24
Alignment: Center
The design is now complete, and the next few steps will focus on creating a functional switch.
15.
Open the Visual Basic Editor by pressing Alt + F11, or by going to the Developer tab and clicking on the Visual Basic button.
16.
Go to Insert > Module.
17.
Copy and paste the following code into the window:
' Christmas tree on switch
Sub SwitchOn()
' Define variable
Dim i As Integer
' Set number of iterations to run
For i = 1 To 2021
' Recalculate Christmas Tree worksheet
Sheets("Sheet1").Calculate
' Set value of A1 to 1
Range("A1").Value = 1
' Start next iteration until 2021 have been completed
Next i
' Set A1 value to 0 after loop has finished
Range("A1").Value = 0
End Sub
A For loop is used to cycle through two lines of code 2021 times (see what I did? 😉).
The first one recalculates the Sheet1 worksheet, which forces the RAND formula to produce a new number. The second sets the value of cell A1 to 1, meaning the conditional formatting will only display in this case.
After the loop has finished, the value of A1 changes to 0, which switches off the conditional formatting.
More information on VBA loops is found on Excel Easy.
18.
Close the VBA window to return to the worksheet.
Right-click on Santa and select Assign Macro.
Choose SwitchOn from the list and click OK.
19.
Hide the Boolean value in cell A1 so it’s out of view.
Go to the Format Cells window and click Custom in the side panel. Then enter ;;; in the text field to create a new custom format.
Click OK.
20.
Finally, switch off worksheet gridlines by going to the View tab and unticking the Gridlines checkbox.
21.
Everything is now complete!
The tree will start with the lights out. Just click on Santa to switch them on for several seconds.
Download the Completed File
Final Words
There you have it. You’ve just learnt how to build a flashing Christmas tree in Excel. Feel free to be creative and adapt it for your own needs.
Merry Christmas! 🎅🏻 🎄
