Welcome to Excel and business math video number two.
Hey, in this video, we've got to talk about copying formulas
with relative and absolute cell references.
Yes, in this video, we have two simple concepts
we need to learn, so that when we are making
a calculation like total pay, and we need
to subtract from each net pay for each employee
a holiday bonus.
Now instead of creating each formula individually,
we'll learn the difference between relative cell
references and absolute cell references.
And what will this do for us?
It'll help us to create lots of formulas quickly.
Now before we go talk about relative and absolute cell
references, let's click on the sheet Last Video.
And we want to remind ourselves about what
we did last video, because last video we created a formula
and copied it down a column.
And we saw for the first time a relative cell reference.
So our goal here is to create our formula.
Equals signs always start a formula.
We're going to use our arrow keys, Left Arrow, Left Arrow,
to get the price.
Then the multiplication symbol, either on the number
pad or Shift-8.
And then Left Arrow to get our number of units sold.
Now we want to copy this down.
But before we copy it down, let's
think about the cell references.
As we mentioned last video, that's not really B2
and that's not really C2.
This formula, with these two relative cell references--
we'll always look for the blue one, one, two
cells to the left, and for the orange one, one cell
to the left.
When I Control-Enter to put the formula in the cell
and keep the cell selected, I point to the fill handle.
And when I see my angry rabbit cursor,
I double-click and send it down.
I immediately go to the last cell
and hit F2, and sure enough, from the point of view
of the cell with the formula, those cell references
are always looking relatively one to the left times one
to the left.
When I hit Enter and go down, we did a second example.
And we used relative cell references in this one also.
Equals sign, Up Arrow, Up Arrow.
To get relative cell reference, always look two above, minus
Up Arrow.
Relative cell reference, always look one above.
Control-Enter.
I copy it to the side.
I go to the last cell and hit F2.
Those are relative cell references.
So we copied our formula.
And for both examples last video,
we needed two relative cell references.
I'm going to click Escape.
Let's go look at the sheet, copy formulas,
and talk about our example in this video.
Now here we have an employee net pay
and there's a holiday bonus.
So we need to add net pay and holiday bonus.
No problem.
We create a formula.
Equal Sign, Left Arrow to get net pay,
plus Right Arrow, Right Arrow.
Now, we only have one cell here.
We're not copying the formula anywhere.
So I can simply hit Enter without worrying
about what type of cell reference I had in the formula.
But now let's come down here and we have an entirely different
situation.
Our formula always needs to look at the net pay
in the particular row.
But for every single cell in this column,
somehow we need the formula to always subtract the holiday
bonus in E14 .
So let's create our base formula.
Equal Sign, a Left Arrow, plus Right Arrow, Right Arrow.
Now I'm going to make a mistake here, Control-Enter,
and then we're going to copy it down by double clicking.
That doesn't look correct at all.
Hey, the reason that we always go to the last cell
and hit the F2 key is to verify that the cell references are
looking in the correct location.
Well, the blue one's definitely looking at one cell
to the left, the correct net pay, but the orange one--
that's not correct at all.
It's looking one, two cells to the right,
but it's supposed to be always looking at-- locked on cell
E14.
So I'm going to hit Enter, and I want to Control-Z to undo.
Control-Z again.
And Control-Z. I did three Control-Zs
to revert back to what we had before we created our formula.
Equal Sign, Left Arrow to get the proper relative cell
reference.
One cell to my left plus, and now I'm
going to Right Arrow, Right Arrow.
Well, right now, we know that's not going to work.
But no problem.
Make sure your cursor is touching the cell reference
that you want to lock or make absolute,
and then hit the F4 key.
When you hit the F4 key and your cursor
is touching the cell reference, it puts the dollar signs in.
It locked the column reference E and the row reference 14.
That means no matter where we copy this formula,
it's always locked on E14.
Now, those dollar signs are arbitrary.
Bricklin and Frankston, who invented the spreadsheet
VisiCalc over 30 years ago, just had
to pick some character that indicates that this cell
reference is locked.
So the dollar sign doesn't mean money or anything.
It just means, hey, that cell reference is locked.
Now we can Control-Enter to enter the formula,
and then double click and send it down.
Go to the last cell and hit the F2 key
to verify that the cell references are looking
in the correct location.
That's totally amazing.
That means we have the ability to choose what type
of cell reference we want, because sometimes we definitely
want the cell reference to move throughout the copy action,
that blue cell reference moved, as we copy down.
And we want the choice to be able to lock it.
So as we copy the formula, every single cell is locked on E14.
Relative cell reference moves throughout the copy action.
Absolute cell reference or locked
means that it's locked throughout the copy action.
That F4 key will add the dollar signs, all right?
I'm going to hit Enter.
Now, let's go look at another example.
We have the same exact data set.
Employee net pay, employee net pay.
So I'm going to create my formula
to add the holiday bonus.
Equals, Up Arrow, relative cell reference.
One cell above plus Down Arrow, Down Arrow.
F4 key to lock it.
So it's always going to be relative cell reference one
above plus whatever's in B32.
Control-Enter.
Hover your angry rabbit cursor over the fill handle and click
and drag.
And there we go.
We go to the last cell and hit F2.
That is beautiful.
We have our relative cell reference,
and locked our $100 Christmas bonus.
Now I'm going to hit Enter.
Use my horizontal scroll bar to scroll over.
Now let's look at example number four.
This example was one of your homework problems last time.
At least the total sales column was part of your homework.
Sales after discount will be new in this video.
Now here is our situation.
Both formulas we need to enter and copy down.
Enter and copy down.
But here, total sales is defined as price times units sold,
and both will be relative cell reference.
So you ready?
Equal Sign, Left Arrow, Left Arrow times Left Arrow.
We're going to copy this formula and both those cell references
should look relatively for each row as we copy down.
So we simply Control-Enter, double click, and send it down.
Be sure to go to the last cell and hit F2 to verify.
Yes, in fact, we have two relative cell references.
Enter.
Now sales after discount.
We have a flat discount.
Every single transaction, we need to subtract $75.
So in cell E42, Equals, Left Arrow, Minus, Right Arrow,
Right Arrow.
And I'm immediately going to lock it by using the F4 key.
The dollar signs are put in, so now it's absolute, or locked.
Control-Enter.
Double click, and send that formula down.
Go to the last cell and hit F2.
Verify.
Yes indeed, relative cell reference and absolute.
That's exactly what I need.
All right, so this video, we saw F2 relative and absolute cell
reference.
It's a simple topic for this video,
but we'll be using relative and absolute cell references
throughout the entire class.
And it's amazing how much time they save, especially when we
see next video these cell references in combination
with Excel's golden rule.
Now there are some homework problems.
There's one, two, three homeworks.
Remember the blue ones.
That's where you do your homework
for practice, and the red one.
You click on that one.
You want to look at the answer after you're done.
All right, if you liked that video,
be sure to click that thumbs up and leave a comment and sub,
because there's always lots more videos to come from Excel
is Fun, including our next video, number three,
where we'll talk about Excel's golden rule.
All right, we'll see you next video.