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.

Callie Malvik

Callie is the Content Manager at Collegis Education, overseeing blog content on behalf of Rasmussen College. She is passionate about creating quality resources that empower others to improve their lives through education.

female writer

Related Content

This piece of ad content was created by Rasmussen College to support its educational programs. Rasmussen College may not prepare students for all positions featured within this content. Please visit www.rasmussen.edu/degrees for a list of programs offered. 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. Rasmussen College is a regionally accredited private college and Public Benefit Corporation.

Add your comment

*

Please enter your name.

*

Please enter your email.

*

Please enter your comment.

Take the Next Step—Talk to Us!

Request More Information

Talk with a program manager today.

Fill out the form to receive information about:
  • Program Details and Applying for Classes 
  • Financial Aid and FAFSA
  • Customized Support Services
  • Detailed Program Plan

Step 1 of 3

What's Your Name?

Please enter your first name.

Please enter your last name.

Step 2 of 3

Contact Information

Please enter your email address.

Please enter your phone number.

Please enter your five digit zip code.

Step 3 of 3

Program Preferences

Please choose a school of study.

Please choose a program.

Please choose a degree.

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.

icon-colored-advance icon-colored-build icon-colored-certificate icon-colored-growth icon-colored-national icon-colored-prep icon-colored-regional icon-colored-state icon-colored-support logo-accreditation-acen logo-accreditation-ccne ras-logo-flame ras-logo-horizontal ras-logo-stacked icon-filter icon-info-circle icon-mail-forward icon-play-solid icon-share-square-o icon-spinner icon-tag icon-general-connect icon-general-degree icon-general-discuss icon-general-email icon-general-find icon-general-laptop icon-general-leader icon-general-map icon-general-paperwork icon-general-phone icon-general-speak-out icon-simple-chat icon-simple-desktop icon-simple-find icon-simple-hamburger icon-simple-phone icon-testimonial-quotes icon-social-facebook-square-colored icon-social-facebook-square icon-social-facebook icon-social-google-plus-square icon-social-google-plus icon-social-instagram icon-social-linkedin-square-colored icon-social-linkedin-square icon-social-linkedin icon-social-pinterest-p icon-social-twitter-square icon-social-twitter icon-social-youtube-play-colored icon-social-youtube-play icon-util-checkbox-white icon-util-checkbox icon-util-checked-white icon-util-checked icon-util-chevron-down icon-util-chevron-left icon-util-chevron-right icon-util-chevron-up icon-util-open-window-button icon-util-open-window-link icon-util-pdf-button icon-util-pdf-link icon-util-refresh icon-util-x