Locking a Reference to a Fixed Row or Column in Google Spreadsheets

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. I always forget how to do this, and frequently need to look this up when I use a row at the top of a spreadsheet for holding static configuration values, such as an api login / key. So, I wanted to blog this both for my own future reference and for the benefit of anyone else who encounters the same problem.

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

Here are a bunch of more tips and function reference materials for Google Spreadsheets: http://bit.ly/3ExvOx – not much on the locking cell references here, but some other killer stuff like “GoogleLookup.”

Back to hacking Google Spreadsheets…

Related posts:

1. URL Escape Strings in Google Spreadsheets with a Simple Script
2. vim tips ( via cheat gem )
3. Google Dashboard Just Showed Me How Much Stored Value I Have with Google
4. How To Make a Fixed Link to Twitter That’s Always Visible on Your Webpage