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
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
To create a grid patterned picture on an Excel worksheet:
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
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.
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:
tree lights (from bottom to top)
strings of tinsel (bottom to top)
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:
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.
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.
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.
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.
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
In the second column, type a short message for key points in the
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.
On the Excel Ribbon, click the Developer Tab. If the tab is not
visible, see these instructions to Add
the Developer Tab
In the Controls group, click Insert
Under Form Controls, click the Scroll Bar tool
On the worksheet, drag a rectangle shape, to create the horizontal
Right-click on the scroll bar selected, and click Format Control
Change the Maximum Value to 60 (based on the highest number used
in the hidden object cells)
For the Cell Link, enter $G$3. This cell will be hidden behind the
scroll bar, where users won't accidentally delete it.
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.
Select cell C3 (move the scroll bar, if it's covering cell C3.)
Type the following formula: =VLOOKUP(G3,MsgList,2,TRUE)
Note: The fourth argument is set to TRUE, so the result will be an
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.
Move the scroll bar, if it is covering cell C3.
On the Excel Ribbon, click the Insert tab.
In the Text group, click Text Box
On the worksheet, drag a rectangle shape at the top right of the
picture, to create a text box.
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.
Click in the Formula Bar and type an equal sign.
Click on cell C3, to create a reference to that cell, then press
the Enter key
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:
Select the range of cells that contain the gifts (C42:AE51)
On the Excel Ribbon, click the Home tab
In the Styles group, click Conditional Formatting, and then click
Under Select a Rule Type, click Use a formula to determine which
cells to format
In the Formula box, enter a formula to compare the active cell to
the scroll bar's linked cell: =$G$3 < C42
Click Format, and format the cells with White fill, White font and
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.
Move the scroll bar, so it covers cells C3 and G3.
Click the up or down button on the scroll bar, to see the decorations
and gifts appear.
Click the down button, to hide the decorations and gifts.
The tree will go from plain to decorated, and back to plain.
Christmas 2015 version: This tree is similar to the 2010
version, with a few new features, like the Santa picture, shown below.
Click here to download the 2015 Christmas Tree sample file. It is in
xlsx format, zipped, and contains no macros.
Watch the Christmas Tree Video
To see the Christmas 2010 version of the file in action, you watch
this short video.