Copying formulas down
7 minutes · 4 steps · free, no sign-up
You almost never type the same formula twenty times. You write it once and copy it. The trick that makes this work is that cell references move with the formula, so each row ends up pointing at its own data. You will fill a formula down a column and then build a running total, which depends on that behaviour.
- Fill a formula down a column
- See how relative references shift row by row
- Build a running total
- Change an input and watch every result update
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceFilling down
Ctrl + D fill the selection down from its top cell
C2: =A2*B2 becomes =A3*B3 one row down
D3: =D2+C3 running total: previous total plus this rowThe key ideasSummary: Copying formulas down
A formula in Sheets does not remember cell addresses as fixed spots. It remembers them as relative positions, such as "the cell two to the left". When you copy =A2*B2 one row down you get =A3*B3, so every row works on its own data.
A running total uses the same idea in a clever way: =D2+C3 means "the total above plus this row's amount". Copied down, each row adds to the one before. And because everything is a formula, changing one price updates the whole column and the total below it.
Watch out forCommon mistakes
- Filling a typed number. If a column holds typed totals, changing a price will not change them. Use a formula and fill it down.
- Starting the running total on the wrong row. The first row of a running total is just the amount itself (
=C2). The addition starts from the second row. - Copying a formula that needs a fixed cell. If every row should use the same cell, for example one tax rate, the reference needs dollar signs. That is the next lesson but one: absolute references.
Next stepTry it in the real Google Sheets
In Google Sheets you can also fill down by dragging the small square at the bottom right of a selected cell (the fill handle), or by double-clicking it to fill as far as the data goes. Ctrl+D fills down and Ctrl+R fills right.