Excel’s spin button lets you quickly increase or decrease a value in a cell without having to type it manually. It can be used in wide-ranging contexts, such as a data entry form, a dashboard worksheet, or a price calculation table, and its flexible controls mean you may tailor it to your needs.
Enable the Developer Tab
The spin button option is accessed via Excel’s Developer tab, which isn’t on the ribbon by default.
Toadd the Developer tab, first, right-click any of the tabs youdohave, and click “Customize The Ribbon.”
Then, in the Excel Options window, select “Main Tabs” in the right-hand drop-down menu, check “Developer,” and click “OK.”
The Developer tab will sit on your ribbon next to the existing tabs.
Prepare Your Data
Next, prepare your data, including a blank column where your spin buttons will go. Remember, the aim of a spin button is to increase or decrease a value in a cell through a single click, so bear this in mind when you think about where your spin buttons will go. In my case, they’ll go in column E, ready for me to link to the data in column D in the next step.
Your tables don’t need to be formatted for spin buttons to work, but it’s good practice touse Excel’s formatted tablesanyway.
Column F contains the formula:
So, when I use the spin button to increase or decrease the values in the Sales column, the value in the Total column will react accordingly.
Add the Spin Buttons
Now, it’s time to add the spin buttons. In the Developer tab on the ribbon, click “Insert,” and click the “Spin Button” icon, which is the icon with an arrow pointing upwards and an arrow pointing downwards.
Then, use your mouse to click and draw the spin button’s position and size. When you release your mouse, the spin button will appear in that location.
Now, to add the spin button to the remaining cells in that column, select the cell containing the current spin button, and click and dragthe AutoFill handledownwards.
Now that the spin buttons are in place, you’re ready to adjust their controls so that they link to the cells in column D.
To select an entire spin button (for example, if you want to move or resize it), first left-click it, and then right-click it.
Set Up the Spin Button Parameters
Since, unfortunately, the cell references in the spin button’s Format Control dialog box do not apply relatively when you copy and paste the spin button to other cells, you need to adjust the parameters for each separately. The only other way around this sometimes laborious process is to write a VBA macro.
Right-click the first spin button you added, and click “Format Control.”
The first thing to do in the Format Control dialog box is to link the spin button to the relevant cell. To do this, click the empty “Cell Link” field box, and then select the relevant spreadsheet cell. In my case, I want the spin button in cell E2 to link to the value in D2. Then, complete the remaining details in the dialog box, including—importantly—changing the “Current Value” field to match the value in the corresponding cell.
Failing to adjust the “Current Value” field will change the corresponding cell’s value to 0, meaning you may lose your data.
Here’s where the tool’s flexibility can be seen. In my case, I want the values to increase or decrease by one each time I click the spin button’s up or down arrows, but you can change that increment to another number if that suits you. You can also set the minimum and maximum values according to the scope of your data.
After you click “OK,” click away from the spin button you just set up, and then click the up and down arrows to see them affect the corresponding cell’s value.
Repeat the process for the remaining spin buttons in your table. If youadd more rows to the bottom of your table, select a cell containing a spin button, press Ctrl+C, select the new cells that require a spin button, and press Ctrl+V. Then, follow the processes above to link each button to the corresponding value.
The spin button is just one of themany form controls you can use in Excel. For example, you can add a drop-down control, a radio button, a scroll bar, and many other controls. The plain button can also be used tocreate Excel macros that automate repetitive tasks.
