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.
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.