Sparkling Christmas Tree in Excel

How to make a wonderful sparkling Christmas tree in Excel.

This method uses only formulas; there is no VBA or Macro required for this to work.

I'll teach you how to build your own or, if you just want to skip to the good stuff, download the Excel file that accompanies this tutorial and enjoy! :)

Here is what we will build:

Steps to Build a Sparkling Christmas Tree in Excel

1. Go to a blank worksheet so we can start fresh.
2. Select Columns C through Y:
   
3. On the Home tab go to Format and select Column Width...
   
   
4. Enter 2.70 and hit OK.
   
5. The columns will now be much smaller than before.  Now, select rows 1 to 12:
   
6. On the Home tab go to Format and select Row Height...
   
   
7. Enter 33.70 and hit OK.
   
8. Now, we are ready to input the tree.
   In cell C1, input this formula and then hit Enter:
   

    =IF(ABS(COLUMN($N$1)-COLUMN())<=ROW()-1,RANDBETWEEN(1,10),"")
   

   
   
9. You shouldn't see anything appear after you enter the formula.  Now, select cell C1, click the quick-fill-handle in the bottom right corner of the cell and drag the cell to the right up to column Y.
   
   Once you let go of the mouse, the formula will have been copied all the way over.
10. Now, range C1 to Y1 should be highlighted.
    
    Click the quick-fill-handle again and drag it down until row 12 to copy the formula down.
    
11. You should now have a weird tree with lots of numbers:
    
12. Now we add color and effects. Select cell C1 to Y12:
    
13. Go to the Home tab and click Conditional Formatting and then select New Rule...
    
    
14. Select the option Use a formula to determine which cells to format
    
    
15. Enter this formula:
    

     =ISNUMBER(C1)
    

    
    
16. Click the Format... button.
    
17. Go to the Fill tab and then click the More Colors button.
    
18. Select the color here that you want for the tree.  I suggest staying on the Standard tab and selecting a dark green color.  When you've found the color, click it and then hit the OK button.
    
19. Go to the Font tab and then to the Color drop down menu and select the same green color that you selected for the Fill color.  The last one that you used will be listed under Recent Colors:
    
20. Once you have selected the correct color for the Font and Fill, hit OK and then hit OK again to get out of the previous smaller window where we entered the ISNUMBER function.
    You will then see a screen like below; hit OK.
    
21. We are almost there! Excel should now look like this! :D
    
22. Keep cell C1 to Y12 selected and then go back to the Home tab and click the Conditional Formatting button and then hover over Icon Sets and click the Traffic Lights option.
    
23. TADAAAAA! (hold down the F9 key to see it sparkle)
    
24. If you want you can also add a stump by selecting some cells under the tree and making their background color brown.
    

Final Result:

(I've also removed gridlines here: View tab and un-check the Gridlines option located in the Show section/box)

Make it Sparkle

To make it sparkle you just have to hold down the F9 key.

Notes

Yes, there are a lot of steps here, but I wanted to make it as easy as possible for you to follow along and create your own tree.  It should not take you more than a couple minutes to make this tree with these directions and it will bring a lot of joy and fun to the office! :)

The formula that I made for you here is pretty awesome; if you want to make the tree bigger or smaller, just copy it down or up to more or less rows.  The only cell reference in the formula $N$1 is a reference to a cell that will be in the center column for the tree; you can change this as you need in order to fit a tree into your worksheet.

Make sure to download the Excel file that accompanies this tutorial so you can see the tree in action!

Merry Christmas!!! :D