Table of Contents
Relative and absolute references are two types of cell references used in Google Sheets. A cell reference is a way to refer to a specific cell or range of cells within a spreadsheet. The main difference between relative and absolute references lies in how they are affected when copied or moved to a different location within the spreadsheet.
Relative references are the default type of reference used in Google Sheets. They are based on the current position of the cell and are adjusted accordingly when copied or moved. For example, if a formula in cell A2 refers to cell B2, when copied to cell A3, the reference will automatically change to B3. This is because the reference is relative to the current position of the cell.
On the other hand, absolute references always refer to a specific cell, regardless of where the formula is copied or moved. They are denoted by a dollar sign ($) in front of the row and/or column reference. For example, if a formula in cell A2 refers to cell $B$2, when copied to cell A3, the reference will remain as $B$2. This is useful when you want to keep a fixed reference to a specific cell or range of cells.
In summary, the main difference between relative and absolute references is that relative references change based on the position of the cell, while absolute references remain fixed. Understanding these differences is important in creating formulas and working with data in Google Sheets.
Google Sheets Relative and Absolute References
Relative and Absolute References
Cells in Google Sheets have unique references, which is their location.
References are used in formulas to do calculations, and the fill function can be used to continue formulas sidewards, downwards and upwards.
Google Sheets have two types of references:
- Relative references
- Absolute references
Absolute reference is a choice we make. It is a command which tells Google Sheets to lock a reference.
The dollar sign ($) is used to make references absolute.
Example of relative reference: A1
Example of absolute reference: $A$1
Relative reference
References are relative by default, and are without dollar sign ($).
The relative reference makes the cells reference free. It gives the fill function freedom to continue the order without restrictions.
Let’s have a look at a relative reference example, helping the Pokemon trainers to count their Pokeballs (B2:B7) and Great balls (C2:C7).
The result is: D2(5):
Next, fill the range D2:D7:
The references being relative allows the fill function to continue the formula for rows downwards.
Have a look at the formulas in D2:D7. You can do this by clicking ion the view menu and choosing Show formulas.
Notice that Google Sheets calculates the next row as you fill.
A Non-Working Example
Let’s try an example that will not work.
Fill D2:G2, filling to the right instead of downwards. Resulting in strange numbers:
Have a look at the formulas.
It assumes that we are calculating sidewards and not downwards.
The numbers that we want to calculate need to be in the same direction as we fill.
Cite this article
stats writer (2024). What are the differences between relative and absolute references in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/what-are-the-differences-between-relative-and-absolute-references-in-google-sheets/
stats writer. "What are the differences between relative and absolute references in Google Sheets?." PSYCHOLOGICAL SCALES, 29 Jun. 2024, https://scales.arabpsychology.com/stats/what-are-the-differences-between-relative-and-absolute-references-in-google-sheets/.
stats writer. "What are the differences between relative and absolute references in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/what-are-the-differences-between-relative-and-absolute-references-in-google-sheets/.
stats writer (2024) 'What are the differences between relative and absolute references in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/what-are-the-differences-between-relative-and-absolute-references-in-google-sheets/.
[1] stats writer, "What are the differences between relative and absolute references in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. What are the differences between relative and absolute references in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.