What is Relative References in Google Sheets?
Relative references are the default cell references in Google Sheets. We use them to reference a cell in a sheet. The relative references change when you copy or fill them into other cells. They are handy for repeating formulas and calculations across multiple rows or columns. There are two types of cell references: relative and absolute. The main difference is the way they behave when copied into other cells.
Relative references change when a formula is copied to another cell while absolute references remain the same even when copied to other cells. Relative references are cell references that change when copied or filled into other cells. They are indicated by the absence of a dollar sign ($). Relative references are useful when you repeat the same calculations in different cells. As an example, we have dragged the formula =A1-B1 applied in C1 till C3. See how the formula changes.
Key Takeaways
- A relative reference in Google Sheets is a default reference type for a cell reference that changes when a formula is copied to another cell in the sheet.
- For example, if you enter the formula =A1 + B1 in cell C1 and copy it from cell C1 to cell C2, the formula will adjust automatically to =A2 + B2.
- There are two main types of references, relative and absolute references, with a third being a mixture of the two.
- Relative referencing is useful for calculations that repeat across the range without manually changing the cell references for each entry.
- To change relative references in Google Sheets, change the cell reference to include a dollar sign (e.g., $A$1)
How to Use Relative References in Google Sheets?
Relative References change when a formula is copied to another cell. It is useful when you must change the cell address when a formula is applied across different cells, as you do not have to apply the change manually. Let us look at how to apply relative references in Google Sheets with a simple example.
Step 1: Let us enter a few numbers in Columns A, B, and C and find their sum.
Step 2: Now, select the cell to hold the formula. Enter the following formula in cell D2.
=A2+B2+C2.
Step 3: Press Enter. Relative references are useful whenever you want to repeat the same calculation across multiple rows. So, now drag the formula to D3. You will see the formula in D3 has changed to =A3+B3+C3.
Step 4: By default, all cell references are relative references. When they are copied across different cells, they change based on the position of rows and columns.
Now, drag the formula to D4 and observe the change.
Examples
Relative references are set by default in Google Sheets, so you needn’t manually change anything to use them. With just a few steps, you can easily create formulas, and other calculations in your spreadsheets. Let us look at some examples to illustrate the same.
Example #1
As we know, a relative reference is a cell reference that changes when we move a formula containing relative references to another location in the Google Sheet. To demonstrate this, let us look at an example. Below, we have the purchases made by a person at a local store. Let us calculate the total amount for each purchase.
Step 1: Enter the following formula in cell D2.
=B2*C2.
Here, we are multiplying the two values to find the total spending on each item. Press Enter to get the result.
Step 2: Now, drag the formula all the way down to D5. You will notice that the relative references, by default, kept changing according to the cell reference where the formula was dragged.
Step 3: Now, let us see what happens when we copy-paste the formula to cell D8 for the final item. Copy the formula from any of the cells to cell D8 and observe the result.
Step 4: You can observe how the formula has been changed to =C8*B8, taking into account the cell reference where it has been pasted. Clever, isn’t it?
This feature in Google Sheets allows you to perform calculations across different rows or columns without manually changing the cell references. It allows for quick task execution when making complex calculations.
Example #2
Now, let us look at another example on using relative references across different sheets. We have a sheet where we have the theoretical and practical marks of some students on a sheet.
Step 1: To find the total marks, apply the formula =A2+B2 in cell C2. Press Enter.
Step 2: Now, drag the formula to cell C5 and check how the relative references have changed for each cell in the formula as we drag it across.
Step 3: Before proceeding with the rest of the example, let us look at an important feature called Autofill in Google Sheets. This useful feature suggests how to apply the formula to all the rows in your range to save time and improve accuracy.
If you select the tick mark highlighted in black in the image below, the formula will be applied to all the succeeding cells in the range without the need to drag it.
Step 4: The formula is applied to all cells. The relative reference is also used in this case.
Step 5: Now, on another sheet, we have the details of two more students, as shown below.
Step 6: Now, copy the formula in the previous sheet cell C5 and paste it in the other sheet’s cell C2.
You can see how the relative reference in the formula changes even across sheets, making it very practical to copy-paste the formula across cells.
Example 3 – Using Relative and Absolute References Together
In the example below, we enter the details of a company’s employees who are to receive a fixed bonus. We insert the bonus percentage in cell C2.
Step 1: Now, to find each employee’s bonus amount. First, multiply the bonus % in cell C2 by each employee’s salary.
Absolute reference
Before that, we must fix the cell containing the bonus percentage. It makes the cell’s reference absolute. To create an absolute reference, you use a $ sign before the column and row values.
Now, apply the following formula in D2.
=B2*$C$2.
To fix the cell reference containing the bonus as an absolute reference, we double-click on cell C2 and place the cursor where we want to enter the $ sign. Enter the $ symbol before the row and column values.
Press Enter.
You get the bonus amount as shown above.
Step 2: Now, to find the same for Emp 2, drag the formula to D3.
The absolute reference hasn’t changed, while the relative reference does.
Step 3: Now, drag the formula to D5 and observe the results.
When you now copy-paste the formula in cells D2 to D5 for the other employees, the bonus percentage remains fixed because of the absolute reference.
Important Things to Note
- Relative references can be used in array formulas. When you apply array formulas in Google Sheets, relative references will automatically adjust based on the position of the array formula.
- To create relative to absolute references in Google Sheets, we must add a “$” symbol before the column and row reference using the absolute reference Google Sheets shortcut key F4.
- To reference an entire column as relative, you mention it as follows: e.g., A:A.
Frequently Asked Questions (FAQs)
When you wish to remove or add the $ symbol in Goole Sheets for relative or absolute references, respectively, first select the reference or formula and click on the key F4. Continue clicking on it until the $ symbol disappears.
You can use the F4 key to toggle through the options for either locking or unlocking a cell reference.
1. $A$1 – Absolute reference
2. A$1 – Absolute Row
3. $A1 – Absolute Column
4. A1 – Relative reference
When you copy a formula with relative references across both rows and columns, the references will be automatically adjusted in both directions. For example, if you copy =A1 + B1 from C1 to D3, the formula will change to =B3 + C3 as both the row and column references adjust themselves according to the new location.
We have two different cell reference styles in Google Sheets.
– A1 reference
Every spreadsheet contains cells that are identified by their column alphabet followed by their row number. Such a style of referencing is known as A1-style referencing. For example, C2, B6, and A1.
-R1C1 reference
The R1C1 notation refers to a cell using a row number and column number instead of the letter-number format. Here, R means row, and C means column. For instance, you refer to a cell as R1C1 (row 1, Column 1). We refer to Cell B6 as R6C2.
Both A1 and R1C1 references can also be absolute or relative. In R1C1 style, the indication of relative references are by square brackets around the row or column number.
R[1]C[1] is the relative reference to the cell, one row down and one column to the right of the active cell.
Download Template
This article must help understand Relative references in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Relative references in Google Sheets. We learn how to use it along with absolute references with step-wise examples. You can learn more from the following articles. –
Leave a Reply