How to Insert a Scroll Bar 

by | Mar 14, 2023 | Excel

Today, we are going to learn how to insert scroll bars in Excel and where we can use them.

How to Insert Scroll Bar 

In our first exam­ple, we have a PMT func­tion. The scroll bars help us change our val­ue for the PMT func­tion more rapid­ly (Fig. 1)

PMT function
Fig. 1 PMT function

Let’s insert a scroll bar from the film Excel tips 43, when I was chang­ing the time with a scroll bar. Let’s start with the Devel­op­er tab. It’s turned off by default, so we have turn it on. Just right click on the rib­bon and choose the Cus­tomize the Rib­bon option (Fig. 2)

Customize the Ribbon option
Fig. 2 Cus­tomize the Rib­bon option

In the win­dow that appeared we have to find the Devel­op­er tab, select the prop­er check­box and press OK (Fig.3)

Developer checkbox
Fig. 3 Devel­op­er checkbox

Now, we can go to the Insert option and select the Scroll Bars in the Form Con­trols area. We aren’t inter­est­ed in ActiveX Con­trols because they are con­nect­ed with VBA, and we need some­thing sim­ple. Now, we can draw our scroll bar. When we are hold­ing the Alt key, Excel will fit our scroll bar to cell bor­ders. We don’t always want to do it, but we have such an option. Even after select­ing our scroll bar area, when we click the Alt key, Excel will still fit it to cell bor­ders (Fig. 5)

Cell borders
Fig. 5 Cell borders

In our case it isn’t so impor­tant. Now, we have our ver­ti­cal bar (Fig. 6)

Vertical bar
Fig. 6 Ver­ti­cal bar

And hor­i­zon­tal bars (Fig. 7)

Horizontal bars
Fig. 7 Hor­i­zon­tal bars

Let’s look at prop­er­ties of the scroll bar. We have to right click to select the scroll bar and right click again to go to the For­mat Con­trol option (Fig. 8).

Going to scroll bar properties
Fig. 8 Going to scroll bar properties

In the win­dow that appeared, we have some options, such as For­mat Con­trol which is now 26, Min­i­mum val­ue which is 0, Max­i­mum val­ue which is 100. We have to remem­ber that scroll bars can have only inte­ger num­bers from 0 to 30.000. In our exam­ple, I want to work with time and I want the scroll bar to have a 1‑minute pre­ci­sion. It means that I have to set the max­i­mum val­ue to 1440, as this is the num­ber of min­utes in one day. The next option, which is Incre­men­tal change, is con­nect­ed to the arrows at the ends of our scroll bars. Then, we have Page change which is con­nect­ed to click­ing on our scroll bar. How­ev­er, the most impor­tant option here is the Cell link bar. We can con­nect Cur­rent val­ue to Cell link. Let’s assume that I want to con­nect our scroll bar to cell E1. I’m click­ing on the cell and press­ing OK (Fig. 9)

Connecting Current value to a cell
Fig. 9 Con­nect­ing Cur­rent val­ue to a cell

Now, the val­ue in cell E1 changed into 26 because that’s the cur­rent val­ue in our scroll bar (Fig. 10)

The same value
Fig. 10 The same value

When I move the scroll bar, the val­ue in cell E1 is also chang­ing in accor­dance to the val­ue on the bar. When we click on the arrows on our scroll bar, the time is chang­ing minute by minute down­wards or upwards. When we click on the scroll bar, but not on the arrows, we move by 10 min­utes upwards or down­wards, depend­ing on which part we are click­ing. We can move even more rapid­ly by catch­ing the slid­er and mov­ing it.

Our val­ue from the scroll bar is an inte­ger. Some­time, though, we need time or per­cent­ages. The ques­tion is, how can we changed inte­gers into time or per­cent­ages? In our exam­ple, we have to just divide the val­ue by 1440 and we will get the time in min­utes (Fig. 11)

=E1/1440

Changing integers into time
Fig. Chang­ing inte­gers into time

https://www.youtube.com/watch?v=_LCqaHTnNHs