Google Sheets is a powerful tool that uses formulas to simplify work. And formulas work in different ways. One useful function is the ability to use cell references instead of actual values in formulas.
Knowing how to use cell references in Google Sheets is essential as it allows you to properly execute formulas across multiple cells. Often, we want a part of the formula to adapt to the cell when it is copied and pasted, but we want certain cell values to remain unchanged. That’s where absolute references come in.
How to Use Absolute References in Google Sheets
Here’s how you can use absolute cell references in Google Sheets:
- Click on the cell where you want to enter the formula.
- Type the equal sign (=).
- Enter the cell address that contains the number of units.
- Add the function you want to use in the formula.
- Type the cell address that contains the unit cost.
- Add a dollar sign ($) before the row and column to make the reference absolute.
- Press Enter to execute the formula.
Types of Cell References in Google Sheets
There are essentially two types of cell references in Sheets: relative and absolute. They affect how data behaves when copied and moved from one cell to another. A relative reference changes when the formula is copied from one cell to another. However, no changes are made to the data when it is copied using an absolute reference.
By default, all cells have relative references. When the data is copied to other cells, it adjusts based on its position in the rows and columns. For example, let’s say there is a formula in cell A1 that is =B1+C1. If we copy this formula to cell A2, instead of maintaining the same cell references, it will adapt to the new positions. So the formula will change to =B2+C2.
An absolute reference in Google Sheets is the opposite of that. Instead of changing the reference based on its new position, the copied formula will keep the same values. Let’s take the same example. We have a formula in cell A1 that is =B1+C1. When we copy the formula to cell A2, the formula will keep the row and column constant, so the formula will remain =B1+C1. Absolute references are not the same as absolute values in Google Sheets.
How to Use an Absolute Cell Reference in Google Sheets
You don’t need to make any modifications to use a relative reference as it is enabled by default in Sheets. However, you will need to follow a specific format to use an absolute reference. All you have to do to turn a relative reference into an absolute reference is to add a dollar sign ($) before the row or column.
You can assign an absolute reference to either a row or a column individually. Let’s take the example of cell address A1. To assign an absolute reference to the row but not the column, we will write it as A$1. To keep the column unchanged but make the row relative, we will write it as $A2. By writing the cell address as $A$1, both the column and row will not change when copied or automatically filled.
An Example of Absolute Reference in Sheets
In this example, we have a table that contains sales data for a store. The first row contains the number of units sold during purchase, while the second row contains the unit price. The third row contains the total price of the units sold. This is calculated by multiplying the number of units by the unit price. As you can see, this uses a relative reference.
Here are the steps to do that:
- First, click on the cell where you want to enter the formula.
- To start the formula, use the equal sign (=).
- Now, enter the cell address that contains the number of units. In this case, it’s cell A2.
- Add a multiplication symbol (*).
- Finally, type the cell address that contains the unit price. In this case, it’s cell B2.
- Press Enter to execute the formula.
If the same formula is copied to other cells below cell C2, they will use a relative reference and change the cell address based on their location. Let’s look at an example and copy the formula to cell C7.
As you can see, the formula smartly changes the address based on the location.
Most of the formula uses a portion of the formula we discussed earlier. However, to demonstrate absolute references, let’s say that every online purchase of the product has a fixed shipping fee of $5. As this value is specified in cell G1, we don’t want the cell address to change when the formula is applied to other cells. We will use Google Sheets’ absolute reference for this.
Here are the steps to do that:
- Click on the cell where you want to enter the formula.
- Type the equal sign (=) to start the formula.
- Enter an opening parenthesis to nest the first equation.
- Enter the cell address that contains the number of units, cell A2 in this case.
- Add a multiplication symbol (*).
- Enter the cell address that contains the unit price, which is B2 in this example.
- Add a closing parenthesis.
- Write the plus sign (+).
- To enter the cell address as an absolute reference, add a dollar sign ($) before the row and column. It will be written as $G$1 in this case.
- Press Enter to execute the formula.
To test if we have applied the absolute reference correctly, we use the suggested autofill feature to automatically fill the formula. As you can see, all cell values have the $5 shipping fee added, which means we have successfully followed the steps.
Shortcuts for Absolute References in Google Sheets
While adding the dollar sign to the cell reference is quite fast, there is an even easier way to apply absolute references. You can select the cell for absolute reference on Windows and use F4 in Google Sheets. The keyboard shortcut for absolute reference in Google Sheets for Mac is fn+ F4.
By pressing the F4 key while selecting the cell, you toggle between absolute reference and relative reference. Once in absolute reference, pressing the shortcut will change it to absolute row reference, then absolute column reference.
How to Use a Relative Cell Reference in Google Sheets
Unlike absolute cell references, relative references change when you copy a formula. For relative cell references, you don’t have to do much because once you have written your formula, they are relative by default.
For example, if we wanted to calculate totals in our example table (like with our golf spreadsheet), we would use relative cell references. Here’s how:
- First, click on the cell where you want to enter the formula.
- To start the formula, use the equal sign (=).
- Now, enter the cell address that contains the number of units. In this case, it’s cell A2.
- Add a multiplication symbol (*).
- Finally, type the cell address that contains the unit price. In this case, it’s cell B2.
- Press Enter to execute the formula.
Here’s how to copy a formula in Google Sheets: drag the circle in the bottom right to fill the rest of the cells.
If you click on each cell containing the results, you will see that they use relative cell references by default. This means that each formula has cell references linked to each other in the rows.
Frequently Asked Questions About Absolute References in Google Sheets
What is the shortcut for an absolute reference in Google Sheets?
The shortcut for an absolute cell reference in Windows is F4 in Google Sheets. By pressing this key while the cell is selected, it toggles between absolute reference and relative reference. The shortcut for an absolute reference in Google Sheets for Mac is fn+ F4.
What is an absolute formula in Google Sheets?
In an absolute formula, cell addresses are always kept the same when the formula is copied and pasted to another cell. Absolute references formulas remain constant and keep the cell addresses as they are when moved to another cell instead of adjusting to them. This is sometimes referred to as using a constant cell reference.
How do you keep a cell reference constant in Google Sheets?
To keep a column or row constant in your spreadsheets, you can use an absolute reference. You can use the F4 keyboard shortcut to instantly toggle between an absolute reference and a relative reference. Or you can add a dollar sign ($) before the column or row in the cell reference of a formula.
How to switch between absolute and relative cell references in Google Sheets?
To switch between absolute and relative references, you can use the F4 keyboard shortcut on Windows. On macOS, you can use fn+ F4. By pressing the shortcut key, Google Sheets will fix cells in a formula and also toggle between the three iterations of absolute reference.
What does the $ symbol mean in Google Sheets?
The dollar sign in Google Sheets is used to convert a cell reference into an absolute cell reference. This means that all subsequent formulas are fixed and will use that particular reference instead of moving when you click and drag the formula to other cells.
What is an absolute cell reference?
An absolute reference is a constant cell reference in Google Sheets that can be used to lock the cell address in a formula. The cell reference will not adjust to the new position when copied and pasted to a new cell. This can be done by adding a dollar sign ($) before the column or row in the cell.
Conclusion on Absolute References in Sheets
Knowing how and when to use relative and absolute references can be a real asset. It can save you the hassle of retyping the formula every time you want to copy it to another cell. And it’s also a necessity for creating complex spreadsheets in Google Sheets.
We hope this article has helped you better understand the two types of references in Google Sheets and how to use them. You can also check out our guide on how to track dependencies in Google Sheets.
Crawlan.com is an excellent tool for exploring and optimizing your Google Sheets spreadsheets. Feel free to take a look for even more tips and resources to enhance your productivity with Google Sheets.
