There are two types of cell references in Excel: relative and absolute. These references behave differently when copied to other cells.

* Download our practice document.

  • Relative references change when copied to another cell.
  • Absolute references stay the same, no matter where they are copied.

Relative References

By default, all cell references are relative. This means they adjust based on their position when copied.

For example:
If you copy the formula =A1+B1 from row 1 to row 2, it will change to =A2+B2.

How to use relative references:

  1. Select the cell where you want the formula.

  2. Enter the formula. Example: =B4*C4.

  3. Press Enter to calculate the result.
  4. Locate the fill handle (small square at the bottom-right of the cell).

  5. Click and drag it over the cells where you want to copy the formula.

  6. Release the mouse. The formula will adjust for each row automatically.

Relative references are useful when you need the same calculation in multiple rows or columns.

Absolute References

Sometimes, you may not want a cell reference to change when copied. Absolute references keep a row or column fixed.

To create an absolute reference, add a dollar sign ($) before the column and row. Example: $A$2.


How to use absolute references:

  1. Select the cell where you want the formula.

  2. Enter the formula. Example: =(B4C4)$E$2.

  3. Press Enter to calculate the result.

  4. Locate the fill handle and drag to copy the formula.

  5. The reference to $E$2 will stay the same in every copied formula.

To quickly switch between relative, absolute, and mixed references, press F4 when typing the formula.


Referencing Cells from Another Worksheet

Excel allows you to refer to a cell from a different worksheet.

To reference a cell from another worksheet:

  1. Type =, followed by the worksheet name, an exclamation mark (!), and the cell reference.
    • Example: =Sheet1!A1 (references cell A1 from "Sheet1").
  2. If the worksheet name has spaces, enclose it in single quotes (‘ ’).
    • Example: ='July Budget'!A1.
  3. Press Enter. The value from the other worksheet will appear.

If you rename the worksheet, Excel will update the reference automatically. However, if you enter the worksheet name incorrectly, you will see a #REF! error.

By understanding relative and absolute references, you can use Excel formulas more effectively.