Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Scrollbar How to create a scrollbar in Excel – Excelchat

How to create a scrollbar in Excel – Excelchat

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc