EXCEL

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:

PMT(interest rate,number of payments,principal borrowed,final balance,payment due)

The payment due figure is 1 if the payment is due at the beginning of the month and 0 if it is due at the end of the month.

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 Toolbox and then click on the scrollbar icon . Your cursor will change to a black cross and you can now drag out the scrollbar on your spreadsheet.

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 above 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 on the Control Toolbox to exit design mode. You can now click the cross in the corner of the Control Toolbox to close this as well.

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.

webmaster@allaboutoffice.co.uk