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