Sparkling Christmas Tree in Excel

Add to Favorites
Author: | Edits: don

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:

b957da8ef13c288e3abfde6b8e95bc08.png

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:
    2b515b0c6610a94bc5cac7fd82b1585a.jpg
  3. On the Home tab go to Format and select Column Width...
    531ef1fcb59b15db08c711e4a885371f.jpg
  4. Enter 2.70 and hit OK.
    3dd8cad0b352c27812da2986b34a7d61.png
  5. The columns will now be much smaller than before.  Now, select rows 1 to 12:
    d4f85731609f11d6f219206df8cf11dc.jpg
  6. On the Home tab go to Format and select Row Height...
    0370e96292493f510fd891ae55fcceff.jpg
  7. Enter 33.70 and hit OK.
    5cfc27c524e1501734ef8e856bb12129.png
  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),"")
    

    9c4f0f289c3526ec8621570e721f2fa1.jpg
  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.
    fb1c9a72607894b10e0c2652f2d9dafa.jpg
    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.
    6716766e60dfbbc720fb5c219e940514.jpg
    Click the quick-fill-handle again and drag it down until row 12 to copy the formula down.
    dba3f88f5a448c0654362ec558d04226.jpg
  11. You should now have a weird tree with lots of numbers:
    3546037749cf685dff1c16f28853c811.jpg
  12. Now we add color and effects. Select cell C1 to Y12:
    f1828b0c21774e73175b5ccbfbecd591.jpg
  13. Go to the Home tab and click Conditional Formatting and then select New Rule...
    0e68fc3d3fa91f1f4a7efe2d0bb76df4.jpg
  14. Select the option Use a formula to determine which cells to format
    3bc524e3d834393b1548d5b00cc172e4.png
  15. Enter this formula:
     
     =ISNUMBER(C1)
    

    40681ceb80e4a7c9847d455a39b09412.png
  16. Click the Format... button.
    7f15840be955596d0233cfca054045fd.png
  17. Go to the Fill tab and then click the More Colors button.
    8c87d29d05fa199df5bb5d52ef94b9d4.png
  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.
    afb0b0647440bfc7e0e7928bb57cfcea.png
  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:
    5b92d1f10d354880cf560bfb84740988.png
  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.
    7c61799515cfc3bbb856bc05f57cbd07.png
  21. We are almost there! Excel should now look like this! :D
    a0ccbfb182e5c53b10abb7bee9867a59.jpg
  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.
    de280d0e95b1e47101e4d6bf71ac66de.jpg
  23. TADAAAAA! (hold down the F9 key to see it sparkle)
    ece54dc8de2c7416212cb6391b09f858.jpg
  24. If you want you can also add a stump by selecting some cells under the tree and making their background color brown.
    0690e1712755a82ddd722636245d55c5.jpg

Final Result:

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

51f6f32f1b03c1b087ac89f85729e620.png

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


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
VBA IF Statement in Excel Macros
Tutorial: IF statements allow you to make decisions in your Macros and VBA in Excel. An IF statement...
Nest IF Statements in Excel
Tutorial: How to nest IF statements inside of each other in Excel so that you can make more complex...
Years, Months, Days Between two dates in Excel
Tutorial: I have found a rather convoluted method to Calculate the Number of Years, Months and Days ...
Turn Off the Formatting Window Popup in Excel
Tutorial: How to stop the annoying formatting window, the "Mini Toolbar" from appearing when you se...
Format a Date to Show Only the Day of Week in Excel
Tutorial: How to format a Date so that only the day of the week is visible from that date. Result: ...
Manually Calculate Formulas and Functions in Excel
Tutorial: How to force Excel to recalculate all formulas and functions without editing or entering ...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course