PlayInfoBox

Home /Utilities /How to Use Absolute References in Excel

How to Use Absolute References in Excel

Tap each step to follow along in order.

  1. What is a relative reference?

    By default, when you copy a formula to another cell, the cell addresses it refers to shift along with it.

  2. What is an absolute reference?

    Adding a $ symbol locks a cell reference in place, so it stays the same no matter where the formula is copied.

  3. Lock a reference with the $ symbol

    Add a $ before the column or row (for example, $A$1), or select the cell reference in the formula and press F4 to cycle through it quickly.

  4. Use a mixed reference when needed

    A mixed reference, like $A1, locks only the column or only the row, which can be useful in certain situations.

  5. Why is this useful?

    When a formula needs to always point to the same cell, like a tax rate or exchange rate, an absolute reference lets you copy it across many rows without errors.

When copying a formula gives strange results

If a formula that always needs to point to the same value, like a fixed exchange rate or tax rate, gives odd results after being copied down a column, the most likely cause is that it is using a relative reference instead of an absolute one.

Check out conditional formatting too

Once your formulas are calculating correctly, using conditional formatting to highlight the results that matter most is a natural next step for making a spreadsheet easier to read.

Frequently Asked Questions

What happens when I press F4?

With a cell reference selected inside a formula, pressing F4 quickly cycles through absolute, mixed, and relative reference styles.

Does it always matter if I skip using absolute references?

No. If you are only writing a formula once and not copying it elsewhere, relative and absolute references produce exactly the same result.