Cell Locking

Have you ever wondered what the difference between =A1, =$A$1, =A$1, and =$A1 is in a spreadsheet? The $ represents cell locking in a spreadsheet, and learning about the power of cell locking can 10x your productivity in a spreadsheet.

Cell locking goes hand in hand with autofill. If you don’t lock your cells (meaning there are no $ signs in your formula), then whenever you autofill or copy and paste, the reference will always maintain the same relationship as your original cell. For example, if you have the formula =A1 in the cell A2, no matter where you autofill or copy and paste to, the reference will always be one cell above, because A1 is one cell above A2.

If you fully lock your cells (meaning there is a $ before both the column letter and the row number), then the reference will remain unchanged, or locked to the original cell, no matter where you autofill or copy and paste. For example, if you have the formula =$A$1 in the cell A2, no matter where you autofill or copy and paste to, the reference will always be =$A$1. The current value in A1 will be what populates in all the other cells with that formula.

Alternatively, you can also just lock one dimension, either the column or row.

There are shortcuts to help you lock references quicker (such as pressing F4 to cycle through the 4 variations).

A1 = Nothing is locked

$A1 = The column is locked, but the row is unlocked.

A$1 = The column is unlocked, but the row is locked.

By leveraging the power of cell locking, you can efficiently and accurately autofill formulas throughout your entire block.