Excel Christmas Tree With Scroll Bar

See how to use an Excel scroll bar on a worksheet, to decorate a Christmas tree, with no macros

Introduction to the Excel Scroll Bar

You can use an Excel worksheet scroll bar to automatically adjust a number in a worksheet cell. For example, users could slide the bar to see how revenue is affected by rising gas prices. An Excel scroll bar is easy to use, and can be very helpful in a dashboard or data entry sheet.

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
• A 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

Create a Christmas Tree Picture

To create a grid patterned picture on an Excel worksheet:

1. Start by sizing the columns and rows, to create small square cells. In this example, the column width is 0.9 and the row height is 8.25
2. Then, colour the cells (use matching fill colour and font colour), to create a completed picture. In the screen shot below, I've created a Christmas tree, with a star on top, lights and tinsel, and gifts beneath the tree.

 

Watch the Christmas Tree Video

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

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 the Scroll Bar

Next, you'll add a scroll bar to the worksheet, to control the messages and hidden items.

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

Get a Sample File

1. Christmas 2010 version: To see how the Excel Scroll Bar Christmas Tree file works, you can download the Excel Scroll Bar sample file. It is in xlsx format, zipped, and does NOT contain any macros.
2. Christmas 2017 version: This 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 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