Relative Reference and Absolute Reference in Google Sheets: Explained
In this article, you will learn about the relative, absolute, and partial references (or mixed references) in Google Sheets. Understand the difference between them and make your Google Sheets life easy. In summary, they have the following characteristics:
- Relative reference: a referred cell or range shifts the same way a formula does. (Example: A1, A1:B1)
- Absolute reference: a referred cell or range is always fixed. (Example: $A$1, $A$1:$B$1)
- Partial reference (or mixed reference): This is a combination of the relative and absolute reference. One of the row’s or column’s indexes is fixed, and the other index moves as a formula does. (Example: $A1, A$1, A$1:B$1, $A1:$B1,$A1:B1, A$1:B1)
What does a relative reference mean in a spreadsheet?
All cell references are relative references by default. When a function containing this reference is copied from one cell to another, the referred cell or range in the function moves as well to keep the initial relative position to the cell where the formula was initially located. In other words, the referred cell shifts by the same number of rows and columns as the formula does).
Example 1: Relative reference (single cell)
Look at cell C3, which refers to A1 as a relative reference. Assume you copy and paste this cell into cell D4; you shift the formula rightward by one column and downward by one row. The relative reference moves in the same way.
Example 2: Relative reference (range)
The principle of cell reference shown in the first example applies to a range. As you can see in the following screenshot, assuming you copy and paste the formula in cell C8 into cell F11, the referred range changes its position by the same number of rows and columns in the same direction while keeping its size.
What is an absolute cell reference in Google Sheets?
An absolute cell reference is quite simple. Wherever you copy a function incorporating this reference type, the referred cell or range in the formula never changes.
Example 3: Absolute Reference (single cell)
Assume you copy and paste cell B16 (referring to A15 as an absolute reference) to D18. Though the location of the formula changes, the reference stays in A15. This picture is how an absolute reference works.
What is the difference between absolute relative and mixed cell referencing?
In this type of reference, the row or column index is locked (absolute reference), and the other is not (relative reference). So, when you copy and paste a function incorporating this reference to a cell or range, its reference doesn’t change for the locked index as an absolute reference but moves for the other index as a relative reference does.
Why is using appropriate reference types important?
The following picture presents an example of the practical usage of three types of references. The table on the left includes a simple income statement you want to fill, followed by the VLOOKUP and HLOOKUP sections at the bottom. The table on the right side shows what formula each cell on the left side contains (e.g., cell I25 corresponds to cell E25). (Note limited number of formulas are shown due to limited space for the VLOOKUP and HLOOKUP).
When you need to fill a certain number of cells with the same formula with or without function, setting the right formula in the first is helpful to save time. In the following picture, the highlighted cells are the ones we set the first formula for each type of formula (except for cell E41 in the VLOOKUP section and cell E46 in the HLOOKUP section, as they are highlighted purely to clarify the corresponding cells in the “Formula section” on the right side).
Once you set them up, you can copy and paste them as each arrow shows, which is much more effective and efficient than typing formulas in each cell. In this case, we can leverage this merit, especially for % Revenue or % Margin, which calculates the value of items against Revenue. Once you create a formula in cell D27 and paste them into cells E27 and F27, you can copy the three cells, D27:F27, and paste them into Rows 29, 31, and 35. As you can see, each copied formula calculates the amount of an item once cell above (e.g., 50 in cell D34 for a formula in cell D35) against the Revenue amount in the same Column. You can also observe, as you have learned, alphabet letters and numbers unchanged where we insert “$”, a mark for an absolute reference, next to them to the left.
There is no correct answer on cell reference. However, there may be the best and most effective way for most cases, so when you conduct your analysis based on massive data set or build up a financial model or KPI dashboard, especially when you need to show many results by formulas, think about the most effective way to save your time by combining three types of references we learned.
How do I toggle between absolute and relative references in Google Sheets?
- Put your cursor on the cell reference whose type you need to change.
- Press “F4” repeated until you get the reference type you want.
- The reference type changes as follows: relative reference (default) → absolute reference → partial reference (row lock) → partial reference (column lock) → relative reference
