What Is Absolute Reference In Google Sheets?
An Absolute Reference in Google Sheets is a feature used in formulas to lock or fix the cell range or the cell references of the dataset, by adding a $ symbol before a cell references’ row number and the column name. When we use the Google Sheets Absolute Reference, the cell reference remains fixed when the formula is copied or moved to a new location like another cell or spreadsheet. It is useful when we want to keep a reference to a specific cell constant in a formula.
For example, we will calculate the total price according to the specific quantity of school supplies for the dataset given below. We have a fixed wholesale rate in column C with which we will calculate the total.
Select cell D2, enter the formula =B2*$C$2, press “Enter” and drag the formula from cell D2 to D4 using the full handle, as shown below.
The output is shown above in column D. We see that the prices’ cell reference is locked using the Absolute Reference Feature and we are able to calculate the total prices of all the other items in the dataset.
Shortcut
The Shortcut for Absolute Reference in Google Sheets is,
In the formula once we insert the cell reference that must be an absolute cell reference,
- Windows,
- Press only the function key F4.
- For some laptops, press the keys “fn+f4”.
- MAC – Press the keys “fn+f4”.
How To Use Absolute Reference In Google Sheets?
We can use Absolute Reference in Google Sheets using 2 ways, namely,
- Manually Entering ‘$’ Symbol.
- Using the shortcut keys.
Method #1 – Manually Entering ‘$’ Symbol –
Step 1: Select the formula cell or the formula result cell, that must be made absolute.
Step 2: When typing the formula or after inserting the formula in the selected cell, insert the ‘$’ symbol before the row and column Coordinates. If it is cell A1, then convert to $A$1.
Step 3: Press the Enter key to view the output.
Method #2 – Using the shortcut keys –
Step 1: Select the formula cell or the formula result cell. It is the cell that must be made absolute.
Step 2: After inserting the formula in the selected cell, select the cell reference. Press the F4 key, i.e., if it is cell A1, then automatically it will get converted to $A$1.
Step 3: Press the Enter key to view the output.
[Special Note:
- If we press the F4 key for the first time, it will add the ‘$’ symbol for both the row and column coordinates.
- The second time we click it, we can see the ‘$’ added only to the row coordinate.
- When pressed for the third time, the ‘$’ gets added to the column name.
- The fourth time we click, the ‘$’ symbol gets removed, making the cell reference relative.]
Examples
Let us consider some Absolute Reference in Google Sheets examples to apply to a fixed rating, fixed conversion and a fixed discount.
Example #1
For the definition dataset, we will calculate the total price according to the specific quantity of school supplies. We have a fixed wholesale rate in column C with which we will calculate the total. We will also see the results if the cell references are not absolute.
The steps to apply the Absolute Reference in Google Sheets are,
Step 1: Select cell D2. Enter the formula =B2*$C$2, as shown below.
Step 2: Press “Enter”. Drag the formula from cell D2 to D4 using the full handle, as shown below.
Step 3: Select cell E2. Enter the formula =B2*C2, as shown below.
Step 4: Press “Enter” and drag the formula from cell E2 to E4. Do it using the full handle, as shown below.
We can see the difference in results with and without Absolute Reference in columns D and E, respectively. We are able to calculate the total prices of all the other items in the dataset if we use the Absolute Reference, but unable to calculate without the same.
Example #2 – Apply a Fixed Rating to List of Items for Comparison
The dataset given below consists of a list of sports and its ratings both for indoor and outdoor games. We will apply the fixed ratings and compare between the different sports using the Absolute Reference.
The steps to Apply a Fixed Rating to a List of Items for Comparison usingthe Google Sheets Absolute Reference are,
Step 1: First, let’s compare without Absolute Reference. So, select cell E1, enter the formula =C2=D2, press “Enter” and drag the formula from cell E2 to E9 using the fill handle, to get the following results.
Step 2: Now let’s compare with Absolute Reference. Therefore, select cell F1, enter the formula =C2=$D$2, press “Enter” and drag the formula from cell F2 to F9 using the fill handle, to get the correct results, as shown below.
We see that in column F, due to the Absolute Reference, we are able to compare the fixed ratings with all the ratings of the games and derive the results.
Example #3 – Use a Fixed Conversion Rate for Currency Conversion
The dataset given below consists of fruits and their price without the currency. We will Use a Fixed Conversion Rate for Currency Conversion using the Absolute Reference. Here, we will use $1 as a fixed value to convert the price to dollars.
The steps to Use a Fixed Conversion Rate for Currency Conversion usingthe Google Sheets Absolute Reference are,
Step 1: First, let’s compare without Absolute Reference. So, select cell D1, enter the formula =B2*C2, press “Enter” and drag the formula from cell D2 to D8 using the fill handle, to get the following results.
Step 2: Now let’s compare with Absolute Reference. Therefore, select cell E1, enter the formula =B2*$C$2, press “Enter” and drag the formula from cell E2 to E8 using the fill handle, to get the correct results, as shown below.
We see that in column E, due to the Absolute Reference, we are able to convert the price to a fixed currency and derive the results.
Example #4 – Apply a Fixed Discount Rate Across Multiple Product Prices
The dataset given below consists of fruits and their price without the currency. We will Apply a Fixed Discount Rate Across Multiple Product Prices using the Absolute Reference.
The steps to Apply a Fixed Discount Rate Across Multiple Product Prices usingthe Google Sheets Absolute Reference are,
Step 1: First, let’s compare without Absolute Reference. So, select cell D1, enter the formula =B2-(B2*C2), press “Enter” and drag the formula from cell D2 to D6 using the fill handle, to get the following results.
Step 2: Now let’s compare with Absolute Reference. Therefore, select cell E1, enter the formula =B2-(B2*$C$2), press “Enter” and drag the formula from cell E2 to E6 using the fill handle, to get the correct results, as shown below.
We see that in column E, due to the Absolute Reference, we are able to calculate the final price after applying a fixed discount. However, column D returned the same price without discount except for cell D2.
Important Things To Note
- To make a cell an Absolute Reference when we use the shortcut key F4 key, press F4 key once, to add the ‘$’ symbol for both the row and column coordinates, twice to add the ‘$’ only to the row coordinate, thrice to add the ‘$’ only to the column name and four times to remove the ‘$’ symbol, making the cell reference relative.
- It helps you avoid using a varying value when using the value as a constant in the formulas.
- Ensure to double-check the formulas before we copy or drag them, whether the cell is absolute or not, or have we accidentally deleted or modified the locked reference.
Frequently Asked Questions (FAQs)
We can use the Absolute Reference in Google Sheets in the following situations:
a. When there is a need to move the formula to a different worksheet or workbook without impacting the cell references.
b. When the formula needs to be dragged to the remaining cells of the range, but the column and row coordinates should stay fixed.
c. When multiple calculations need to be performed by referring to a particular cell again and again.
d. When we want a cell reference to remain constant in formulas in different cells of the same worksheet or even in other workbooks.
Removing Absolute Reference in Google Sheets from a cell implies changing them to either mixed or relative references.
• We have a couple of ways to remove the Absolute Reference in Google Sheets, namely,
• Manually delete the dollar sign ($), which precedes the column label and/or the row number.
• Double-click the cell containing the formula. Select the absolute cell reference and press the F4 key once or thrice to change the cell reference changes to mixed or to completely remove it, respectively.
The differences between Relative and Absolute References in Google Sheets are listed as follows:
• Absolute References stay static irrespective of where they are copied, while relative references change when copied to a different location.
• In an Absolute Reference, both column and row coordinates are fixed. On the other hand, in a relative reference, both column and row coordinates are variable.
• An Absolute Reference is used when calculations involve a specific cell to be referred multiple times. In contrast, a relative reference is used when the same calculation needs to be repeated across various columns and/or rows.
• An Absolute Reference is inserted either manually or by pressing the F4 key.
Download Template
This article must be helpful to understand the Absolute Reference in Google Sheets, with its features and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Absolute Reference In Google Sheets. We learn how to use it to lock a range along with its shortcut methods and examples. You can learn more from the following articles. –
Leave a Reply