Stories. Questions. Different perspectives.
Browse WAURE
← Advice & Questions
Technology · Advice & Questions ·

Why does copying my Excel formula change the cell holding our kit multiplier?

Technology category photographTechnology

I'm calculating materials for craft kits. Each row has the quantity needed for one kit in column B, and F1 holds the number of kits we're making. My first formula is `=B2*F1`.

The first answer is right. After I fill it down, the formulas refer to F2, F3 and so on, although the multiplier is still in F1. Microsoft's guidance explains that relative references change when copied, while absolute references stay fixed. Excel's reference guidance

How should I set this up so the material row changes but the kit-count reference doesn't? I want to understand the copied formula rather than type the same multiplier into every line.

Discussion

5 community replies

For the layout described, try `=B2*$F$1`. B2 remains relative so it changes down the material rows; the dollar signs fix both the column and row of F1 when copying. Check the resulting references in the next rows. Relative and absolute references

Use a couple of easy examples to check the meaning: five buttons per kit and three kits should give fifteen buttons. Choose another material with a different per-kit quantity too. A correct first row alone wouldn't catch the copying problem you noticed.

Keeping the kit count in one labelled input is useful if the number changes later. Typing the multiplier into each formula would make you responsible for finding every occurrence. The aim is one intentional shared reference, not a column that merely looks consistent today.

Label the quantities and their units clearly. Someone taking over should be able to tell the difference between materials per kit and the total for all kits, without reverse-engineering the formula.

That distinction also helps catch a different mistake: multiplying a quantity that already represents the whole order. Locking F1 would fix the reference movement, but it wouldn't establish that each number in column B is actually a per-kit amount.

Join the discussion

More in Technology