If you want people to be able to adjust values interactively you can add a scroll bar. This can adjust the values smoothly as you move a slider up and down. As an example, we will create a spreadsheet to show the monthly repayment on a loan and use a scroll bar to adjust the interest rate. Our original spreadsheet contains the cells shown below.
The PMT formula is used to calculate the monthly repayment, it is constructed as follows:
rate,number of payments,principal borrowed,final balance,payment
If the interest figure in cell A2 is adjusted the corresponding payment will be shown. We are going to add a slider bar to adjust the interest rate.
To add a scroll bar,
click on the View drop-down menu and choose Toolbars.
Select the Control
and then click on the scrollbar icon
When you have positioned your scrollbar, click your right mouse button on the bar and choose Properties from the drop-down menu that appears.
The values marked in the diagram
are the only ones that
you should need to change. Enter a reference for any currently blank cell by the
LinkedCell box to set the cell to alter. The Maximum, Minimum, LargeChange and SmallChange values
must all be whole numbers, use a formula in your spreadsheet to
further adjust these values if you want the changes to be small
(e.g. divide by 100 if you want to change by 0.01). The SmallChange value is the amount by which the value
is adjusted when you drag the slider along the scroll bar.
The LargeChange value
is the jump that will be made if you click anywhere in the scroll
bar to move the slider instead of dragging it. When you have set the values, click the cross at the top
right hand corner of the properties window to close it.
You do not need to set
the other values in this window. Finally, to make your scroll bar active, click on the
design mode icon
As you slide the slider on the scroll bar the value in cell A3 changes from 1 to 200. We have not allowed a minimum value of 0 for this slider as we are going to divide the value in cell A3 and this would not be possible with a value of 0.
Finally, we can hide row 3 so that we cannot see the cell holding our adjustment value and can format cell A2 as a percentage to make it more readable.
The image below shows an example of the output from our finished spreadsheet:
To make changes to the scroll bar you will need to return to the View menu and click Toolbars to display the Control Toolbox. You will then need to click the Design icon to return to design mode and it will then be possible to select the scroll bar and move it or change its properties again. Don?t forget to exit design mode and close the toolbox when you have finished.
Click here to open an Excel workbook containing a slider bar.
Click here to return to the Excel index.