[Google Sheets] How to Use Absolute and Relative Cell References.
Learn how to change referenced cells between relative and absolute quickly by using the F4 hotkey.
Formulas in Google sheets use cell references that are relative by default. What that means is if you drag a formula - the cells that the formula references will change.
As you can see here in cell E2 we have a formula that equals cell B2 + C2. Since the value of B2 is 1 and the value of C2 is 2 the result of the formula is 3.
Now if we copy cell E2 and paste it in a different cell or try and drag the formula down both will result in the new formula returning 0 since the formula is no longer adding the values in cells B2 and C2.
If we go back to edit the formula we can make the cell references absolute by adding $ signs in front of both the letter and number reference of the cell. Now when we copy cell E2 or drag the formula down the result will still be 3 since the formulas in the new cells still reference B2 and C2.
In this case we locked both the column and the row by placing the $ sign in front of both the letter and the number of the cell reference. However if we wanted to we could lock just one.
As an example let's go back to cell E2 and let's add a dollar sign in front of the letters but not numbers. Since the letters represent the columns and the numbers represent the rows, this means that the cell reference will be absolut if we drag the formula left or right but the cell references will be relative if we drag the formula up or down.
We could do the opposite if we remove the dollar signs in front of the letters and add them in front of the numbers. Now when we drag the formula up and down the references will be absolute but when we drag the formula left and right the references are absolute.
Finally we can make some cells absolute and some cells relative. By adding dollar signs in front of both the letter and number of cell B2 but removing all dollar signs from cell C2 will result in maintaining an absolute reference to B2 but the formula will not remain on C2 as we drag the formula down.
There is a shortcut to switch between absolute and relative references which can be done by pressing the F4 key when the cell reference is highlighted.
To show an application where using relative and absolute references would be helpful. Let’s say we have a Google Sheet set up to help us calculate the price of an order for our bike store. Our bike store sells three items; bike frames, bike tires, and bike seats. Each order could have any combination of these parts in any quantity. So we each order we track the quantity of each part ordered. To calculate the total price of each order more efficiently we can multiply the quantity of each part in order one by the price per part. However if we apply that formula to the other ordered by dragging the formula down we can see that since the cells referencing quantity are dynamic we are picking up the correct cells but we will have to change the cells referencing price to be absolute so those cells don’t change.
By using the F4 hotkey we can quickly add the $ signs to the three cells referencing the price and now dragging down the formula will work as required.