# Cell References

Course: [Excel](https://www.devtutorials.ai/learn/excel) · Section: Formulas and Functions
Source: https://www.devtutorials.ai/learn/excel/formulas-and-functions/cell-references

> Relative, absolute, and mixed references, copying formulas, and how references update when the sheet changes.

One of Excel's biggest advantages is that you can write a formula once and copy it to many cells. Excel automatically adjusts cell references when a formula is copied, helping you save time and avoid repetitive work. In this lesson, you'll learn about relative, absolute, and mixed references, and how to control whether references change or stay fixed when formulas are copied.

Open the **Excel sandbox** and follow along.

## Relative References

A standard cell reference such as `B2` is called a **relative reference**.

A relative reference changes based on the formula's new location when copied.

For example:

If **D2** contains: =B2*C2

and you copy the formula to **D3**, Excel automatically changes it to: =B3*C3

If you copy it to **D4**, it becomes: =B4*C4

Each copied formula works with the values from its own row.

Relative references are the most commonly used type of reference because they automatically adjust when formulas are copied.

## Absolute References

Sometimes a formula must always refer to a specific cell, such as a tax rate, discount rate, or constant value. In this situation, use an **absolute reference**.

An absolute reference uses dollar signs (`$`) to lock both the column and row.

Example: $F$1

If **E2** contains: =D2*$F$1

and the formula is copied down the column, Excel changes only the relative reference:

=D3*$F$1

=D4*$F$1

The reference `$F$1` always remains fixed.

While entering a formula, place the cursor on a cell reference and press **F4** to cycle through the available reference types automatically.

## Mixed References

A **mixed reference** locks either the row or the column, but not both.

| Reference | Column | Row | Behavior When Copied |
|------------|------------|------------|------------|
| `B2` | Changes | Changes | Both row and column adjust |
| `$B$2` | Fixed | Fixed | Never changes |
| `$B2` | Fixed | Changes | Column stays B, row changes |
| `B$2` | Changes | Fixed | Row stays 2, column changes |

Mixed references are particularly useful when formulas are copied both across columns and down rows, such as in multiplication tables or calculation grids.

## Copying Formulas

You can copy formulas using several methods:

### Copy and Paste

1. Select the formula cell.
2. Press **Ctrl + C**.
3. Select the destination cells.
4. Press **Ctrl + V**.

### Fill Handle

The **fill handle** is the small square in the bottom-right corner of a selected cell.

To use it:

1. Select the formula cell.
2. Point to the fill handle.
3. Drag it downward, upward, left, or right.

You can also double-click the fill handle to automatically fill formulas down adjacent rows containing data.

Before copying a formula, decide whether each reference should move or stay fixed. Use relative references for values that should change and absolute references for values that should remain constant.

## Formula Updates

Excel automatically updates references when worksheet changes occur.

### Inserting Rows or Columns

If you insert rows or columns referenced by a formula, Excel updates the references automatically.

Example:

If a formula references: =SUM(B2:B10)

and you insert a row within that range, Excel expands the formula to include the new row.

### Moving Referenced Cells

When referenced cells are moved using cut and paste, Excel updates the formula to follow the moved data.

### Deleting Referenced Cells

If a referenced cell, row, or column is deleted, Excel cannot find the required data and displays: #REF!

This indicates an invalid reference.

The `#REF!` error appears when a formula refers to a cell that no longer exists. If this happens accidentally, press **Ctrl + Z** immediately to undo the deletion.

## Common mistakes

| Mistake | Result | Solution |
|----------|----------|----------|
| Copying `=D2*F1` down a column | References change to F2, F3, and F4 | Use `=D2*$F$1` |
| Locking every reference with `$` | Every row returns the same result | Lock only the references that should stay fixed |
| Deleting a referenced cell | `#REF!` error appears | Undo the deletion or correct the formula |
| Locking the wrong row or column | The formula behaves incorrectly when copied | Use **F4** to choose the correct reference type |
| Entering a constant directly into formulas | Updates require editing every formula | Store the constant in a separate cell |

## Exercise

Click **Reset** in the sandbox header and enter the following data:

- **A1:** `Item`
- **B1:** `Price`
- **C1:** `With tax`
- **E1:** `Tax rate`
- **F1:** `0.1`
- **A2:** `Lamp`, **B2:** `40`
- **A3:** `Desk`, **B3:** `150`
- **A4:** `Chair`, **B4:** `60`

Then complete each task and click **Check**.

## What to Remember

- **Relative references** (`B2`) change when a formula is copied.
- **Absolute references** (`$F$1`) remain fixed when copied.
- **Mixed references** (`$B2` and `B$2`) lock either the column or the row.
- Press **F4** to switch between relative, absolute, and mixed references.
- Use the **Fill Handle** to copy formulas quickly.
- Excel automatically updates references when rows and columns are inserted or moved.
- Deleting a referenced cell causes a **`#REF!`** error.
- Store constants such as tax rates in separate cells and use absolute references to refer to them.

In the next lesson, you'll learn how to use **AutoFill** to quickly create sequences of numbers, dates, and repeating patterns.