Reference a cell using formula?

Referencing Cells in Google Sheets Formulas

Learn how to effectively reference cells in Google Sheets formulas, including absolute, relative, and mixed references, to build dynamic and robust spreadsheets.

Referencing cells is a fundamental skill in Google Sheets, allowing you to build formulas that interact with data across your spreadsheet. Understanding the different types of cell references – relative, absolute, and mixed – is crucial for creating dynamic formulas that can be copied and pasted without breaking, or for locking references to specific cells or ranges. This article will guide you through each type, explain their use cases, and provide practical examples.

Understanding Relative References

By default, when you reference a cell in a formula, Google Sheets uses a relative reference. This means that if you copy the formula to another cell, the cell references within the formula will adjust relative to the new position. This behavior is incredibly useful for applying the same calculation logic across a row or column of data.

=A1+B1

A simple relative reference formula

For example, if you enter =A1+B1 in cell C1 and then drag the fill handle down to C2, the formula in C2 will automatically become =A2+B2. This relative adjustment saves a lot of time and effort when dealing with large datasets.

flowchart TD
    A["Formula in C1: =A1+B1"]
    B["Drag fill handle to C2"]
    C["Formula in C2: =A2+B2"]
    A --> B
    B --> C

How relative references adjust when copied

Mastering Absolute References

Sometimes, you need a cell reference to remain fixed, regardless of where the formula is copied. This is where absolute references come in. You create an absolute reference by placing a dollar sign ($) before both the column letter and the row number (e.g., $A$1). This locks the reference to that specific cell.

=$A$1*B1

An absolute reference to cell A1 multiplied by a relative reference to B1

Consider a scenario where cell A1 contains a fixed tax rate, and you want to apply this rate to a column of prices in column B. If you put =$A$1*B1 in cell C1 and drag it down, $A$1 will always refer to the tax rate in A1, while B1 will adjust to B2, B3, and so on.

Utilizing Mixed References

Mixed references offer a hybrid approach, allowing you to lock either the column or the row, but not both. This is particularly useful when creating tables or matrices where you need to fix one dimension while allowing the other to adjust.

There are two types of mixed references:

• A$1: Locks the row (1), but allows the column (A) to change.
• $A1: Locks the column (A), but allows the row (1) to change.

=A$1*B2

Row 1 is locked, column A is relative

=$A1*B2

Column A is locked, row 1 is relative

A common application for mixed references is building a multiplication table. If you have numbers in row 1 and column A, you can use a single formula with mixed references to fill the entire table correctly.

flowchart LR
    A["Formula in B2: =$A2*B$1"]
    B["Copy across row"]
    C["Copy down column"]
    D["Result: Correct multiplication table"]
    A --> B
    A --> C
    B --> D
    C --> D

Mixed references for a multiplication table