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]

Â