Write and copy formulas

Choose relative, absolute or mixed references so a formula stays correct after being copied down, across, or both, and diagnose a copied column that produces plausible but incorrect numbers.

Copying a formula is the moment a spreadsheet stops being a calculator and starts being a model. It is also the moment the most expensive mistakes are made, because a formula copied wrongly usually still produces numbers, and those numbers usually still look reasonable.

A total that reads #VALUE! gets fixed the same morning. A cost column where rows 7 through 40 quietly multiplied by an empty cell reads as a column of zeros, or worse, of small plausible amounts. It gets fixed after somebody has already used it.

The whole defence is one decision made once per reference: when this formula moves, should this part of it move too?

A formula is evaluated in a fixed order, not left to right. Powers first, then multiplication and division, then addition and subtraction, then comparisons. Brackets override all of it. =2+3*4 is 14, not 20, and no amount of reading it aloud changes that.

The part that matters more is what a reference means.

A relative reference is a direction, not an address. When cell E2 holds =C2*4.25, the sheet does not remember "C2". It remembers "two columns to my left, same row". Copy that formula to E3 and it still means two columns to the left, same row, which is now C3. That is exactly what you want for a column where each row does its own arithmetic.

An absolute reference is an address. $I$2 means cell I2 and keeps meaning cell I2 wherever the formula is copied. The dollar signs are not decoration: the first anchors the column, the second anchors the row.

A mixed reference anchors one axis and lets the other move. $A2 always uses column A but takes its row from wherever the formula sits. B$1 always uses row 1 but takes its column from wherever the formula sits. This is the only way to fill a two-way block from a single formula, and it is the case most people never learn, because copying down alone never requires it.

Deciding is a two-question routine:

1. When I copy this formula down, should this reference move to the next row? If no, put a dollar sign before the row number.
2. When I copy it across, should this reference move to the next column? If no, put a dollar sign before the column letter.

Answer those two questions for each reference in the formula and the anchoring writes itself.

One more thing that trips people up constantly: a percentage format changes what a cell shows, not what it holds. A cell displaying 18.2% holds about 0.182 — in the exercise below it holds 0.1818181818, and the display is rounded to one decimal place while the stored value is not. If you compute a share and then multiply it by 100 as well as formatting it as a percentage, you get 1818.2%. Percentage points and percentages are two different quantities, and mixing them is the single most common way a correct calculation is reported wrongly.

Cedar Ridge charges a rate per learner per session. The rate lives in one cell, I2, and the cost column is built from it.

        C          E
1    Learners     Cost
2       18        =C2*$I$2
3       12        =C3*$I$2
4       21        =C4*$I$2

Read E2 aloud as the sheet reads it: "the cell two columns to my left, times cell I2". Copy it down and only the first half moves. E3 becomes =C3*$I$2, E4 becomes =C4*$I$2, and the rate stays put.

Now the version without the dollar signs:

2       18        =C2*I2
3       12        =C3*I3
4       21        =C4*I4

Row 2 is correct by accident. Row 3 multiplies twelve learners by whatever happens to be in I3, and I3 is empty, so the cost of that session is recorded as zero. Nothing is highlighted. Nothing errors. The column simply becomes wrong from the second row down.

Cell B2 of a small block holds =$A2*B$1, where column A holds learner counts down the page and row 1 holds rates across it.

        A       B       C       D
1    (label)   4.00    4.25    4.50
2      10
3      15
4      20

Before you read on, write down what C3 and D4 become once B2 is copied across to D2 and down to row 4, and what value each one produces.

A share-of-total column starts out like this, with the total sitting in E9:

=E2/E9

It is right in row 2 and useless below it: copied to row 3 it becomes =E3/E10, which divides by an empty cell and returns #DIV/0!. At least that failure is loud.

Anchor the denominator:

=E2/$E$9

Now every row divides by the same total. Add the column up and it should come to exactly 100 percent; if it does not, a row is missing from the total or is being counted twice.

Then resist one specific temptation. Having formatted the column as a percentage, do not also write:

=E2/$E$9*100

That cell holds 18.18 and displays 1818.2%. The format has already done the multiplication for display purposes. Choose one or the other, never both.

A colleague sends a workbook. The cost column looks fine at the top and the total is far too low. They have already tried retyping row 7 by hand, which fixed row 7 and nothing else.

Diagnose it in this order.

1. Click a wrong cell and read its formula, not its value. The value tells you there is a problem; only the formula tells you what kind.
2. Compare two rows. If row 2 says =C2*I2 and row 7 says =C7*I7, the second half moved when it should not have. That is a relative reference doing exactly what it was told.
3. Look at where it landed. I7 is empty. An empty cell used in multiplication behaves as zero, which is why the answer is a plausible-looking 0 rather than an error.
4. Fix the source, then re-copy. Correct the formula in the first row of the block, anchor the reference that should not have moved, and copy it down again. Do not repair the rows one at a time: you will fix the eight you can see and leave the ones below the fold.
5. Prove the fix. Change the rate cell and watch every row move. A column that does not respond to its own input cell is not fixed; it has been overwritten with numbers.

The general shape: when a copied formula is wrong, the mistake is in the anchoring, not in the arithmetic. Retyping a value hides the mistake instead of removing it.

Two blocks, two different anchoring problems. The Costs sheet needs a column copied down; the Projection sheet needs a block copied down and across at once.

Column F is already formatted as a percentage, so store the share as a fraction and let the format display it.

Take a workbook you already have, or the cost table from this lesson, and give yourself a five-minute audit.

1. Find every formula containing a number typed directly into it — a rate, a tax fraction, a headcount. For each one, decide whether it belongs in an input cell instead. Most do.
2. Pick the longest formula on the sheet and read each reference aloud with the two questions: does it move down, does it move across.
3. Change one input cell to an obviously wrong value, such as doubling it. Watch which cells move. Any output that does not move, and should have, is a typed-in number wearing a formula's clothes.
4. Change it back.

Step 3 is the one worth keeping. It takes ten seconds and finds a class of error that reading never does.

Sign in to track your progress on this exercise.

An assistant is genuinely good at one thing here and unreliable at another.

Useful: paste a formula and ask what it does, reference by reference. "Explain =$A2*B$1 and say what it becomes if I copy it two columns right and one row down" gets a clear, usually correct answer, and it is the kind of answer you can check in ten seconds by doing the copy yourself.

Unreliable: describing your workbook. An assistant cannot see your sheet. Asked "why is my total wrong?", it will produce a confident list of plausible causes, and there is no way to tell from the answer whether any of them is yours.

So bring it the formula, not the symptom. And check what it says the way this lesson checks everything else: copy the formula, look at what the reference became, and see whether the number moves when the input moves.

You are ready for the next lesson when you can answer these without opening a sheet.

• Give the value of =2+3*4 and of =(2+3)*4, and say which one a reader is likelier to assume.
• Explain what $ does to a reference, and what the difference is between $B4, B$4 and $B$4.
• Describe the two questions to ask about a reference before copying a formula.
• Say what a cell displaying 12% actually holds, and what goes wrong if you multiply it by 100 as well.
• Describe the ten-second test that finds a typed-in number pretending to be a calculation.

Sign in to track your progress on this exercise.

A reference is either a direction or an address, and copying is what reveals which one you chose. Anchor what should not move, leave free what should, and prove the result by changing an input and watching the outputs follow.

Next: Summarize and check. You now have columns worth totalling. The next lesson is about totals that can be checked, and about the difference between a cell that is empty, a cell that holds zero, and a cell whose value nobody ever recorded.