A formula works out a value from other cells. Write it once, copy it down a column, and every line gets its own answer, as long as its references are written right.
C6 = 0.04 One formula filled down the column. Each line divides its own amount by the same total.
Most of what a spreadsheet does comes down to a few formulas: a total, an average, each line’s share of the total. None of them is hard to write once. The work is in writing them so they can be copied, because nobody types the same formula into fifty rows. You write it in the first row and fill it down.
That’s where formulas quietly go wrong. Copy one and its references move with it, which is usually what you want and sometimes isn’t. The first row is right, the rest are wrong, and nothing says so. Below you build a total and a share step by step, then see how references behave when a formula is copied, each with a small sheet where you can make the mistakes yourself.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
A plain reference like B2 is relative: it means “the cell one column to the left, in this
row”. Copy the formula a row down and it reads B3; three rows down, B5. That’s
what lets one formula fill a whole column, each line working on its own amount.
C4 = 0.16 The copy in C4 reads =B4/$B$7. B2 moved down two rows with it, $B$7 stayed put, so Hardware’s 240 is divided by the total.
The total is the other way round: every line divides by the same cell. Left as B7, it moves
too, and each copy divides by whatever sits further down. The first row is right, so the mistake shows
only once you look past it.
C6 = 0.04 The copy in C6 reads =B6/$B$7. The total stays put wherever the formula goes, and the five shares add up to 1.
You don’t type the dollar signs. Put the cursor on the reference in the formula and press F4 (Fn + F4
on most Mac keyboards). Each press moves on to the next form: $B$7, B$7, $B7, then back to B7. The same goes for tax rates, exchange rates and
targets: any formula that mixes “this row” with one fixed cell.
A range is two references, and both move. Copy =AVERAGE(B2:B6) one column to the right and
it becomes =AVERAGE(C2:C6), the next month’s amounts. So a summary row needs writing only
once. The two mistakes here are a range that runs a row too far, into the total, and a range locked
when it should have moved.
D8 = 360 Copied right, B2:B6 became C2:C6, then D2:D6. Each month averages its own five lines, and the range stops above the total.
A SUM or AVERAGE that ignores amounts you can see usually means those cells hold text rather than numbers, often typed with a currency sign or imported from another system. Data cleaning practice covers the fixes.
To carry on in your own Excel, download the workbook: a year of household spending, 72 rows in one long list, with five tasks to work through. One of them is a share column, where the total has to be locked before it’s copied down.
Twelve months of spending across six categories in tidy long format. A gentle beginner set: totals per month with SUMIF, per-category averages, and share-of-total percentages.
72 rows · Month · Category · Amount
Free · fictional data · opens in Excel and Google Sheets
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches Excel formulas: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →