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.
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.
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 »