We use cell referencing in Google Sheets to help our formulas look for the values they need. These values are oftentimes not fixed; sometimes they are not even known at the time the formulas are added. Once the values are added or changed, the Google Sheets will automatically update the formula to get the new value. As a simple example, consider these set of values:
To calculate the percentages, we have to divide the scores in Test 1 by the total maximum score written above:
If we try to copy the equation to the rest of the items, however, the formula automatically adjusts. This is not always good, as we need the cell C1 reference fixed when we copy the formulas by dragging down the small blue box at the lower right. How can we do so? The solution is simple: we add $$ to the cell reference. So for our example, instead of writing C1, we write $C$1:
The cell reference $C$1 makes an absolute reference to the cell located in Column C and Row 1.
That is how you make an absolute reference to a specific cell. Pretty simple, right?
A Keyboard Shortcut
A quicker way, however, is to press F4 immediately after selecting a cell to add to your formula. (For Mac Users, press fn + F4!)
The cell reference C1 now becomes $C$1. If you press F4 again, however, you would notice that it does not easily revert to C1, but instead becomes C$1 then $C1. What do they mean?
Since $C$1 locks down the reference to column C and row 1, referencing cell C1, then similarly:
C$1 locks down only row 1, but the column can change. Look at example below:
For cell C16, we multiply the numbers at B16 and C15. Since we fixed the reference at B16, we see that the only reference that changed is at C15, which updated itself to point at D15 for cell D16, and so on. For cell C17, we multiply the numbers at B17 and C15. But we only fixed the row reference at row 17, so the column of the cell it is referencing also changed.
$C1 locks down only row 1, but the column can change. Look at example below:
For cell C16, we multiply the numbers at B16 and C15. Since we fixed the reference at C15, we see that the only reference that changed is at B16, which updated itself to point at B17 for cell C17, and so on. For cell D16, we multiply the numbers at B16 and D15. But we only fixed the row reference at column C, so the row of the cell it is referencing also changed.
Suscribe to get more data and analytics tips!
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.