Using Form Controls in Excel: The Spinner

Form Controls can provide users with more interaction with the spreadsheet data, leading to a clearer understanding of What would happen if... scenarios. The Forms Toolbar (choose View>Toolbars>Forms) contains all the tools relevant to adding and manipulating controls on an Excel worksheet. This includes Text Labels, Group Boxes, Buttons, Checkboxes and Option buttons, Spinners, Scrollers and List Boxes. To understand how they operate, we are going to use a Spinner to change the values in a cell.

Open a spreadsheet and in cell A1 enter a heading - Using Spinner Controls (or similar). In cell B4, enter a number of your choice. Now display the Forms Toolbar (choose View>Toolbars>Forms) and select the Spinner control . Drag out the Spinner so that it is just to the left of the number in cell B4 (you can resize it at any time by clicking on the spinner with the right mouse button and dragging the handles). To specify how the Spinner will control a cell you need to display the Spinner's properties: right-click on the Spinner and Select Format Control... In the dialogue box which appears, Select the Control tab and set each of the properties to the following values:

Note that the minimum value cannot be less than 0, therefore it is not possible to directly set the linked cell's contents to a negative value. To test the Spinner, close the Forms toolbar and check that clicking the up and down arrows actually changes the value in cell B4. The Slider Control performs a similar function to the Spinner, but is normally used to enter (Control) a large range of values. Now you can use the values created by the Spinner to make a simple function machine. In cell B5 enter =B4*2.5+1 (i.e. multiply the value in cell B4 by 2.5 then add 1). As you use the Spinner, the values in both B4 and B5 will change.

How can you get the Spinner to create negative values?
If you haven't worked this out yet, here is the solution. In the Spinner properties (shown above in red), make the Cell Link a cell which is out of sight, e.g. Z1. Now change cell B4 to the following: =Z1-20 (i.e. subtract 20 from the contents of cell Z1). As you use the Spinner, cell B4 will display a range of values from -20 to +20.

Spinner Task 1
Set up a spreadsheet to help to solve the following problem: My friend is having a problem with the Atkins Diet! He misread one page that told him to eat 125 bacon sandwiches over a period of 5 days. After the first day he was to eat 7 more sandwiches than the previous day. How many sandwiches did he eat on the first day?

Spinner Task 2
Set up a spreadsheet to help to solve the following problem: The library in Taibach has a large room with enough tables and chairs to seat 236 people. Some of the tables are in the shape of a hexagon and seat 6 people each. The rest of the tables are octogonal and seat eight people each. If there are 35 tables in the room, how many are hexagons and how many are octagons?

Download or open an example Excel Workbook showing the Controls in use.