Excel Tip: Pump up the volume on your data entry screens with a simple slider control

Sometimes you have a spreadsheet that you want to look good and feel interactive, but might only have a few inputs.

A good example of this might be a loan illustration. You might want to experiment with different loan amounts, and terms for example.

You can of course just have an input cell for each of the variables (loan amount and term for example). But if you want to really jazz up the spreadsheet, what about interactive slider controls to enter the values, with the illustration responding to your movement of the sliders?

This is much easier than it sounds.

First of all, you will need to check that you have the Developer ribbon showing. If you do not have a ribbon called Developer, you can add it by clicking File, Options, Customize Ribbon and ticking Developer in the right hand column (Main Tabs).

Now go to the Developer Ribbon and click Insert (it has a picture of a toolbox above it) and you will see the following options:

Select the option I have circled above and the cursor becomes a cross-hair for you to draw the rectangle that will become your slider.

Once you have drawn it, right-click on it and select Format Control.

The screen above has all of the settings you need to make your slider work. The settings above could be for selection of Loan Amount in the example earlier. I will go through each in turn:

Current value - This is simply the value you want the slider to be set at before it is moved
Minimum value - The value when the slider is at the extreme left
Maximum value - The value when the slider is at the extreme right
Incremental change - How much you want the value to increase/decrease by when you click the arrow at either end
Page change - How much you want the value to increase/decrease by when you click the space between the selector and the arrow at either end
Cell link - The cell that you want the slider to link to. This will be the Loan Amount variable in the example described. In this case cell C7 will  show the value selected on the slider, and if you edit cell C7, the slider will move.

Have a play and see what you think.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".