Helen Bradley explains how to add interactive elements to an Excel worksheet using scroll bars and spin buttons.
Whenever your user has a discrete number of choices to make for inputting data into an Excel worksheet you can save time by automating how they enter this data. You can do this in a number of ways and one of them is to use a spin button or a scroll bar to do the work. Today, I’ll introduce you to using spin buttons and scroll bars and show you an way to add an interactive element to an Excel chart with a spin button.
- Get your Developer tab
- Make a Spin Button more useful
- How Scroll Bars work
- Interactive chart with a Spin Button
Get your Developer tab
In Excel 2007 and Excel 2010, the spin buttons and scroll bars are accessible from the Developer tab. If you don’t have this visible, in Excel 2010, choose File > Options > Customize Ribbon and in the second panel make sure that the Developer checkbox is selected. In Excel 2007 click the Office button, choose Excel Options and then enable the Show Developer tab in the Ribbon checkbox in the Popular group.
To see the tools, choose Developer > Insert and then select either the Spin Button or the Scroll Bar tool from the Form Controls group. It’s vital that you select those in the Form Controls group not the ActiveX Controls as they work very differently and it’s the Form Controls that we are using.
Drag a Spin Button onto your worksheet and drag a Scroll Bar onto your worksheet too. Notice that you can drag your scrollbars so they are oriented either vertically or horizontally. To move or resize a control, right click it to select it and then size or move as required.
To see how these work, right click the Spin Button and choose Format Control. In the Control tab are your options for configuring the Spin Button. Essentially what the Spin Button does is to place a value in a cell for you. You click the arrows to increase or decrease the value in the cell.
The limits for the Spin Button are that the value is to be a whole number between 0 and 30,000 and the Incremental Change can be any whole number between 1 and 30,000.
For now, set the Current Value to 50, Minimum Value to 0, Maximum Value to 300 and the Incremental Change to 10. Click in the Cell Link box, click on cell A1 and close the dialog.
Click away from the Spin Button to deselect it and now click the arrows on the Spin Button. As you do, you’ll see the value in cell A1 change. You can increase the value to 300 but not any higher and decrease it to 0 but not any lower. Notice that the value changes in steps of 10.
Make a Spin Button more useful
You can use a Spin Button like this to make it easy for a user to enter a value into a worksheet by clicking a button rather than typing a value. However, you might be wondering what you can do when the values they are to enter are not whole numbers between 0 and 30,000?
The answer is to use an intermediate cell to make the calculations for you. So, for example, if you want a user to enter a value between 0 percent and 5 percent in steps of 0.1% you’ll need to scale the value the Spin Button gives you so you get a range of values from 0 to .05 in steps of .001.
There are any number of ways to do the mathematics for this and it doesn’t really matter how you do it as long as your solution works. Here is one way to get your Spin Button to do this: right click it and set the Minimum Value to 0, the Maximum Value to 500 and the Incremental Change to 10. Again, set the cell link to cell A1. Now, in cell A2 type the formula =A1/10000, and format A2 using a percentage format and one decimal place.
Now as you click on the Spin Button you’ll see that the value in cell A2 gives you what you want – a percentage value between 0 and 5% in increments of 0.1%. The value in cell A1 is being created by the Spin Button but it is the value in cell A2 that you are most interested in. Whenever you need to produce a value more complex than an integer between 0 and 30000 use a solution like this to scale the value you get from the Spin Button to get your desired value.
How Scroll Bars work
A Scroll Bar works in a similar way to the Spin Button except that with a Scroll Bar you can set a Page Change value which is the change in value when you click on the Scroll Bar either side of its moving marker. The Incremental Change value is applied when you click on the arrows at either end of the Scroll Bar. With a Scroll Bar you must, of course, set a Cell Link for the value to be placed in. If you want to scale the value, you’ll need to use a second cell with a formula in it that will scale the value that the Scroll Bar gives you appropriately for the end result that you need.
For example, consider an organization that makes loans of anything from $20,000 to $5,000,000 in multiples of $10,000. You can use a scrollbar for entering the loan amount. In the example I’ve set the cell link cell to E2 and the formula in C2 is =E2*10000 – this is the cell that shows the desired loan amount.
The Scroll Bar’s properties are: Minimum Value of 2, a Maximum Value of 500, Incremental Change of 1 and a Page Change of 10. The Incremental Change has to be set to 1 to allow the user to fine-tune the amount to any multiple of $10,000. This is essential because if you’re going to build a solution that is usable the user has to be able to easily get to the result they want using it. If you were to set the Incremental Change to 5, for example the user would only be able to increment the actual loan amount in multiples of 50,000 which is too big – they have to be able to increment the loan in multiples of $10,000 (because that’s what the organization lends), so the Incremental Change has to be set to 1 to accommodate this.
The Page Change value lets the user change the loan amount in steps of $100,000 which will help the user get closer to the loan amount that they want to use. The middle slider is not controlled by any setting so a user can get instantly from $20,000 to $5,000,000 by simply dragging the marker from one end of the scroll bar to the other.
Interactive chart with a Spin Button
To see how this can all be put together, here is a worksheet with a series of sales values for dates from the 1-Jun-2011 to 28-Sep-2011. These dates, when converted to numbers, are in the range of 40,695 to 40,814 (dates being represented in Excel by the number of days since 1-Jan-1900).
In cell C2 is this formula: =IF($G$1=A2,B2,NA()) which is been copied down column C. The cells from C2:C19 have been formatted using a conditional format that hides any errors – because the formula results in a series of #N/A errors. We could have avoided the errors appearing in column C with this IF function =IF($G$1=A2, B2,”") but if we do this, then the chart will plot a series of zero values which is not what we want so creating errors with our formula is actually the desirable outcome.
To hide the errors, select the cells in column C and choose Conditional Formatting > New Rule, choose Format Only Cells That Contain and from the first dropdown list, select Errors and then set the format so it is white text on a white background – an effective way to hide errors!
The formula in cell G1 is =40000+G3 and cell G3 will be the Cell Link for the Spin Button.
These are the properties for the Spin Button: Current Value 695, Minimum Value 695, Maximum Value 814, Incremental Change 7 and Cell Link G3. Test the Spin Button – as you click it you should see a value appear in column C that corresponds with the date in cell G1. The Minimum Value is the number when added to 40000 gives the date 1-Jun-2011 and the Maximum Value + 40000 gives you the date 28-Sep-2011. We used an Incremental Change of 7 so the dates we see in cell G1 are spaced one week apart to match the dates in column A. As you click the arrows on the Spin Button, the contents of G3 change to be one of the dates from column A.
Add the chart
The chart is created using the range A1:C19 and it is created as a column chart. The chart is sized to cover contents of column C but still display row 1 of the worksheet.
To format the chart I right clicked the single bar that is visible for Series 2 and selected Change Series Chart Type and chose Line Chart With Markers. Right click the marker, choose Format Data Series and set this to an attractive marker style. Right click the marker again and choose Add Data Labels and then click the Legend to select it and delete it.
Now, when you click on the Spin Button the marker moves up and down the chart to highlight the sales for the date that appears in cell G1 and the label shows the unit of sales next to the marker.
There are innumerable ways to incorporate Spin Buttons and Scroll Bars into your worksheets. You can use them for data entry or, as I’ve shown here, to make charts more interactive.