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
Share Your Story Ideas
Our campuses and online community have stories to tell and we want to hear them! Did your campus raise the most money in the community for an organization? Do you have online study tips for other students? Would you like to share a personal success story about overcoming an obstacle while earning your degree?
To have your story idea considered:
  • You must be a faculty member, current student or graduate
  • Story ideas must be regarding Rasmussen College or an inspiring story about a student at Rasmussen College
  • Your submission must be original and may not have been published elsewhere online already
Please Note: Your story idea may be featured on the Rasmussen College News Beat or on one of our social networks. A member of our news team will contact you should we move forward with a blog post.
Feel free to suggest an idea for a blog post to be featured on the Rasmussen College News Beat by filling out the form below:

First Name: (required)

Last Name: (required)

Email Address: (required)

Phone Number: (required)

500 characters or less

close

Your Story Idea Has Been Submitted

Thank you for sending us a story idea! We’re reviewing submissions and may contact you soon to learn more about your story. In the meantime, make sure to check out our current blogs to see what’s happening on campus.

close