- In this worksheet we’re seeing two examplesof form controls: a scroll bar and a spin button.They’re very similar.The scroll bar does have an advantagein ease of use when dealing with larger numbers.You’ve probably seen these, too.Not only with internet usage, but perhapsin some Excel worksheets.They’re easy to use and they’re, if not self-explanatory,fairly close.I’ve got a scroll bar up here.I’m going to drag this slider barto the right a little bit.Keep an eye on cell C2 as I’m doing this.Typically form controls are linkedto a certain cell, in this case: C2.

The chart to the right is also linked to cell C2,and so when I finally let go of the mouse herethe number “35” is present, we also see thatbeing reflected in the chart.Now, if I need to change the scoreI can certainly use the slider bar, or ifit’s nearby maybe I’ll just use the arrows here;something like this.That’s the chart reacting as cell C2 changes.I can slide this farther to the right,and as I start to click the arrows here,also keep an eye on cell G2.

Suddenly it turns in to “eligible.”This is actually a function.It’s the “IF” function, which simply saysif cell 2 is greater than 75, display the word “eligible.”Perhaps the score on this test, if it’s over 75it means that you are eligible for a scholarshipor aid or something like that.The point is there are no hard and fast rules as tohow you might use the controls, so in the example hereall we’re saying is as we make a change to the controleither by using arrows or the slider bar,we change cell C2.

What we do with that information can be wide ranging.In this case, for the moment, all we’re doing iswe’ve got a chart that is reflecting that total,we also have a formula in G2that gives us different answers.A spin button is similar.We could’ve used a spin button up above.We’re using it down below.But as soon as we start to use it I think you can seea difference here.As I use these up or down arrows herewe see the scores here changing.But what if I wanted to rapidly go downto score number five?I can’t get there very quickly withoutstarting to click rapidly; it’s going totake me a while to get there.

So spin buttons are likely to be usedwhen the range of scores is a bit tighter.Like, maybe even tighter than this.If the scores are wide-ranging,a scroll bar like we see up above is likely to work better.When I created this spin button I could’ve made it morehorizontal or vertical; so here’s the horizontalversion of it; and as with all form controls,if you CTRL + click on them,you’ve got some control over how wideor tall or wide this is.This doesn’t have to be very wide.

Doesn’t have to be very tall.Your call on how you want these to look.So I’d like to show you how to create these,and how you might use them.Notice as I click the up arrow or down arrowon the PAC Score the chart to the right is reacting.As I reach certain levels here, suddenly the word“good” pops in there.And if I go up a bit more we see “very good” and so-on.I’m just holding it down, “excellent.”Now, that may or may not be how you’ll be using this,but it gives you some idea.If the score is 60 then it’s “perfect,” and so-on.

This is actually a formula here.I’ll double-click it so you can see this.Let me make this a little bit narrower here.You can see it even better.There we go.It involves a little bit of math here, where we’re simplytaking that value from C8.So once again, we’re taking the result of having usedthe control, in other words the spin button changescell C8, the chart reacts immediately, and the formulathat we’ve set up here simply takes that value,divides it by 10, and then uses the “CHOOSE” functionto come up with these different scores.

So, different kinds of creativity can be involved here.I’d like to show you how to create these.Pretty similar in how we do this.With the CTRL key I’ll selectthe scroll bar up above and simply press “delete.”And I’ll delete the reference here in C2.And create one of these.In order to create a form controlyou need to have visible the “Developer” tab in the ribbon.If you don’t see this in the ribbon, right-click any othertab, choose “Customize the Ribbon,”and you’ll see “Developer” here.

I’ve already got it checked; I’ll simply click “OK.”If you don’t, you’ll make it checked and then “OK.”Once it’s visible, on the “Developer” tabyou will see, with the “Insert” buttonon the controls group here,different kinds of form controls.Right there we see scroll bar.Right there we see a spin button.So let’s say we want to create a scroll bar.When you create these, if at first it’s not obvious,should this be tall, wide, narrow?Not always sure, just make it somewhat medium.You’ll decide later.

You can easily change the shape of this.Now the key step here in making this all come togetheris the idea that you want this linked to a certain cell.The chart to the right, by the way, is alreadyset up to be linked to cell C2.That was done ahead of time.And I’m not at all suggesting that a chartis required in these situations.It does give, in this example, additional visual impactto the scroll bar.So right-clicking the scroll bar; sometimes you haveto do this a second time; choose “Format Control.”The range of values here will be from 0 - 100.

And these don’t always match up perfectlywith what you’re seeing here, so you might haveto make a change.Every time I’ll be clicking the right and left arrowas we see them on the ACT Score, in the actualscroll bar, we want the value to change by one.That’s the incremental change.“Cell link” right here, I want this to be linked to cell C2.The “3-D shading” helps a little bit on the visual.Click “OK.”So this could be wider, taller.We’ll worry about that maybe a bit later.Click outside of this.

As I click the right arrow watch cell C2.If I start to click it a few timesyou see what’s happening in the chart.We can also use the slider bar.One advantage of scroll bar over spin buttonis that when the range of numbers is widewe can move along much faster herewhen we’re changing this.Notice also in cell G2,as I click the right arrows herewe see “not eligible,” suddenly we see“eligible,” so what’s happening here?In G2 is a formula, and it simply saysif cell C2 is greater than 75,as it currently is,we will display the test “eligible.”Otherwise we display “not eligible.”Now, when I created this I could’ve made itmore vertical, so I’m gonna hold down the CTRL keyand simply make a copy of this, and now CTRL,drag-downward, let go of the mouse first.

What if I make this more vertical?Here’s what’s a little bit odd.If this were a vertically oriented scroll bar,I’ll click the up arrow, watch the number to the left.It went down as I click up.That doesn’t quite relate to my sense of logic.I keep pressing the up arrow,and this number goes down.So I wouldn’t use the scroll bar this way.It just doesn’t make sense logically.So CTRL + click this and get rid of it.With the spin button, then it will work.So let’s get rid of the spin buttonand do the same kind of thing.

A little bit faster this time.CTRL + click, get rid of that.CTRL + click here,press “delete” again.Also delete this.And let’s set up a spin button.Here too, “Developer” tab, in the controls group“Insert,” there’s our spin button, click.We can make it horizontal or vertical,I’ll make it horizontal first.And when you do this at times you’ll say,“Oh, it should be this high, this wide.”You’ll change it, perhaps, later.We need to link this to cell C8.

Right-click, “Format Control,”“Cell link,” C8.And just click on it.But here the minimum value and maximum valueare going to be different.Between 10 and 60.And as in the previous example,the incremental change, as it is often,is going to be one.But with a much larger range of numbers,suppose it was between 0 and 10,000 or something,or 0 - 1,000 even, we might want the incremental changeto be 5, or 10, or 100, or whatever.

Here the incremental change is “1.”That simply means what happens when you click the arrow,it doesn’t mean that you can’t go to other values.“Cell link” here, “OK.”I click the arrow, well click outside of it first,then click the arrow.Notice that this cell here for the moment is blank.But when we reach a certain level...We see the word “good” when we reach 30.Here’s that formula again.It’s simply saying, depending upon the value in C8we’re gonna divide it by three, and thenif it’s equal to a 30, anything in the 30’s is gonna be“good,” anything in the 40’s “very good,”50’s “excellent,” and if it’s 60 it’s a “perfect” score.

We see the different displays that would emerge from that.Like we did before with the scroll bar,let’s make a duplicate of this;so CTRL + click to select this, and nowwith the CTRL key again I’ll drag this over hereand reshape it.It could be vertical.Is this any better?Not necessarily, but here at least when we clickthe up arrow the score is going up.And so, that certainly is a viable alternative.The horizontal or the vertical versions herework equally well, whereas they didn’t in the scroll bar.

So I’ve seen different techniques.You might also notice here that when we clickone of these with the CTRL key,that the “Format” tab, the contextual format tab,in the ribbon appears but hardly any feature here is usable.So we’ve seen two different examples of form controls,both created by way of the “Developer” taband both used in different ways, and yetthey’re both tied to the same chart.One of many different ways you can usethese form controls.

Resume Transcript Auto-Scroll

Author

Updated

2/19/2019

Released

1/16/2015

This tips-based course will show Excel users productivity-boosting tricks, cool hidden features, need-to-know functions, and advanced content on subjects such as using PivotTables for data analysis. Tune in every Tuesday for a new tip from expert Dennis Taylor. Each tutorial is a short, self-contained lesson guaranteed to give you new insights into Excel.

Note: Because this is an ongoing series, viewers will not receive a certificate of completion.

Skill Level Appropriate for all

25h 38m

Duration

1,776,401

Views

Show MoreShow Less

Q: Why can't I earn a Certificate of Completion for this course?

A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.