There are times when we have a huge set of data in an Excel sheet. With this set of huge data, we need to have a slide bar in order to easily navigate through the work. With good sliders, you will not have a reason to be typing entries manually in order to go to the cell you want. This is because you can be utilizing the scrollbar to get to the values that you want. In this post, we shall learn how to create scrollbars.
Easy steps to create slider bars
In order to easily create a scroll bar, let us look at the following guide of how to insert scrollbars.
Step 1: Prepare your data sheet
Note that having a huge data set with horizontal scroll bar missing can actually present a hectic situation where it becomes very difficult to view that data. At the same time, when you have the bottom scroll bar missing, you will have some difficulties in accessing that data at the bottom of your sheet.
Consider the data in the figure below, where we have data from 35 countries in one spreadsheet. Notice that out of all the thirty five countries, only 19 are visible in the current window. This means that we must create a scroll bar to access all the other contents.
Figure 1: Data to use for Scrollbar
With the above data, let us insert slide bars that will enable us only see 10 states at any given time.
Step 2: Activate Developer Tab
Once we have put together our data set in a way that we can easily manage it, the next thing is to activate the developer tab if it is not yet activated already. Activating the developer tab is not any tedious work. All you have to do is right-click on any of the tabs, and select the Customize the Ribbon option.
Figure 2: Activating developer tab
In the dialog box that appears, check the Developer option on the right of the window under Main Tabs pane.
Figure 3: Check developer tab
This will enable you have Developer as one of the tabs.
Step 3: Insert Scrollbar
Now that we have enabled the Developer tab, we need to use it to insert missing scrollbars. Doing this is quite simple. We need to go to the Developer tab and click on the Insert. Select Spin Button, (Scroll bar) under the Form Control section.
Figure 4: Click scrollbar from ribbon
The next thing is to click on the Scroll bar. After this, click on any cell of your spreadsheet and you will be able to see a scroll bar inserted.
Figure 5: Example of vertical scrollbar
Customizing the slider
Having a slider that is not well customized might still make your work hard to navigate through. For this reason, after inserting the missing scrollbar in your worksheet, you have to customize it so that it can perfectly fit the needs of your worksheet. To customize sliders, make the following changes;
- Put current value as 1
- Indicate the minimum value as 1
- Let the maximum value be 19
- Put incremental change as 1
- Have the cell link as $L$3
Figure 6: Customize the scrollbar
The next thing you need to do is to resize the created scroll bar. Ensure that it occupies at least 10 rows on the spreadsheet as shown in the figure below;
Figure 7: Scrollbar created in Excel
Enter the OFFSET formula
Now that you have created a slide bar, the next thing you need to do is to put the OFFSET formula in the data. The OFFSET formula is as below for purpose of this tutorial;
=OFFSET (C3,$L$3,0)
After putting the formula, copy it down to fill the other cells of the 10 rows of column H in our example.
Do the same for the other columns in the 10 rows selected.
And with this, you have created a good scrollbar for your worksheet.
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment