Hi, this is Wayne again with a topic “Adding a Scroll Bar in Excel”.
In this Excel tutorial, I want to show you how to add a form control that gives you a scroll bar to help you adjust numbers in Excel. If you’re not familiar with form controls, you really should look into them and if there’s a lot of interest in this particular video, I will make another video that covers more form controls. So let me know if you’re interested in that, as you can see, this is a simple inventory for a store that sells CDs in the wonderful genre of synth pop, and this is just hypothetical. But you can see here at the left, some of the bands whose music this store carries, including the latest album by Muse if you’re familiar with the band Muse simulation theory is a really good, synth pop CD.
So this just tracks the bands, the albums, the genres and some of the other things like price of the CD and the quantity of that CD. That’S on hand in the store. Now what happens if the quantity changes? Let’S say that a bunch of CDs are sold.
I need to change the quantity. Maybe the store goes out and purchases some more CDs again. This number needs to change and, of course, you can just click and type in the new number on top of it.
But let’s look at how to add a form control that can help you do that a little bit faster in some cases now to add a form control. You need to make sure you have the Developer tab here on the ribbon and I don’t see it there. So how do I get that onto the ribbon? Basically, all you have to do is right-click on one of the tabs and choose customize the ribbon, and that should give you a pop-up with a bunch of different options that you can add onto the ribbon and there is the Developer tab.
So I check that box. Click. Ok.
Now I should have a Developer tab here on the ribbon. It took a second to appear, but there it is now that it’s there. I can just click on it and look for the controls group.
This is where you’ll find all sorts of interesting and exciting controls that you can add to your spreadsheets. I’M gon na go here to insert and I’ll click, and here are the form controls and the one I’m looking for is scroll bar, and here it is so I’ll click there once you’ve clicked on that little button Excel now gives you the opportunity to basically draw Your scrollbar, so I’m gon na, put my mouse pointer wherever I want that scrollbar to be located so right here and I’ll left-click and drag to draw out the scrollbar I’ll, just put it right there and it appears now. This scrollbar is basically floating on top of the data, so don’t worry if it overlaps your data, it’s not going to ruin your data at all and it won’t even affect your data until you set it up properly, and I want this scrollbar to affect this number Of the quantity of this CD, so now that it’s there, I can just right click on the scrollbar and I need to go down and format the control remember. This is considered a form control, so I want to format the control and the most important option here. In my opinion, is this cell link I’m gon na scoot this over to the left? I need to go down here to cell link and click and put in the cell that it’s linked to, if you prefer, you can just click this button here and then select the cell that you want it connected to, and then I can just go back up Here, click this button to get back into the options. Another important option here that you need to be aware of is this: the minimum value and also the maximum value, so I’ll, just put the minimum value as being zero.
The maximum value right now it’s set to be a hundred, I’m gon na go up to maybe 200. Let’S say that this CD store doesn’t want to carry huge inventories, let’s just limit it to 200, and then here you can set the incremental change so every time I click on this arrow. This number here will go up by one unless you want it to go up by two or by five or whatever, so I’m gon na change it back to one okay, the rest of this I’m gon na leave, as is, and then I’ll just click. Ok. Now, let’s try it out so here’s my scrollbar and you can see, there’s a gray vertical line and I can click and hold the mouse click and drag that scrollbar to very quickly increase the quantity on hand all the way up to 200. If I want to – but let’s say it’s 175, I can just click and drag to get it to 175.
You can also use these buttons. I can click this right button to go up a couple of numbers, and so you have two ways to adjust the numbers gradually, one by one or using the scroll bar. You can change the number much more dramatically so now. Of course, I could insert another scroll bar for each of these CD titles, perhaps a faster way to do. That, though, would be to simply right click on the scroll bar and notice that that also gives you the option to resize the scroll bar and also to move it. So you need to right-click on it to get those options back, but in this case I just want to copy it and then paste it. So I’m just gon na do ctrl V to paste. So now I have a copy of it. But remember I connected this scroll bar to this particular cell cell f3, so this new copy of it is also going to affect cell f3, and so I need to right-click go down to format control until this second scroll bar that it’s supposed to affect f4. So now, let’s test it and to really test these properly, you really should click on another cell before you try to use them so now that I’ve clicked away, I can go back and let’s use the scroll bar and look.
It is affecting the correct cell now. So I’m just going to copy paste these all the way down onto as many of these cells as I want – and I know for sure, I’m gon na need a bunch more of these Mews and cosma City CDs. So I’m gon na jump right down there to add scroll bars for them so that I can increase their quantities. But again I need to make sure I right click, format, control and link it to the right cell. Just like that, thanks for watching, if you’re interested in learning about more of the form controls, just let me know in the description below, but in the meantime I hope you found this tutorial to be helpful.
If you did, please click the like button below and consider connecting with me on my social media accounts like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe. Please click the bell next to the subscribe button. If you do that, you’ll be notified whenever I post another video and watch for another video from me at least every Monday, if you’d like to support my you Tube channel, consider becoming a patron of mine through my patreon account and you’ll find links to that along With information about these bands in the description below .