How to Use Scrollbars and Spinners in Excel 2007

by Helen Bradley

Helen Bradley illustrates how to add handy form elements to your Excel worksheet.

When you're creating an application in Microsoft Excel often times you will want to give your user a way to enter values in a cell that does not involve them having to type them. Spinners and scrollbars are two valuable features in Excel 2007 that you can use to turn data entry into something that can be done with a click of the mouse. In this article, we'll show you how to create a spreadsheet solution using spinners and scrollbars for data entry.

» Finding the Tools

Spinners and scrollbars are Form tools and they're accessible from the Developer toolbar which is hidden, by default, in Excel 2007. To display it, click the Office button and choose the Excel Options > Show Developer tab in the Ribbon, and click OK.

» Create the Worksheet

So that you have a worksheet to work with, create a worksheet that looks like the one in the figure. To do this, enter these values into these cells:

Cell: Value:
C3 Principal
C4 Interest Rate
C5 Payments per Year
C6 Number of Years
C7 Period Payment
D3 200,000
D4 5%
D5 12
D6 25
D7 =ABS(PMT(D4/D5,D6*D5,D3))

This calculator helps you determine the period payment on a mortgage given the principal borrowed, the interest rate, the number of payments per year, and the number of years that the mortgage is amortized over.

» Configure the Spinner

If you want to share this workbook, your users will have to click in the cells in column D to enter their data. It would be better if we made it easier for them to use.

To do this, click in cell E6, select the Developer tab, click Insert and click Spin Button (Form Control) tool — you must use form controls rather than ActiveX controls, as these are something very different.

Drag the Spinner box into cell E6 so it can be used to adjust the number of years. To do this, right click the spinner control and choose Format Control > Control tab. Set the Current Value to 25, the Minimum Value to 5, the Maximum Value to 40, and the Incremental Change to 1.

Click in the Cell Link area, type D6 and click OK. This links the spinner control output to cell D6 so that when you click either arrow, the value the spinner returns is placed in D6.

The spinner has been set so it returns a value in the range 5 to 40 and the incremental change value controls the increment when you click either arrow — in our case the value increases or decreases by one.

This is a simple example as there is a direct relationship between the Spinner output and the range of values needed to be inserted into cell D6.

» A More Complex Example

Add a second spinner to cell E3, right-click the Spinner, choose Format Control > Control tab. We have a problem with this spinner's settings in that the principal for a typical mortgage is far larger than the allowable spinner values, which are between 0 and 30,000.

The solution is to place the value that the spinner returns in a second cell and to use this value in a calculation so we can create the desired range in cell D3. If we want mortgage principal values between 50,000 and 500,000 we can set the spinner's minimum value to 500, the maximum value to 5,000 and multiply these values by 100 to get the desired range.

In addition, we'll set the incremental change to 10 so that ultimately, when we multiply that by 100 we'll have the mortgage principal incrementing by 1,000 for every click of the spinner. Set the Cell Link to F3 and click OK.

To finish this spinner, click in cell D3 and enter this formula:


When you adjust the spinner, the value in cell F3 adjusts and, as a result, the principal value in cell D3 recalculates with each change.

Typically when you use spinners and scrollbars you'll need to make adjustments like this to ensure that the value that you get from the spinner or scrollbar can be used on your worksheet.

» Using Scrollbars

Now let's use a scrollbar to change the interest rate value. Click the Scrollbar Form Tool and drag a horizontal scrollbar in cell E4. Right click it and choose Format Control. The scrollbar has all the same settings as the Spinner control and a Page Change value.

The Page Change value is the amount that the value changes when you click either side of the marker in the scrollbar. Typically, clicking here moves you a larger value than when you click the scrollbar arrows.

To get a usable interest rate value we will need to place the scrollbar value in another cell and use the value it returns in a calculation to get interest rates in the range 0.5% to 10% (which is the numerical range of 0.005 to 0.1) and an increment of 0.05%. So configure the Scrollbar with these properties: Minimum Value 50, Maximum Value 1,000, Incremental Change 5, Page Change 10, and set the Cell Link to cell F4.

Into cell D4 type this formula:


This will return a value between 0.5% and 10% with an increment of 0.05% and a page change of 0.1%. When you test this, also test the scrollbar by dragging the middle slider and you will encounter a small problem in that the interest rates don't always appear as multiples of 0.05%. You can solve the problem by replacing the formula in cell D4 with this formula:


This formula rounds the value returned by the scrollbar upwards to the nearest multiple of 5 before converting it into the desired interest rate value.

» Tidying Up

When you are done creating your scrollbars and spinners, hide column F by selecting the column letter, right clicking, and choosing Hide. Now the interim values won't confuse your user.

Next time you are developing an application in Excel give consideration to whether the Spinners and Scrollbars feature in Excel 2007 will provide your users with a smarter way to enter data than by typing it.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. She blogs at http://www.projectwoman.com/blogger.html.

Adapted from winplanet.com.

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!

This article was originally published on Thursday Apr 9th 2009
Mobile Site | Full Site