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:
Difference between absolute and relative reference
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.
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.
Steps to convert an absolute reference to a relative:
- Use the Formula Bar to edit the formula or apply the F2 shortcut to enter the cell edit mode
- Select the value(s) where you want to change the reference
- Press the F4 key to remove the dollar sign ($)
Learn more on how the toggle shortcut works.
After removing the unnecessary $ sign, the formula will work again.
Thank you for being us today. We hope that you enjoyed this tutorial. Stay tuned.