Use of Spin Buttons in Excel

Question: How can I use a spin button in Excel to display data?

A spin button enables the user to increase or decrease a number in a cell. You will usually find examples of spin buttons on order forms or internet shopping baskets allowing the user to increase or decrease the number of products they want to purchase.


The example I use is a numbered list of names, each of whom has a percentage score assigned to them and a Yes or No.

- When you have created a similar table to the above, the first action is to give each section a name that can be used later in formula's. To do this, highlight cells A2 to B11 and in the name box enter the word 'list' (as shown below):

- Repeat this action by ...

• Highlight A2:C11 and in the name box enter 'percent'
• Highlight A2:D11 and in the name box enter 'yesno'

- If you do not have the Control Toolbox displayed on your worksheet, right click on the blue area (next to the Help Button) and select 'Control Toolbox'.

 The control toolbox:







- Select the Spin Button and draw the button onto the Excel worksheet (in cells F2 and F3). Then right click and select Properties.

- As above, you will need to change the following Spin Button properties:

• Linked Cell = G2 (this is the cell that will change when the spin button is pressed)
• Max = 10 (this is the maximum number the spin button will count up to)
• Min = 1 (this is the minimum number the spin button will count down to)
• Value = 1 (this sets the start value at 1)

- Finally, we need to link the number generated by the Spin Button to the example list using the =LOOKUP function. This is made easier for us because we named sections of the table at the beginning of the exercise.

The formula's are as follows:

• Cell H2 ... =LOOKUP(G2,list)
• Cell H3 ... =LOOKUP(G2,percent)
• Cell H4 ... =LOOKUP(G2,yesno)

- You should now be able to click on your Spin Button to change the number in cell G2, and display the data from the data table.