MrExcel's Learn Excel #410 - Absolute References

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Sep 29, 2009.
Sometimes, you want to enter a formula where one reference doesn't change when you copy the formula down. Episode 410 will show you the shortcut for creating absolute references.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Hope you had a great holiday break.
We're back here on Wednesday for another podcast.
This is holiday week, I realize, a lot of people are at work, so I'm going to go back and do some of my favorite tips from the past.
These are some critical tips, if you're new to the podcast, you probably missed our first hundred, two hundred episodes and these are some things I just want to make sure that everyone understands.
First we're to talk about is entering formulas.
You know, when we enter a formula, for example =H2/F2 and copy that formula down a few rows, Excel automatically changes the addresses.
So H2 changes to H6, F2 changes to F6 and allows our formula to work all the way down the column.
That's exactly what we expect, but sometimes we might have setting over here, for example a Tax Rate, 6.5%, it's the local sales tax rate.
So we calculate sales tax from the formula of =F2*L1.
When we copy that formula down, it's not going to work.
We're going to get zeros everywhere and when we take a look at one of the copied formulas, we’ll see that the F2 changed to F5 exactly like we wanted to do, but the L1 changed to L4.
We want that L1 to always point to L1 and it's called an absolute reference.
When you are entering that formula, so you've entered =F2*L1, while the insertion point is right next to the L1, you want to hit the F4 key.
The F4 key will put in two dollar signs: $L$1.
That tells Excel: no matter where we copy this formula, up, down, left or right, we always want it to point to L1.
Now when I drag the fill handle to copy that formula down, you'll see that we get the right answer.
F2 is allowed to change to F5, but L1, because of the dollar signs, always points at L1.
it's called an absolute reference.
You probably have an occasion to use it, maybe once a month, it's really good to know, it's one of those things that you never learn when you're teaching Excel to yourself, but it's very important, can save you lots and lots of time when you need to use it.
Called an absolute reference.
Hey, thanks for stopping by, we’ll see you tomorrow for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top