My Day✳All blog articles ↗

Keep a shared multiplier fixed when copying a formula

Copy a live formula with its shared cell fixed.

Copying an Excel formula moves relative references. If one multiplier is shared by all rows, fix its column and row with $. Keep the dependency as a live formula.

F1: 3 labels per kit; results: 6 and 12
F1: 3 labels per kit; results: 6 and 12

What to do

  1. Identify the shared multiplier cell and each row’s different input quantity.
  2. Fix both coordinates of the multiplier while leaving the quantity reference relative.
  3. After copying, inspect the new formula’s addresses and both results, not just displayed cells.

Example

Fictionally, F1=3 labels per kit, B2=2 kits, B3=4; F2=5. C2 contains =B2*$F$1 and gives 6. Copied down to C3, it becomes =B3*$F$1 and gives 12. Without fixing, the reference would become F2 and give 20. Model and addresses checked; no real Excel test occurred.

My Day · Windows

Save this note manually in My Day.

Buy My Day for Windows ↗