Microsoft Excel · Calculation tools, not function references · Intermediate
Copy formulas with relative, absolute and mixed references in Excel
Copy formulas with relative, absolute and mixed references in Excel with a complete practice example, expected results and recovery checks.
Official documentation reviewed; example logic independently checked; native Excel execution not performed · Editorial standards
Quick answer: In a copied formula, A1 moves with both row and column, $A$1 stays fixed, and $A1 or A$1 locks only one direction. Put the dollar sign immediately before the part that must not move, then inspect the copied formula at the far corner of the output.
Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.
Build a two-way calculation grid
In a blank worksheet put Base amount in A1 and enter 100, 200 and 300 in A2:A4. Put the numeric rates 0.05, 0.10 and 0.15 in B1:D1, formatted as percentages. Leave B2:D4 blank. The goal is to multiply each row’s base amount by each column’s rate without typing nine different formulas.
In B2 enter =$A2*B$1. The first reference always uses column A but allows the row to change. The second always uses row 1 but allows the column to change. The formula should produce 5.
Copy in both directions
- Copy B2 and paste into C2:D2, or drag its fill handle across to D2.
- Inspect C2 and D2. They should contain
=$A2*C$1and=$A2*D$1, returning 10 and 15. - Select B2:D2 and copy or fill down through row 4.
- Inspect D4. Its formula should be
=$A4*D$1, returning 45.
The complete expected grid is:
| Base | 5% | 10% | 15% |
|---|---|---|---|
| 100 | 5 | 10 | 15 |
| 200 | 10 | 20 | 30 |
| 300 | 15 | 30 | 45 |
Checking only B2 is insufficient because even an incorrectly anchored formula can be right in its original cell. The copied corner D4 tests both directions at once.
Compare relative and absolute references
For a separate one-rate example, enter 0.08 in F1 and =A2*$F$1 in F2. Copy F2 down to F4. Expect 8, 16 and 24. The A reference changes with each row, while the fully absolute rate remains F1. The last formula must be =A4*$F$1.
When editing a formula in the formula bar, select a reference and press F4 to cycle the reference types. Inspect the actual dollar signs rather than relying on how many times you pressed the key. On a keyboard where function keys perform hardware actions, type the dollar signs directly if needed; this tutorial’s shortcut is the Windows Excel command, not a promise about every keyboard configuration.
Change an input to test the anchors
Change A3 from 200 to 250. The middle row of the grid should become 12.5, 25, 37.5, and F3 should become 20. Rows 2 and 4 must be unchanged. Then change D1 from 0.15 to 0.20. Only the grid’s D column should change, becoming 20, 50, 60. These checks reveal whether the row or column was fixed incorrectly.
If every row repeats the first result, an input row may have been locked unnecessarily. If rates drift down into results or blanks, the header row was not locked. Correct the source formula, copy it again over the output block and compare the entire grid rather than patching individual results.
Save as XLSX, reopen, and inspect formulas as well as values. Dollar signs control reference movement during copying; they do not protect a cell from editing, make data confidential or freeze its calculated value. This is a normal cell-formula exercise, not a structured-reference table workflow.
Official references
Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.