Google Sheets fixed cell formula

For example, if you are in cell A1 and the formula in that cell references B1, then if you copy-paste this formula into cell A2, the formula actually pasted in the cell references B2. This is generally what we want.

However, if you are in cell A1 and the formula in that cell now says $B$1 (it will still reference the cell B1, as before), but
then if you copy-paste this formula into cell A2, the formula actually pasted in the cell will still say $A$11 (and still reference the cell B1).

You can also use "mixed" references, where only the column or row is static, and the other is a dynamic reference. For example $A$13 or $A$14. This link gives a good example and use case (creating multiplication tables) for mixed references, as well as static vs relative references, and warns to always use static references with $A$15 and arrays: Relative and Absolute References in Google Sheets and Excel.

Knowing about cell references in Google Sheets is crucial as it allows you to execute formulas properly across several cells. Often we want one part of the formula to adapt to the cell when copy-pasted but keep some cell values unchanged. This is where the concept of absolute references comes in handy.

This article discusses the differences between absolute and relative reference, what Google Sheets absolute reference is, and how you can use it in your spreadsheets. Read on to master this aspect of Google Sheets in as little as a few minutes.

Table of Contents

Types of Cell References in Sheets

There are essentially 2 types of cell references in Sheets. These are relative and absolute references. They effectively change the way data behaves when copied and transferred from one cell to another. Relative reference changes when the formula is copied from one cell. However, no change is made to the data when copied from one cell to the other when using an absolute reference.

All cells have a relative reference by default. Then data is copied across cells and the data changes based on its position in the rows and columns. For example, assume that there is a formula in cell A1 that is =B1+C1. Now we copy over that formula to cell A2. Instead of the formula keeping the same cell references, it will adapt the references in relation to their new position. So now the formula will change to =B2+C2

An absolute reference in Google Sheets is the total opposite of that. Instead of changing the reference in relation to its new position, the copied formula will keep the same values. Take the same example as before. We have a formula in cell A1 that is =B1+C1. When we copy the formula over to cell A2, the formula will keep the row and column consistent, and the formula will remain as =B1+C1. Absolute references are not the same as absolute values in Google Sheets.

How to Use Absolute Cell Reference in Google Sheets

You don’t need to make any changes to use relative reference as it is enabled by default in Sheets. However, you will need to follow a specific format to use absolute reference. All you need to do to turn a relative reference into an absolute one is add a Dollar symbol ($) before the row or the column.

You can designate an absolute reference to a row or 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 ensure the column stays unchanged but the row remains relative, we write it as $A2. Writing the cell address as $A$1 will make it so that both column and row won’t change when copied or auto-filled.

An Example of Absolute Reference in Sheets

In this example, we have a spreadsheet that contains the data for sales in a store. The first row contains the units sold in the purchase, while the second row contains the price per unit. The third row contains the total price of the units sold. This is calculated by multiplying the number of units with the price per unit. As you will see, this uses relative reference.

Here are the steps you need to follow to do this:

1. First, click on the cell where we wish to input the formula.
2. To start off the formula, use an Equal to (=) symbol.
3. Now let’s enter the cell address for the cell containing the number of units. In this case, it is cell A2.
4. Now we add a Multiplication (*) symbol.
5. Finally, type in the cell address for the cell containing the unit cost. In this case, it is cell B2.
6. Press Enter to execute the formula.

If the same formula is copied to the other cells below cell C2, they will use relative reference and change the cell address according to their location. Let’s look at an example and copy the formula into cell C7.

As you can see, the formula intelligently changes the address according to the location.

Most of the formula uses a part of the formula we discussed before. However, now to demonstrate absolute reference, let’s say that each online purchase of the product has a fixed $5 shipping fee. 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 be using the Google Sheets absolute cell reference to do this.

Here are the steps you need to follow to do this:

1. Click on the cell where you wish to enter the formula.
2. Type in the Equal to (=) symbol to start off the formula.
3. Enter a starting bracket to nest the first equation.
4. Enter the cell address for the cell containing the unit number, cell A2 in this case.
5. Add a Multiplication (*) symbol.
6. Enter the address for the cell containing the price per unit, which is B2 in this example.
7. Add a closing bracket.
8. Write the Plus (+) symbol.
9. To enter the cell address as an absolute reference, we add a Dollar ($) symbol before the row and the column. It will be written as $G$1 in this case.
10. Press Enter to execute the formula.

To test whether we have applied the absolute reference correctly, we use the Suggested autofill feature to fill in the formula automatically. As you can see, all the values in the cells have the $5 shipment fee added, which means that we executed the steps effectively.

Shortcuts for Absolute Reference in Google Sheets

Although adding the dollar sign to the cell reference is pretty quick, there is a much easier way to apply absolute references. You can select the cell for the absolute reference on windows and use F4 in Google Sheets. The Google Sheets absolute reference shortcut for mac is fn+ F4 instead.

Pressing the key while the selected cell will alternate between absolute reference and relative reference. Once in absolute reference, pressing the shortcut will turn it into absolute row reference, and pressing it again will turn it into absolute column reference.

Google Sheets Absolute Reference Frequently Asked Questions

What Is the Shortcut for Absolute Reference in Google Sheets?

The absolute cell reference shortcut for Windows is F4 in Google Sheets. Pressing the key while the selected cell will alternate between absolute reference and relative reference. The Google Sheets absolute reference shortcut for mac is fn+ F4.

What Is an Absolute Formula Google Sheets?

In an absolute formula, the cell addresses are always kept the same when the formula is copied and pasted into a different cell. Absolute reference formulas always stay constant and carry over the cell addressed when moved to a different cell rather than adapting to it. This is sometimes referred to as using a constant cell reference.

How Do You Keep a Cell Reference Constant in Google Sheets?

You can use an absolute reference to keep a column or a row constant in your spreadsheets. You can use the F4 keyboard shortcut to instantly switch between absolute reference and relative reference. You can add a Dollar ($) symbol before the column or row in the cell reference in a formula.

How Do I Toggle Between Absolute and Relative References in Google Sheets?

To toggle between absolute and relative references, you can use the F4 keyboard shortcut in Windows. On macOS, you can use fn+ F4. Pressing the shortcut key will decide whether Google Sheets fixes the cells in a formula and also toggles between the three iterations of absolute reference.

What Is an Absolute Cell Reference?

Absolute reference can be used to fix the cell address in a formula. The cell reference won’t adapt to the new location when it is copy-pasted into a new cell. This can be done by adding a Dollar ($) symbol before the column or row in the cell.

Wrapping Up Absolute Reference in Sheets

Knowing how and when to use relative and absolute references can be a lifesaver. It can save you from the hassle of retyping the formula every time you wish to copy it to another cell. It is also a necessity for making complex spreadsheets in Google Sheets.

We hope this article helped you better understand the two types of references in Google Sheets and how you can use them.

Most Popular Posts

Chris Daniel

Chris is a spreadsheet expert and content writer. He has a double Bachelor's Degree in Teaching and has been working in the education industry for over 11 years. His experience makes him adept at breaking down complex topics so that everyone is able to understand.

How do you keep a cell constant in a formula in Google Sheets?

How do I create a fixed cell in Google Sheets?

How to do F4 on Google Sheets?

How do you keep a cell fixed in a formula?