An Excel formula can work perfectly in its first cell and fail as soon as it is copied. The calculation may be correct, but the references move in ways the worksheet’s design did not intend.
Relative, absolute, and mixed references control that movement. The useful question is not simply whether a reference should be locked. It is which part should remain fixed when the formula moves across columns or down rows.
Understand what the dollar sign fixes
A relative reference such as A1 can change both its row and column when copied. An absolute reference, $A$1, fixes both.
Mixed references fix only one component. $A1 keeps column A while allowing the row to change. A$1 keeps row 1 while allowing the column to change.
Microsoft documents these reference types and the use of F4 to cycle between them while editing a reference in supported desktop workflows.
Think of each dollar sign as attaching to the component immediately after it. That makes mixed references easier to read than memorizing four visually similar patterns.
Use an absolute reference for a shared input
Imagine a worksheet where column B contains package weights and cell F1 contains a single conversion factor.
The formula in C2 could be:
=B2*$F$1When copied down, the weight reference changes to B3, B4, and so on. The conversion factor remains F1.
This arrangement also makes the worksheet easier to review. Instead of embedding the same factor in every formula, the workbook has one visible input that can be labeled and checked.
Excel’s documentation describes references as a way to use values from cells, ranges, and other worksheets. While editing a formula, its color-coded reference outlines can help identify which inputs it actually uses.
Use mixed references for a two-dimensional grid
Suppose column A lists quantities, while row 1 lists several unit-cost assumptions. Each interior cell should multiply its row’s quantity by its column’s assumption.
In B2, use:
=$A2*B$1Copying across keeps the quantity in column A while moving to the next assumption. Copying down keeps the assumption in row 1 while moving to the next quantity.
For example, the corresponding formula in C3 becomes:
=$A3*C$1That example reveals why fixing everything would be wrong. $A$2*$B$1 would keep calculating the original intersection instead of following the grid.
Test movement in both directions
Before filling a large range, copy the formula one cell to the right and one cell down. Inspect both results in the formula bar.
Choose test inputs that make mistakes obvious. If every quantity and assumption contains the same number, an incorrect reference may still produce a plausible result. Distinct values make the relationship easier to verify.
Finally, inspect a cell near the far edge of the completed range. Confirm that its references correspond to the correct row and column labels.
Dollar signs should express the worksheet’s intended relationships. Once those relationships are clear, copying formulas becomes predictable, and later reviewers can understand why some references move while others stay fixed.