Using a slider to control values in a cell in Excel

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

This is a cool function that can assist you to make easy to use adjustable tables and charts.
Using the example of a mortgage, I used the mortgage template spreadsheet in Excel (under New Worksheet) to create a mortgage example. I then added a graph of the closing balance.

So now we have a nice looking chart that shows the closing balance of our mortgage over time.
Lets say we want to add the ability to easily change the additional payments and see graphically what that will do to our loan timeline.
image

From the Forms toolbar select the spin button. Then click and drag your mouse where you want it on your sheet . You can resize it later if necessary.

image
Now right click the control and select format control. Set it up as follows.
image

Now you can scroll up and down and as you do the monthly loan payments changes by $10 a time, and the loan balance adjusts accordingly. Obviously you can make these settings what ever you desire for any application where you need to rapidly change 1 (or more) variable and see the results.

AttachmentSize
Mortgage loan calculator1.xlsx106.36 KB