0:07

The next topic that we're going to discuss is cell locking.

Cell locking is a powerful tool that is used extensively in Excel.

As we saw in the previous section, Excel uses relative references within formulas.

This means that it will automatically move the cells that are being

referenced when the formula is pasted across rows or columns.

Sometimes this is exactly what you want to do, but not always.

This is where cell locking comes into play when you do not want

Excel to move across rows and columns using relative reference.

Cell locking basically tells Excel not to move the reference when copying and

pasting.

A locked cell is indicated by the dollar sign.

A dollar sign in the front of a column letter locks the column, and

a dollar sign in front of a row number locks the row.

If you put a dollar sign in both the front letter and

the back row number, you lock the entire cell.

You can lock a cell by manually typing out the dollar sign or

by using the F4 key shortcut to place the dollars in a different

combination around the row, columns, both, or none.

Cell locking helps increase efficiency when performed correctly.

And also helps you avoid hardcoding values within a formula.

Second, I find many spreadsheet errors come from lack of cell locking or

hardcoding.

Let's practice cell locking.

In exercise one, we're being asked

to calculate the monthly savings using 5% growth rate.

That can be found in cell L12.

1:59

We're going to start by calculating the savings in February

by applying the 5% growth rate to the January savings of $100.

We start by typing = followed by the amount of

the savings in January, which can be found in cell G12.

We then multiply the value by 1 plus the growth rate,

which can be found in L12.

2:33

As you can see, the savings for February will be $105.

The same formula can be used to calculate the remaining months.

So let's copy down the rest of the tables.

Notice that something must have gone wrong, since the savings for

the rest of the months remain constant instead of growing by 5%.

3:35

Which is good, but it'll also increase the row number for the growth percentage,

which is not what we wanted.

We wanted that 5% being multiplied by each month.

We can use cell locking to tell Excel that we do not want it to

increase the row numbers for the growth rate.

In order to do so, let's go back to the formula for February.

If we press F2, it opens the formula and lets us edit it.

We want to lock both the column and the row, so

we're going to add a dollar sign in front of both the L and the 12.

Now our formula is ready for copying.

Let's copy it and paste it down the table.

As you can see, the savings grows every month.

4:17

We're going to be doing the same thing for savings in column H,

only this time, we will be using the F4 shortcut to lock down our growth rate.

First, let's write our growth rate formula for February the same way we did before.

4:34

As we can see, this calculates the correct value for February.

However, we already know that we can't copy this

value down unless we walk in the growth rate.

In order to lock cell L12,

we click on the reference within the formula and then press F4 to lock it.

4:54

As you can see, Excel puts in dollar signs,

locking the cell entirely when you click F4 the first time.

Now if we press F4 again, we notice that one of the dollar signs is removed.

The cell row has now been locked, but the the column has not.

5:12

If we press F4 again,

we see that the dollar sign has moved from the row to the column.

And if we did it one more time, we go back to the cell being unlocked.

We want to lock the growth rate, so let's press F4 once more to do that.

Now the formula is ready to be copied down.

As you can see, the growth rate is correctly calculated for

each of the months.

Now it's your turn to practice cell locking with exercise 1B.

Remember that you can lock a cell entirely by adding the dollar sign manually or

by using the F4 shortcut.

[MUSIC]