Home > Format > Conditional > Christmas Tree Excel Christmas Trees With Scroll BarSee how to use an Excel scroll bar on a worksheet, to decorate a Christmas tree, with no macros. A simpler example changes light colour when you tap theF9 key, and another example uses a spin button to add lights, tinsel and gifts to an Excel Chrismas tree. |
Decorate Excel Christmas TreesIt's fun to decorate Christmas Trees in Excel, and in the sections below, there are 3 different ways to do that. This short video shows a Scroll Bar Christmas tree, a Spin Button tree, and a few other Christmas-themed Excel files, from the Excel for the Holidays page. 1) Simple Christmas TreeFirst, this simple tree uses conditional formatting icons to show its lights. When you open the workbook, press the F9 key on your keyboard, to change the light colours. 2) Christmas Tree With Scroll BarThis Christmas tree is fancier, with a scroll bar that adds lights, tinsel, and gifts, as you move the scroll bar marker to the right.. 3) Christmas Tree with Spin ButtonThis tree is similar to the Scroll Bar version, with a few new features, like the Santa picture, shown below. It has a Spin button, instead of a Scroll bar, and a check box to change the light colours Christmas Tree InstructionsIn the sections below, you'll find the steps for creating each of the trees: -- 1) Simple Christmas Tree -- 2) Excel scroll bar Christmas tree -- 3) Excel Spin Button tree Christmas Tree DownloadsIf you don't have time to build the Christmas trees from scratch, you can go to the download section, and get all 3 of the completed Christmas Tree workbooks. |
1) Simple Christmas TreeThis was the first Excel Christmas tree that I made, and its lights change colour when you press the F9 key. The lights are "traffic light" icons, in red, yellow and green. Make Tree ShapeHere are the basic steps that I followed, to create the Christmas tree:
Add Tree LightsNext, here are the steps that I followed to create the Christmas tree lights, using a formula, and conditional formatting:
Add Christmas Light IconsNext, here are the steps I followed to add conditional formatting icons to the Christmas tree, as twinkling lights.
Make the Lights TwinkleAfter the tree is set up, and the conditional formatting icons are in place, you can make the lights "twinkle"
The icon colours will change, based on each cell's formula result:
Keep tapping the F9 key, to continue changing the icon colours, and to make the Christmas tree lights "twinkle" |
2) Scroll Bar Christmas TreeIn this example, you'll see how to use an Excel scroll bar on a worksheet, to decorate a Christmas tree, without using macros. It has features that you can adapt to other workbooks, such as:
Video: Scroll Bar Christmas TreeTo see the Christmas Tree file in action, watch this short video. The written instructions are below the video. Scroll Bar Tree StepsHere are the main steps in creating the scroll bar Christmas tree, and the details are in the sections below. |
Create Named RangesTo make it easier to select specific parts of the picture, you can create named ranges. In the sample Christmas tree workbook several named ranges were created, including one for each tree light colour, each gift, and the star. After you create the named ranges, you can quickly go to a range by selecting it in the Name Box drop down list. After you select a range, you'll be able to format it, or enter numbers in all the cells. |
Add Scroll BarNext, you'll add a scroll bar to the worksheet, to control the messages and hidden items. Tip: You can use an Excel worksheet scroll bar to automatically adjust a number in a worksheet cell - up or down. An Excel scroll bar is easy to use, and can be helpful in a dashboard or data entry sheet.
|
3) Spin Button Christmas TreeIn this example, you'll see how to use an Excel spin button on a worksheet, to decorate a Christmas tree, without using macros. The Excel Christmas TreeHere is a screen shot of the spin button Christmas Tree workbook, fully decorated. This version uses the conditional formatting icons from the Simple Christmas Tree, and gifts and sequential tree trimming from the Scroll Bar Christmas tree. Linked PictureSomething new in this version is a picture of Santa that appears at the end of the sequence. He's delivering the gifts while everyone is asleep! This is a linked picture, that refers to the SantaPic named range.
Here is the formula that controls the picture's display:
|
Conditional Formatting IconsOn the tree, the lights, tinsel and star at the top all appear based on conditional formatting settings. In a merged cell at the top, a Star icon appears if that cell’s formula returns a 1.
And here is the formula in the merged cell:
Then, using customized icon sets, a value of 1 or greater shows a star, and other values show no cell icon. Lights and TinselThe conditional formatting for the tree lights and tinsel strings works the same way. A lookup table sets the values for each row number, with higher numbers appearing later in the sequence. =IF(VLOOKUP(ROW(),Lists!$F$2:$G$17,2,TRUE)>$G$3,0,(RAND()*100)+1) There are 3 possible tree ornaments/lights, and the RAND function is used to mix them up.
Here is the formula:
The tinsel strings use the Zero Filled Boxes icon. |
Start the Tree TrimmingThere is a spin button on the worksheet, and you can click that to move up or down in the tree trimming sequence. The spin button is linked to cell G3
Tree Trimming MessagesJust like the Scroll Bar version, this tree has messages that appear, based on the Spin Button value. You can change the messages, or add new ones, in the table in the workbook. |
Download Sample Files
|
Related Tutorials |
Last updated: November 16, 2022 3:41 PM