A scroll bar in Excel is a useful tool for managing large datasets without taking up too much screen space. It’s especially handy when creating an Excel dashboard where you need to display a lot of data in a confined area. In this step-by-step guide, I’ll walk you through the process of adding a scroll bar in Excel. You’ll also learn how to link it to a dataset, so when a user adjusts the scroll bar, the data displayed updates accordingly.
CREATING A SCROLL BAR IN EXCEL
For this tutorial, I’ll be using data for 28 states in India, including each state’s area and population (census 2001).
The goal is to display only 10 states at a time in the dataset. As the user moves the scroll bar, the displayed data will automatically update, showing different states based on the scroll bar’s position. Here’s an example of what this setup will look like:
Click here to download the example file
Steps to Create a Scroll Bar in Excel
- The first step is to get your data in place. For the purpose of this post, I have used census 2001 data of 28 Indian States with its Area and Population.
- Go to Developer Tab –> Insert –> Scroll Bar (Form Control).
If the Developer tab is missing from the ribbon, it’s because it’s hidden by default in Excel. You’ll need to enable it first to make it visible.
3. Click on Scroll Bar (Form Control) button and click anywhere on your worksheet. This will insert a Scroll Bar in the excel worksheet.
4. Right-click on the Scroll Bar and click on ‘Format Control’. This will open a Format Control dialog box.
5. In the Format Control dialogue box go to the ‘Control’ tab, and make the following changes:
- Current Value: 1
- Minimum Value: 1
- Maximum Value: 19 (it is 19 here as we display 10 rows at a time. So when the user makes the scroll bar value 19, it displays rows 19-28)
- Incremental Change: 1
- Page Change: 5
- Cell Link: $L$3
The cell $L$3 is linked to the scroll bar in Excel, and its value ranges from 1 to 19. This is the key cell that will control the scrollable list. Don’t worry if this doesn’t quite make sense yet—just keep reading, and it will all come together!
6. Resize the Scroll Bar so that it fits the length of the 10 columns (this is just to give it a good look, as shown in the pic below).
7. Now enter the following formula in the first cell (H4) and then copy it to fill all the other cells:
=OFFSET(C3,$L$3,0)
Note that this OFFSET formula is dependent on cell L3, which is linked to the scroll bar.
Now you are all set with a Scroll Bar in Excel.
How does this work?
The OFFSET formula uses cell C3 as the reference point and shifts it based on the value in L3. Since L3 is linked to the scroll bar, when the scroll bar is set to 1, the formula will reference the first state name. When the scroll bar is set to 2, it will reference the second state, and so on.
Also, since C3 is not locked, in the second row, the formula will adjust to =OFFSET(C4,$L$3,0) and function in the same way.
