Excel 2010 Tutorial - How to Use the Fill Handle and Static Cell Reference

In this video of the Excel 2010 Tutorial series, School of Technology  Program Coordinator Justin Denton describes how to use the fill handle and static cell reference in Microsoft Excel 2010. This tutorial video is a great beginning step to your courses in the School of Technology and Design program at Rasmussen College.

Transcript

Well, we can do that pull down option again, and if I pull this down, it goes ahead and populates the rest of the details. But the only problem is when I did that, and if you take a look at the table, it's incrementing the table values as well because I didn't make them a static reference. Has anybody used the static references in Excel formulas?

So what a static reference is, is just basically locking that table, or locking that value into place. So when you pull the fill handle down and drag it down, it doesn't change specific values. So I want D4 to change, because when I pull down, I want D5, D6, D7. So I'm not going to mess with this one. But my table, I can't have it change. It needs to stay the same.I can show you this, that if I put in SPN here, because it incremented the value, it now can't find the SPN code. I know it can, because if I go up here, it finds it. But if I notice that my table references, B2 through C7, versus B3 through C8, something changed. It incremented when I pulled it down.

So if I go back up here and I do a just really quick thing, put a dollar sign, basically, in front of the B and in front of the 2, and dollar sign in front of the C and a dollar sign in front of the 7, by doing that, it makes it a static reference. So when I go to drag down again, it knows not to increment that table value. It still means B2 through C7. It just keeps that reference without adjusting it as you're pulling down. And you can do that with your formulas as well.

So if I happen to have one static value, so I've got my cost of inventory. Well, let's just say my boss comes in and says, we need to double our inventory. Well, I can quickly multiply all these by two and put them down below. And I'll put a value, we'll say, doubled inventory, or we'll just put inventory increase. So below this, I need to know what my inventory increase is. I'll make them bold.

Well, I don't want to have inventory increase just be stuck to double, because my boss may come in and say we need to triple our inventory on hand. There's a big event about to occur. So I need to specify a value somewhere. So I'll put in here, Boss's Inventory Increase Request. So now my boss can type in whatever value he wants in here.

Well, today he wants our inventory to triple. Chicago's hosting something. We're the primary vendor for that event. And now, we need to be able to have inventory on hand to be able to supply that event, along with our current customers. So I need to build a quick formula that will determine what would be the triple value of my inventory cost.

To do that, I would do the equals sum, take the inventory cost, and then multiply it by B18 in my bracket. And, you know, it's a pretty basic formula. We took one value, multiplied it by another. But the thing is, if we want to fill handle down, well, it went to G5, which was right. But it automatically incremented that B18 value, which now tried to multiply by nothing, because there's nothing in B19.

So I still want to use the fill handle function and not have to go in here and manually change all the formulas. So if we go back and we basically apply that same principle that we did with the building a table, and put, well, G4 I need increment as I pull down. I'm not going to touch that. But B18 needs to stay a static value. I need everything to multiply by B18. So then I'll put a dollar sign in front of the B, and a dollar sign in front of the 18, which now makes that a static value. So as I fill handle this down, it'll go ahead and multiply all these by the value 3 here, and create what it would cost to triple my inventory.

So from 1140 to 3420, from 8325 to 2475, and so forth. So I've made that static reference, so now I can build into my formulas and have one single point of update. So I've built that out. I've went through the process to build that out, so if my boss comes in and says, you know, today, we just need double the inventory. Then I'll go back and it'll go ahead and update all that value on the fly, because we've made that static reference with all those various cells. I mean, you go in here and manually reference all those, and manually build out all those formulas, but after you've done it once, now you've got it set where it references one single point and updates the rest.

External links provided on Rasmussen.edu are for reference only. Rasmussen College does not guarantee, approve, control, or specifically endorse the information or products available on websites linked to, and is not endorsed by website owners, authors and/or organizations referenced.

Callie is an Inbound Marketing Specialist whose aim is to compose helpful and encouraging content to assist Rasmussen College students. Her eagerness for helping others combined with her creative writing passion makes her a great asset to past, present and prospective learners.

comments powered by Disqus
close

Request Program Information Request Program Information

Rasmussen College, SUPPORT+, and You

Experience the value of our SUPPORT+ network of student services by speaking with one of our program managers. We'll assign the program manager best qualified to support and contact you by phone or email to discuss your future at Rasmussen College.

Please complete all fields

Personal Information

Campus and Program Selection

By requesting information, I authorize Rasmussen College to contact me by email, phone or text message at the number provided. There is no obligation to enroll.