Creating a Slider in Excel

[Pages:4]Creating a Slider in Excel

While most people usually just type data values into spreadsheets, there are other tools you can use to control values if you want. One I sometimes find useful is a slider (also called a scrollbar). The following instructions take you (almost) step-by-step through the process of creating a slider in Excel that scrolls from -1 to 1 in steps of 0.02. Note that other spreadsheets I've seen also support sliders and the steps to creating them are similar, if not quite the same.

1. Go to the View menu. Select Toolbars and then click the check the Control Toolbox.

2. This will bring up the Control Toolbox. Click on the top left icon to enter "Design Mode." Design Mode Toggle

Scroll Bar (Slider) Control

3. Once you are in Design mode, you can click the "Scroll Bar" control. Your cursor will change to a + as you move off the control toolbox and you can then click and drag to draw a slider on your spreadsheet. Now right-click on the slider you've just drawn and select Properties from the menu of options that appears.

4. Selecting Properties will open the Properties box. The properties can be listed either alphabetically or grouped by category depending on which of the two tabs at the top you select. The properties you are most interested in for a slider are the LinkedCell, LargeChange, Max, Min, and SmallChange values. Min and Max denote the values assigned to the left and right ends of the slider. These values must be unsigned integers. SmallChange is how far the slider moves if you click on the arrows at either edge of the slider while LargeChange is how far the slider moves if you click in the space between the scrollbar "thumb" and the arrow (this also controls the width of the thumb). These must also be unsigned integers. Typically you will want SmallChange

to be 1, so you can control the value of the slider 1 unit at a time. Finally, LinkedCell is the cell in the spreadsheet where the value of the slider will appear. For this demonstration, I'll set LinkedCell to A1, Max to 200, Min to 0, LargeChange to 10, and leave SmallChange at 0.

5. Now I click once again on the Design Mode toggle in the Control Toolbox to leave Design mode so I can actually use my new slider. At first the only thing that looks different is that the little circles marking the edges of my slider disappear. The cell A1 is empty. But once I click on the slider and move the thumb about, A1 takes on different values depending on how I slide the thumb around.

6. Of course, the slider can only produce unsigned integer values. I wanted the slider to give me values from -1 to 1 in steps of 0.01. So I need each 1 unit change in the slider to give me a 0.01 unit change in the final value, which means the slope must be 0.01. So my formula will be y = 0.01x + C. Since I want the left edge, with Min=0, to correspond to -1, I need y(0) = C = -1. So now my function is y = 0.01x ? 1. Finally I check that the right edge at Max=200 corresponds to y(200) = 0.01(200) ? 1 = 2 ? 1 = 1 as desired. If this last step hadn't worked out, I would have had to go back and fix the Max value. If I want big steps to be 0.1, then since 0.1 is 10?0.01, I need LargeChange to have value 10 (as I just happened to choose when I set up the slider in the first place ? gee what are the odds of that).Now I just enter my formula into cell A2 as a function of A1, so cell A2 contains =0.01*A1-1 and the slider moves in steps of size 0.01 from -1 to 1.

So here is a situation where you will need to create linear functions. Note that you can continue to toggle between Design mode and regular mode and change the properties of the slider if you don't get it right the first time.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download