How to use Absolute Reference in Google Sheets

- Written by Puneet

In simple words, an absolute cell reference in Google Sheets is a way to keep a cell reference fixed when copying a formula to other cells.

That means the cell reference will always point to the same cell no matter where you copy the formula. You create an absolute cell reference by adding a dollar sign (\$) before the column letter and row number, like \$A\$1.

In the above example, the formula in cell B1 is =A1*\$D\$1. Here, \$D\$1 is an absolute cell reference. The dollar signs (\$) before the column letter D and row number 1 ensure that this reference always points to cell D1, no matter where the formula is copied.

Cell D1 contains the number 100. The formula in cell B1 multiplies the value in cell A1 (151) by the value in cell D1. When this formula is copied down column B, each cell in column B multiplies the corresponding value in column A by the fixed value in cell D1 because we have used the absolute cell reference.

This way, while the reference to column A changes (A2, A3, etc.), the reference to D1 stays fixed (absolute), ensuring consistent multiplication by 100 for each row.

Understanding Absolute References

As I said, in Google Sheets, the dollar sign (\$) is used in cell references to create absolute references. It ensures that the specified column, row, or both remain constant when you copy the formula to other cells.

• \$A\$1: Both column (A) and the row (1) are fixed. No matter where you copy the formula, it will always refer to cell A1.
• A\$1: Only row (1) is fixed. When you copy the formula horizontally (across columns), the row remains the same, but the column changes (e.g., B\$1, C\$1).
• \$A1: Only column (A) is fixed. When you copy the formula vertically (across rows), the column remains the same, but the row changes (e.g., \$A2, \$A3).

Using a dollar sign lets you control which part of the cell reference stays fixed, allowing for flexible and accurate formula copying.

Alert: Always check where you have added the dollar sign when using a semi-absolute reference. One of the most common mistakes users make is fixing the row instead of the column or vice versa.

Keyboard Shortcut to Apply Absolute Reference in Google Sheets

To quickly apply an absolute reference in a formula using the keyboard, you can use the F4 key.

Select the cell where you are writing your formula. Type your formula and select the cell reference you want to make absolute. Press F4. Each press of F4 will cycle through the different reference types:

• First press: \$A\$1 (both column and row absolute)
• Second press: A\$1 (row absolute)
• Third press: \$A1 (column absolute)
• Fourth press: A1 (relative reference)

Using the F4 key from the keyboard is a quick way to toggle between the different types of references without having to type the dollar signs manually.

Note – Combining absolute references with named ranges allows you to manage data more efficiently and reduce errors in large workbooks. If you have a dataset in Sheet1 and want to refer to a specific cell or range in multiple other sheets, you can create a named range with an absolute reference.

What is Relative Reference in Google Sheets

Relative references change based on where you copy the formula. For example, copying a formula in cell B1 as =A1 to cell B2 will automatically change to =A2. It makes applying the same calculation to different rows or columns easy without manually adjusting the formula.