Home › Tutorials › Excel Tutorial › Relative and Absolute References
Relative and Absolute References

Relative and Absolute References


Relative vs. Absolute References

Mastering the Art of Copying Formulas

In Excel, when you copy a formula from one cell to another, Excel automatically "adjusts" the cell addresses. This is usually helpful, but sometimes you need to "lock" a specific cell in place.

1. Relative Reference (A1)

The default setting. When you drag the formula down, the row number increases (A1 becomes A2, A3, etc.).

Best for: Calculating row-by-row items (like Quantity × Price).

2. Absolute Reference ($A$1)

The "Locked" setting. The dollar signs act as locks. No matter where you copy the formula, it will always point to that exact cell.

Best for: Using a fixed Tax Rate, Discount, or Target value.

The "Tax Rate" Scenario

Imagine you have 100 products in Column A and one Tax Rate (5%) in cell D1.

=A2 * $D$1

When you drag this down, A2 will change to A3, A4... but $D$1 will never change. Without those dollar signs, Excel would look at D2, D3, and D4, which are likely empty!

⌨️

Skill Eco Pro-Tip: The F4 Shortcut

You don't have to type the dollar signs manually! While typing your formula, click a cell reference and press the F4 key on your keyboard. Excel will automatically cycle through the dollar sign combinations for you.

Visual Trick:

Think of the $ symbol as a Fixed Pole.
• $A$1: The Column AND Row are locked.
• A1: Everything is free to move.

🏋️ Test Yourself With Exercises

Take our quiz on Relative and Absolute References to test your knowledge.

Exercise »