Google Spreadsheet: Locking row or column cell references

From Glitchdata
Jump to navigation Jump to search

When you use Copy Down/Right, AutoFill or Copy & Paste in Google Spreadsheets, cell references automatically change: eg, if you have a formula in C2 that references A2 and you copy down the formula to C3, the formula will automatically reference A3 instead of A2. It is possible to "fix" or "lock" references to a single cell, row, or column.


Lock a Reference to a Column

Suppose you have a formula in D4 that references D1, and you want to lock the reference so that when you copy down from D4 the reference does not change to D2..Dn, but stays fixed to D1. Use the "$" character before the row number:

=D$1

Lock a Reference to a Row

Suppose you have a formula in D4 that references A4, and you want to lock the reference so that when you copy right from D4 the reference does not change to B4..n4, but stays fixed to A4. Use the "$" character before the column letter:

=$A4

Lock a Reference to a Single Cell

Suppose you have a formula in D4 that references A1, and you want to lock the reference so that when you copy down or right from D4 the reference always stays fixed to A1. Use the "$" character before the column letter and row number:

=$A$1