Home > Format > Conditional > Christmas Tree

Excel Christmas Trees With Scroll Bar

See 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 Trees

It'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 Tree

First, 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 Bar

This 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 Button

This 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 Instructions

In 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 Downloads

If 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 Tree

This 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 Shape

Here are the basic steps that I followed, to create the Christmas tree:

1. First, adjust the worksheet column witdths and row heightss, to create small square cells.
• In this example, the column width is 0.9 and the row height is 8.25
2. Next, colour some of the worksheet cells green, to create a Christmas tree shape - I kept it very simple, like something that I would have drawn in kindergarten!
3. Then, at the bottom of the tree, colour a few cells brown, for the tree trunk
4. Next, below the trunk, colour a few cells red, to make a tree stand
5. Finally, at the top, use yellow fill colour to create a 5-pointed star

Add Tree Lights

Next, here are the steps that I followed to create the Christmas tree lights, using a formula, and conditional formatting:

1. First, select cells throughout the tree, where you want the Christmas lights to appear
   • Select the first "light" cell, then press the Ctrl key, and select as many light cells as you want
   • In my tree, I selected 30 "light" cells
2. Then, with the cells selected, type this formula in the formula bar: =RAND()*100
3. Next, press Ctrl + Enter, to add the formula to all of the selected cells
4. Keep all the "light" cells selected, so you're ready for the next step

Add Christmas Light Icons

Next, here are the steps I followed to add conditional formatting icons to the Christmas tree, as twinkling lights.

1. With the "light" cells still selected, go to the Excel Ribbon, and click the Home tab
2. In the Styles group, click on Conditional Formatting
3. In the drop-down menu, point to Icon Sets
4. In the pop-up list of Icon Set options, click on one of icon sets in the Shapes section.
   • I chose the first option - 3 Traffic Lights (Unrimmed)

Make the Lights Twinkle

After the tree is set up, and the conditional formatting icons are in place, you can make the lights "twinkle"

• Select any cell on the worksheet
• Then, tap the F9 key on your keyboard, to force the worksheet to calculate its formulas
• The results for each RAND formula will change

The icon colours will change, based on each cell's formula result:

• low numbers will have a green icon
• medium numbers will have a yellow icon
• high numbers will have a red icon

Keep tapping the F9 key, to continue changing the icon colours, and to make the Christmas tree lights "twinkle"

2) Scroll Bar Christmas Tree

In 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:

• Scroll bar lets users change a number quickly and easily
• Text box that displays a changing message based on VLOOKUP formula
• conditional formatting shows features (lights, gifts) when target number is reached
• named ranges make it easy to work with specific cells

Video: Scroll Bar Christmas Tree

To see the Christmas Tree file in action, watch this short video. The written instructions are below the video.

Scroll Bar Tree Steps

Here are the main steps in creating the scroll bar Christmas tree, and the details are in the sections below.

Make a Christmas Tree

Here are the basic steps that I followed, to create the Christmas tree:

1. First, adjust the worksheet column witdths and row heightss, to create small square cells.
• In this example, the column width is 0.9 and the row height is 8.25
2. Next, colour some of the worksheet cells green, to create a Christmas tree shape - I kept it very simple - a jagged triangle shape
3. Then, at the bottom of the tree, colour a few cells brown, for the tree trunk
4. Next, below the trunk, colour a few cells red, to make a tree stand

Add Star and Lights

After the basic tree is designed, add some decorations

1. After that, at the top, use yellow fill colour to create a 5-pointed star
2. Next, select cells for the strings of tinsel, and colour them a light grey
3. Next, select several cells, to use as lights, and colour them yellow
4. Then, select another group of cells, to use as lights, and colour them red
5. Repeat that step, to create blue light cells.

Add Gifts

After the decorations are on the tree, create a few simple gift shapes below the tree.

I created small coloured squares and rectangles, and used a contrasting colour for a ribbon and bow.

In the screen shot below, I've created a Christmas tree, with a star on top, lights and tinsel, and gifts beneath the tree.

Create Named Ranges

To 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.

Number the Hidden Items

The completed picture includes several items that will be hidden when the workbook is opened. For example, the star is not on top of the tree, and the gifts are not beneath the tree.

These items are revealed in the order that you set, by typing numbers in the worksheet cells. For the Christmas tree, the reveal order is:

1. tree lights (from bottom to top)
2. strings of tinsel (bottom to top)
3. star
4. gifts (from 1 to 4)

Each hidden object will be revealed when the scroll bar reaches the number typed in its worksheet cell.

To add the sequence numbers:

1. Type a number in each tree light cell, starting with 1 in cell F32, and ending with 30 in cell P12. The font is black in the screen shot below, so you can see the typed numbers near the top of the tree.

   

2. Type the same number in each tinsel string, so the entire string appears at the same time. (Tip: With the entire string selected, type a number, then press Ctrl + Enter)
   Leave a gap between the string numbers, so there's a bit of time between their appearance. The string numbers in this example are 32, 35, 38, 42 and 45.

   

3. Type the same number in each cell for the Star, so the entire star appears at the same time. The star number in this example is 48, so it appears slightly after the last tinsel string.
4. Type the same number in each cell for each gift, so each gift appears separately. The gift number in this example are 50, 53, 56 and 58.

   

Create the Message List

A text box on the worksheet will display a message, based on the current number selected in the scroll bar. On a different worksheet, create a lookup table for these message.

1. In the first column, type a number, to indicate when the message should appear. The first number should be zero, and that message will be visible when the workbook opens, and the scroll bar hasn't been adjusted.
2. In the second column, type a short message for key points in the process.
3. Name the list (not including the headings) as MsgList.

   

Add Scroll Bar

Next, 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.

1. On the Excel Ribbon, click the Developer Tab. If the tab is not visible, see these instructions to Add the Developer Tab
2. In the Controls group, click Insert
3. Under Form Controls, click the Scroll Bar tool

   

4. On the worksheet, drag a rectangle shape, to create the horizontal scroll bar.

   

5. Right-click on the scroll bar selected, and click Format Control
6. Change the Maximum Value to 60 (based on the highest number used in the hidden object cells)
7. For the Cell Link, enter $G$3. This cell will be hidden behind the scroll bar, where users won't accidentally delete it.
8. Click OK, to close the Format Control window.

   

Create VLOOKUP Formula

Next, you'll create a VLOOKUP formula to find the message that matches the scroll bar setting.

1. Select cell C3 (move the scroll bar, if it's covering cell C3.)
2. Type the following formula: =VLOOKUP(G3,MsgList,2,TRUE)
   Note: The fourth argument is set to TRUE, so the result will be an approximate match.

   

3. Format cells C3 and G3 with white font

Add the Text Box

Next, a text box is added to the worksheet, to display the message from VLookup formula.

1. Move the scroll bar, if it is covering cell C3.
2. On the Excel Ribbon, click the Insert tab.
3. In the Text group, click Text Box
4. On the worksheet, drag a rectangle shape at the top right of the picture, to create a text box.
5. Click on the border of the text box, to select the shape -- after clicking, you should not see the cursor flashing inside the text box.

   

6. Click in the Formula Bar and type an equal sign.
7. Click on cell C3, to create a reference to that cell, then press the Enter key

   

8. Format the text box with no border, no fill, and a large, bold font.

The text box will display the message for the number currently selected in the scroll bar (probably zero). Click the scroll bar buttons, up or down, to see the message change. If you drag the scroll bar box, the message won't change until you release the slider.

Add Conditional Formatting

The final step is to add conditional formatting to the hidden objects, so they appear when the scroll bar reaches a specific number.

• The star and the gifts will be formatted as white fill and font, until the scroll bar reaches their number.
• The tree lights and tinsel strings will be formatted as green fill and font, until the scroll bar reaches their number.

For example, to add conditional formatting to the gifts:

1. Select the range of cells that contain the gifts (C42:AE51)
2. On the Excel Ribbon, click the Home tab
3. In the Styles group, click Conditional Formatting, and then click New Rule
4. Under Select a Rule Type, click Use a formula to determine which cells to format
5. In the Formula box, enter a formula to compare the active cell to the scroll bar's linked cell: =$G$3 < C42
6. Click Format, and format the cells with White fill, White font and no border.
7. Click OK

Create similar conditional formatting for the star, after selecting cells O3:S6. Use the formula: =$G$3 < O3

Create similar conditional formatting for the tree, after selecting cells B7:AF40. Set the conditional formatting to green font and green fill. Use the formula: =$G$3 < B7

Test the Scroll Bar

After everything is set up, you can test the scroll bar.

1. Move the scroll bar, so it covers cells C3 and G3.
2. Click the up or down button on the scroll bar, to see the decorations and gifts appear.
3. Click the down button, to hide the decorations and gifts.

The tree will go from plain to decorated, and back to plain.

3) Spin Button Christmas Tree

In 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 Tree

Here 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 Picture

Something 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.

• That range is an empty cell (J1), until the Spin Button reaches a setting of 59.
• After that, it shows the picture from cells J4:L12.

Here is the formula that controls the picture's display:

• =IF(Tree!$G$3>=59,Lists!$J$4:$L$12,Lists!$J$1)

Conditional Formatting Icons

On 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.

• Note: I don’t usually use merged cells, but made an exception for Christmas.

And here is the formula in the merged cell:

• =IF($G$3>=VLOOKUP(ROW(),RowLU,3,TRUE),1,0)

Then, using customized icon sets, a value of 1 or greater shows a star, and other values show no cell icon.

Lights and Tinsel

The 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.

• Note: I add 1 to the result, to ensure that the result is never zero.

Here is the formula:

• =IF(VLOOKUP(ROW(), Lists!$F$2:$G$17, 2,TRUE) > $G$3,0, (RAND()*100)+1)

The tinsel strings use the Zero Filled Boxes icon.

Start the Tree Trimming

There 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

• You can clear that cell if you want to start over.
• And, if you’re impatient, type 60 in that cell, to see the completed tree.

Tree Trimming Messages

Just 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

1. Simple Christmas Tree: You can download the zipped Simple Excel Christmas tree file, and press the F9 key to change the light colours. The file is in xlsx format, zipped, and does NOT contain any macros.
2. Christmas Scroll Bar Tree: To see how the Excel Scroll Bar Christmas Tree file (2010 version) works, you can download the Excel Scroll Bar sample file. It is in xlsx format, zipped, and does NOT contain any macros.
3. Christmas Spin Button Tree: This 2015 version of the Christmas tree is similar to the 2010 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. Click here to download the 2015/2017 Christmas Tree sample file. It is in xlsx format, zipped, and does NOT contain any macros.

Related Tutorials

Excel Advent Calendars

Excel for the Holidays

Excel Christmas Planner

Excel Christmas Tree - icons

Named Ranges

VLOOKUP

Conditional Formatting

Developer Tab