Excel Cell References: Relative, Absolute, and Mixed

Cell references tell Excel where to find the values used in a formula. When you copy a formula, a relative reference changes, an absolute reference stays fixed, and a mixed reference locks either the row or the column.

Understanding this difference helps you copy formulas correctly instead of fixing them one cell at a time.

Cell reference quick guide

What is a cell reference in Excel?

A cell reference is the address of a cell, such as B4 or F2. In a formula, it tells Excel which value to use.

For example, this formula multiplies the value in B4 by the value in C4:

=B4*C4

If you copy that formula down one row, Excel changes it to =B5*C5. That is usually useful because each row uses its own values.

Relative references: A1

A relative reference changes when you copy a formula. It is the default reference type in Excel.

Suppose column B contains sales and column C contains a percentage. In D4, you enter:

=B4*C4

When you fill the formula down to D5, Excel changes it to:

=B5*C5

Both references move down one row because neither the row nor the column is locked.

Absolute references: $A$1

An absolute reference keeps both the column and the row fixed. Add a dollar sign before the column letter and before the row number.

For example, imagine this commission worksheet:

If the standard commission rate is 7.5% in F5, enter this formula in C6:

=B6*$F$5

Then fill the formula down. The sales reference changes for each person, but the commission rate continues to point to F5:

C6: =B6*$F$5
C7: =B7*$F$5
C8: =B8*$F$5

Without the dollar signs, the rate reference would move to F6, then F7, and so on. That usually produces incorrect results because the rate is stored in one fixed cell.

Mixed references: A$1 and $A1

A mixed reference locks only one part of the address:

• A$1 locks row 1 but lets the column change.
• $A1 locks column A but lets the row change.

Mixed references are useful when you copy a formula both across columns and down rows.

For example, place multipliers 1, 2, and 3 in A20:A22. Place 10, 20, and 30 across B19:D19. In B20, enter:

=$A20*B$19

Now copy the formula across and down. In the copied formulas:

• $A20 always uses column A, but its row changes as you fill down.
• B$19 always uses row 19, but its column changes as you fill across.

This creates a multiplication grid without rewriting formulas for every cell.

How to use the $ sign in a formula

Type a dollar sign directly before the part of the reference you want to lock:

• Use $A$1 to lock both the column and row.
• Use A$1 to lock only the row.
• Use $A1 to lock only the column.

In supported Windows desktop versions of Excel, you can select a reference in the formula bar and press F4 to cycle through the reference types. Keyboard behavior can differ by platform and device, so the dollar-sign method works everywhere.

For a focused, step-by-step example of locking one rate or setting while copying a formula, read How to Lock a Cell in Excel with $ and F4.

Common cell-reference mistakes

Locking every reference

Absolute references are useful only when a value should stay fixed. If you write =$B$6*$C$6 and fill it down, every row will use the values from row 6.

Forgetting to lock a fixed rate or setting

If a tax rate, discount rate, target, or other setting is stored in one cell, use an absolute reference for that cell before copying the formula.

Locking the wrong part of a mixed reference

Read the reference from left to right: the first dollar sign controls the column, and the second controls the row. For example, A$1 locks the row, while $A1 locks the column.

Not checking the copied formula

After filling a formula, select one of the copied cells and inspect the formula bar. This is the quickest way to confirm that the references changed exactly as intended.

If a formula still does not change or returns an unexpected result after you check its references, see our guide to fixing Excel formulas that are not updating.

When should you use each type?

• Use relative references when each copied formula should use values from its own row or column.
• Use absolute references for a single rate, setting, lookup cell, or other fixed input.
• Use mixed references for grids and other formulas copied in two directions.

Once you understand references, formulas such as IF, SUMIFS, COUNTIFS, and XLOOKUP become much easier to build and troubleshoot.

Source and further reading

For Microsoft’s current guidance on relative, absolute, and mixed references, see Switch between relative, absolute, and mixed references.

To learn how The Excel Guide researches, tests, and updates its tutorials, read our Editorial Policy.