Relative Reference

A relative reference in Excel points to a cell or range address, and it changes when you copy the formula to another cell.

This article is a part of our definitive guide on Excel Formulas.

So take a closer look at it.

For example, a relative reference to cell B1 looks like this:

=B1

Difference between absolute and relative reference

Excel provides three different types of cell references: relative, absolute, and mixed.

It is good to know that all references in Excel formulas are relative.

Relative and absolute references work differently. For example, if you copy a formula to other cells, relative references change based on the current position of rows and columns. However, absolute references remain the same because it operates like a constant.

Formula Example

relative reference excel

In the example shown, the formula in F3 contains two relative references that will change as follows when copied down column F:

=D3 * E3
=D4 * E4
=D5 * E5
=D6 * E6

Switch reference to relative

In our earlier post, we have talked about how the absolute reference works. You can copy the formula without changing the position. It uses the dollar sign ($).

The dollar sign ($) “fixes” cell D3 and cell E3. So, if you copy the formula down, the price and the quantity remain 45 and 4.

convert to relative reference

Steps to convert an absolute reference to a relative:

  1. Use the Formula Bar to edit the formula or apply the F2 shortcut to enter the cell edit mode
  2. Select the value(s) where you want to change the reference
  3. Press the F4 key to remove the dollar sign ($)

Learn more about how the toggle shortcut works.

press f4 to turn the reference to relative

After removing the unnecessary $ sign, the formula will work again.

Thank you for being with us today. We hope that you enjoyed this tutorial. Stay tuned.

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.