Most spreadsheet programs like Google Sheets and Microsoft Excel have some handy features for locking cell references that you should take the time to learn.
Learning how to lock cell references with the dollar sign can greatly speed up your productivity when you’re doing different formulas.
In this tutorial, we will cover everything you need to know about cell referencing and show you a shortcut for locking cell reference by using F4 on your keyboard.
Table of Contents
In Google Sheets, a cell reference will tell the spreadsheet where to get the values that are used in a formula.
If you want to do a formula in Google Sheets, you can manually type the numbers in your formula or you can use cell references in your formula to do calculations with data in your spreadsheet.
For example, if I want to do a formula of 1+2 =3, I can manually enter these numbers in the formula, or I can use cell referencing of A1+B1 if I have that data in those cells in my spreadsheet.
Using cell references in your formulas will greatly speed up the time it takes to do calculations. If you’re trying to calculate with data in your spreadsheet, you should always be using the cell references instead of typing numbers manually.
Relative Cell Reference
By default, spreadsheet programs like Google Sheets and Excel are set up with relative cell reference.
This means that when you move your formula and paste it down or to the right, your cell references move with it.
If you paste your formula down one row, your cell references move down one row.
If you paste your formulas 3 columns to the right, your cell references move 3 columns to the right.
In the below example, my formula in row 1 is A1+B1. You can see that when I copy and paste my formula down to row 2, the formula automatically changes to A2+B2. When I copy to row 3, the formula changes to A3+B3.
Whichever direction I move by formula, my cell reference will also move. That is what relative cell reference means.
Relative cell references are a very useful feature for quickly doing calculations on sets of data, but sometimes you may want your cell references to stay in place, lucikly you can do that with absolute cell reference
Absolute Cell Reference
If you want to be able to reference one cell in your formula without the cell reference changing, you can accomplish this with absolute cell reference.
To do this, you will need to use the dollar sign ($) in your formula.
In the below example, in cell A1 I have a value, and in column B, I have a different value on each row that I want to add to A1.
By placing dollar signs in my formula in front of the A and in front of the 1 in cell A1, I have locked the cell reference (absolute cell reference).
Again, this is done by using the dollar sign like so: $A$1.
You can see in the above example, the when my formula moves down, the locked cell reference of A1 stays in place, while the B cell reference is relative to the row I am on. This is a very useful skill to learn in spreadsheets.
By using the dollar sign in your formulas, you can lock your formula on a specific cell by placing a $ in front of the letter and number in your formula like this:
You can also lock your formula on a certain row by placing a dollar sign in front of the cell number like this:
Or you can lock your cell reference on a certain column by placing the dollar sign in front of the cell letter only:
Locking Cell Reference Shortcut (F4)
The shortcut for locking your cell reference is to use F4 on your keyboard.
After you select your cell for your formula, press F4 to place two dollar signs in your cell reference for an absolute cell reference.
Press F4 twice for locking row reference, and press F4 three times for locking column reference.
Using F4 is a lot faster than manually typing the dollar signs each time, try it out on your spreadsheet and get comfortable with how to use it
Understanding how spreadsheets reference cells and how to manually control which cells are reference is a must-learn skill for all spreadsheet users.
Whether you are using Google Sheets or Microsoft Excel, cell references work the same way. All of the tricks we have covered here work on both programs.
We hope you found this tutorial helpful, as always watch the video above if you are confused at all.