Sheets — a budget, step by step

Build a monthly budget that adds up your income, takes off your expenses, and splits what is left between two savings accounts — where one number decides how the split falls. By the end you will have used every kind of reference Sheets has, and know how to see what it actually calculated when something looks wrong.

1 · The idea in one paragraph

A sheet is a grid, and on the grid sit panels — separate blocks of columns, each with its own headings and its own calculations. A panel is what other spreadsheets call “a table”, except a sheet may hold as many as you like, side by side. A formula belongs to a column, not to a cell: write it once at the top and every row of that column runs it. That is why the formulas read like English rather than like =D4*$B$1.

2 · The rate that controls everything

Start at the top of the empty sheet. In A3 type Emergency share, and in B3 type 0.6000. That is the number the whole budget turns on: sixty per cent of whatever is left over goes to the emergency fund.

Now make B3 a shared rate so formulas can call it by name. A shared rate is just a cell you have given a name to — it is still an ordinary cell you can type in.

$$emergency_share   →  B3

3 · Income

Add a panel (☰ › Panels › Add a panel…) called Income, at column 0 row 6, with headings Source, Amount and 4 rows. Type your income in:

Source        Amount
Salary          4200
Freelance        650
Interest          35

Click the Amount heading. In the dialog set Totals row to SUM($amount), and under Appearance set the number format to Money. The column now reads $4,200.00 and a total of $4,885.00 appears under it.

4 · Expenses

Another panel, Expenses, at column 4 row 6 — beside the income panel, not below it — with headings Item, Cost and 6 rows:

Item               Cost
Rent               1450
Groceries           620
Utilities           240
Transport           180
Insurance           155
Everything else     400

Give Cost the same treatment: SUM($cost) as its total, Money as its format. That comes to $3,045.00.

5 · What is left over

A third panel, Surplus, at column 0 row 15, with 1 row and three columns: Income, Expenses, Surplus. Click each heading in turn and give it a formula:

$income     =  SUM(B8:B11)
$expenses   =  SUM(F8:F13)
$surplus    =  $income - $expenses

The first two use cell ranges, because they reach into another panel's cells: B8:B11 is the income amounts, F8:F13 the costs. The third uses $income and $expenses — other columns of this panel, on this row. Set all three to Money and you have $4,885.00 − $3,045.00 = $1,840.00.

6 · Splitting the surplus

The last panel, Savings split, at column 0 row 20, 1 row, four columns:

$emergency_rate  =  $$emergency_share
$to_emergency    =  $emergency_rate * $surplus[summary]
$long_term_rate  =  1 - $$emergency_share
$to_long_term    =  $long_term_rate * $surplus[summary]

Read those four lines carefully, because they are the whole point of the exercise:

• $$emergency_share is the rate you typed in B3 — the one number.
• 1 - $$emergency_share is the other share, derived from it. There is no second rate to keep in step, so the two shares can never fail to add up to 100%.
• $surplus[summary] reaches into the Surplus panel for the amount to divide. This is the third kind of reference, and nothing without panels can express it.

Set the two rate columns to Number, 4 places and the two amount columns to Money. The result:

Emergency rate   To emergency   Long term rate   To long term
        0.6000      $1,104.00           0.4000        $736.00

$1,104.00 + $736.00 = $1,840.00. Every cent of the surplus is allocated, and nothing was typed twice.

7 · Now change one number

Go back to B3 and type 0.2500. Press Enter.

Emergency rate   To emergency   Long term rate   To long term
        0.2500        $460.00           0.7500      $1,380.00

Both accounts moved, the total is still $1,840.00, and you edited one cell. Change a grocery bill and watch it travel the other way: expenses → surplus → both accounts. Sheets works out for itself which columns depend on which and calculates them in that order, so you never have to think about it.

8 · When it does not do what you expected

The fastest way to see what a sheet actually calculated — rather than what you meant — is to print it:

webgen-sheets --show ~/monthly-budget.wgsheet

That lists every panel, every value, and each column's formula beside its name, which is usually where the problem is hiding. The file itself is plain JSON and worth opening in Edit: the data is a simple grid of text, and the panels sit beside it.

What the error values mean

Three things that catch people out

• A formula runs on every row of its column. There is no way to give row 1 one formula and row 2 another. That is why the savings split is one row with four columns rather than two rows — if you need genuinely different rules, use two panels.
• Names come from headings. Two columns headed Emergency % and Emergency $ both reduce to $emergency, so the second becomes $emergency_2 — which works, but reads badly. Give columns headings that differ in a word, not a symbol.
• Renaming a heading re-points the formulas that used it, automatically. That is deliberate: the alternative is a rename that looks cosmetic and silently turns a working sheet into #REF.

9 · Where to go next

• ☰ › Open the payroll forecast — the same ideas at full size: one costing chain run four times over the same staff, with hours and margin varied, laid out 2×2 on one sheet. It is the reason panels exist.
• Export to Word… carries the styling, not just the data — alignment, bold, background colours and money formatting all cross over.
• Export as CSV… writes each panel in turn.