Hi, this is Wayne again with a topic “Using Spin Buttons in Excel”.
In this article, I want to show you another of the Excel form controls in a previous video. I showed you how to insert checkboxes into your spreadsheets using this Developer tab in the controls group clicking, insert and check box, and you should definitely watch that check boxes. Video, but in this article, we’re gon na look at this form control here called spin button in some versions of Excel. It’S also called a spinner, so in this article we will look at what it is, what it does and how to set it up step. One is to make sure that you have the Developer tab enabled in your version of Excel and the way you do that is by going here to file and going down to options.
I can click and one of the options that appears is to customize the ribbon. When I click on that, it gives me a list of popular commands that I can add to the ribbon, but over here on the right there are main tabs and developer might not be checked. You might have that unselected and I highly recommend that you do check the box to get the Developer tab to appear for you on the ribbon. So I’ll click.
Ok, there’s my Developer tab. Once you have the Developer tab, you can go here to the controls. Group click insert and choose spin button when you click on spin button notice that your mouse pointer changes to a plus sign. You can then click and drag to draw out a spin button.
I want the button to more or less fit in the cell, so maybe about that size, and then I can click on it to drag it. Where I want it to be. If I need to adjust the size, I can use the handles in the corners to click and drag to make the button bigger or smaller, but I’m pretty happy with the size of that button. If you want a little more control as you size the button, it is possible to hold the control key while clicking and dragging and see what it does. It makes the button grow equally above/below, left and right, and so that can be a nice option to have as you’re resizing your spin buttons. Now that I’ve got the spin button, if I want to click on one of the buttons notice that nothing is in order for these buttons to become clickable, I have to first click away from the spin button and now clicking back on it notice that I can Click on the top button or the bottom button.
However, now that I’ve done that, look, I can’t click and drag to move the spin button. If you want to get back into moving mode, just right-click on the spin button, and now you can left-click on it to drag it so right-clicking on it will re-enable this moving mode. Okay. So let’s look at how this spin button can help me with this spreadsheet. I have here the beginnings of a yearly salary, estimator, just a very simple spreadsheet, with some simple formulas to help someone estimate the salary that they might earn in the coming year and, of course they could click here to type in a number. Let’S say 500 a month I tap enter and I’ve set up a simple formula here in this cell cell b4. If I double click there, you can see the formula and all it does. Is it multiplies b2 by the number 12 for 12 months, and then it adds that to the contents of b3, let’s say: there’s additional extra monthly earnings, maybe from yard sales or from doing extra work on the side or whatever it might be, and that’s multiplied by 12 and then I put both of those in parentheses, the parentheses are not necessary. I just included them to look a little more understandable, I’ll tap enter on the keyboard. You can see if I put in let’s say 10 extra dollars a month. It updates the total estimated earnings, so this is great, but what, if I don’t want the person using the spreadsheet to have to type at all, it would be nice if all they had to do was click on a button and the button would adjust the numbers For them, the way you can set that up is by right clicking on the spin button, form control and then just go down to format control. When you do that, you get a pop-up with some important options.
There are several tabs worth of options, but the one that we’re going to look at exclusively in this article is the control tab. That’S the important one! First off it wants to know what the current value is. Well, I would like the current value of this cell to be 0, so I’ll just put zero in there. The minimum value is zero. I don’t want them to be able to make negative salary now. What is the maximum value for this cell? Well, we could say something like maybe twenty five thousand could be the maximum value. Next, what about the incremental change? Every time someone clicks this up arrow. How much do I want the number to go up from from zero to? What do I want it to go from zero to a hundred zero to a thousand to zero to ten thousand? What’S the incremental change? Well, I would like to change it to be a hundred, so it’ll go up by a hundred at a time now. The final setting that I need to adjust here is the cell link and the easiest way to do this is just to click this button, and what I’m doing here is I’m linking this form control spin button to a cell which cell well. This is the one.
So I click on it and then to get back into the options. All I have to do is click this button here and it takes me back in and it has identified the exact cell that is linked to this form control. Now I can click, OK and let’s try it out just like before. In order for me to click to make this work, I have to first click away from it. Those little handles disappear from off the form control, and now I can click the up arrow and notice that the monthly salary goes up by 100.
Click again goes up by 200. If I click down, it goes down by 100, now watch what happens if I click and hold the button. It just goes up very rapidly. Now I could set up another form: control for the estimated additional monthly earnings and if I don’t want to go through the same process of inserting and resizing, and all of that, I could simply right-click on the existing form, control copy it and right click paste and Then right, click on the new form, control and go down to format, control I’ll need to change the cell that it’s linked to I’ll change it to this. One here click this button here and I need to think about any possible adjustments to these settings.
For example, when it comes to additional monthly earnings, it may not be in the hundreds of dollars. It may be more like tens of dollars, so I’m changing that incremental change, and I should also change the current value down to zero click. Ok and after I click away from the spin button now I can click back on it to add $ 10 a month $ 20 a month 30. I can also subtract just like you would expect and it calculates it out.
So everything seems to be working great, but there is a hidden problem that you need to be aware of. Look what happens if I click and hold on the monthly salary spin box and that monthly salary is going up and up looking pretty good. But look what happens when it hits 25,000. It stops now, if only we all have this problem of making too much money for the spreadsheet. But the spin button here has come up against a limit. You may or may not have noticed this, but I set a limit on the high end for this number.
If I right click on the spin button and go down to format control, you can see what the limit is maximum value 25,000. Now, what if I set that to be 30,000 click? Ok, great! If I click away and then back on the button, it should go up to 30,000 and we’re good. But what, if I want to go even higher than that? How about 40,000 look, what happens if I try to make 40,000 the maximum value Excel says the scroll value must be between 0 and 30,000 now, even if you don’t foresee that being a problem in this particular case, there are cases when the number that you’ll need Will be much higher than 30,000, so how do you handle that? Well, this is a trick that you can do. You can right click on the format control the spin button in this case and I’m going to set the current value back to 0, and this time I’m gon na say instead of 100 being the incremental change, I’m gon na say 1 is the incremental change.
Now the trick here is to think of this one, not as being the number 1 but as being 1,000. So again this represents 1,000 and then the other part of the trick is. I need to make this linked not directly to this cell, but to another cell. Maybe d2 so I’ll click.
This button link it to d2 click. This button to go back and click; OK, okay! So now, when I click away from the button and then back on it clicking on the top half, it increases by one at a time decreases by one at a time if I click below. So if the number three represents a thousand or I could have made it a hundred either way, all I have to do now is click here on b2 and create a formula I’ll type, the equal sign, so this cell is equal to d3. Whatever is in d3 multiplied by 1000 and tap enter so now that I’ve done that, I can very easily go above 30,000, see how that works. But this is a simple trick for how to make it so that a spin button can make a number go above. 30,000, it looks like there’s an error code here, but all I have to do is double click between B and C, and it stretches out so that the full number can fit in the space provided. If I don’t want this number here to be confusing, I can right click on the D and choose hide, and now I have a wonderful form, control that I can use to quickly estimate my yearly salary and it’s just too bad that this isn’t true.
Thanks for watching, I hope you found this tutorial to be helpful. If you did please like follow and subscribe and when you subscribe click the bell next to the subscribe button, so that you’ll be notified whenever I post another video, and if you want to support my channel, you can do so through my patreon account. Look in the description below for a link; .