Excel Absolute, Relative, and Mixed References: Keep Formulas from Breaking with $ and F4

Locking Excel cells is one of the most common mistakes beginners make. This guide explains Excel absolute and relative references with reproducible tables and formulas. Learn the F4 cycle, plus SUMIF, XLOOKUP, and chart references all in one place.

Quick Fix: The F4 Cycle

1. It changes in this order: A1 (relative) → $A$1 (absolute) → A$1 (row locked) → $A1 (column locked).
2. As a basic rule, lock rows when copying across and lock columns when copying down.

Key Concepts

• Understand the differences among relative references (A1), absolute references ($A$1), and mixed references (A$1/$A1).
• Table structured references are the safest option when columns are inserted.

Practical Examples

Locking Unit Prices and Tax Rates

=XLOOKUP([@Item], PriceList[Item], PriceList[Price], "Not found")

=[@[Amount]] * $C$1

Mixed Reference (Copying Across)

=$M$2 * N$2

Locking SUMIF Ranges

=SUMIF(Sales!$B:$B, B$1, Sales!$D:$D)

XLOOKUP Locking Strategy

=XLOOKUP($A5, $B$2:$B$100, C$2:C$100)

Charts, Spills, and Defined Names

Use J2# for spilled ranges, and define static ranges such as $A$1:$A$100 as names.

Alternatives, Notes, and Checklist

• Use tables first; use $ to adjust standard ranges.
• For SUMIF/COUNTIF, keep range pairs the same size.
• Copying across = lock rows; copying down = lock columns.

Troubleshooting

Conclusion

Using $ and F4 correctly makes formulas more reliable. The next article covers how to use SUMIF/COUNTIF correctly.