Spreadsheets
A Household Records Spreadsheet That Stays Tidy
A spreadsheet is the most useful general tool a household has for keeping records, and also the easiest to ruin. The usual cause is treating the grid as a sheet of paper: a title across the top, a few blank rows for air, merged cells, a total squeezed in halfway down, a note typed into whichever cell was nearest. It looks neat and can no longer be sorted, filtered or summed without breaking something.
The remedy is to decide that each sheet is a table and nothing else.
What is worth tracking
Start with lists that answer questions you are actually asked:
- Accounts and subscriptions. Provider, what it is for, how it is paid, renewal date, how to cancel.
- Appliances and warranties. Item, purchase date, where bought, warranty end, where the receipt is filed.
- Insurance and contracts. Policy reference, what is covered, renewal date, contact route.
- Home maintenance. Date, what was done, who did it, when it is next due.
- Meter readings. Date, meter, reading.
One file with a sheet per list is easier to keep than many files.
The rules of a tidy table
- One header row, in the first row, with a short plain name for each column.
- One row per thing. One appliance, one payment, one reading. Never two.
- One fact per cell. "Paid, but check in March" is two facts; give the second its own notes column.
- No blank rows, no merged cells, no sub-headings inside the data. The program treats a blank row as the end of the table.
- No totals inside the table. A total row gets sorted into the middle the first time you reorder the list.
- Do not use colour as data. If red means overdue, add a status column that says "overdue". Colour cannot be filtered reliably and means nothing to the next person.
Dates, amounts and other troublemakers
Enter dates as real dates, not as text, and display them year first so that nobody has to guess whether the third of April or the fourth of March was meant. Put amounts in one column as plain numbers and the currency, if you have more than one, in a column of its own. Reference numbers that begin with zero or are very long should be stored as text, or the program will quietly drop the zeros or round the digits.
For any column with a fixed set of answers, such as status or category, use data validation: a setting that limits a cell to a list of allowed values, usually shown as a drop-down. It prevents the slow drift into "Paid", "paid", "PAID" and "pd", which a filter treats as four different things.
Summaries live somewhere else
Keep the raw table on one sheet and do the adding-up on another. A simple formula that sums a column by category will do for most households. If you want to slice the same list several ways, by month and by category for instance, a pivot table builds that summary from the raw rows without altering them. Either way, the table stays a pure record, and the summary can be thrown away and rebuilt at any time.
What does not belong in it
Do not keep passwords, card numbers or security answers in a household spreadsheet. A spreadsheet is designed to be opened, shared and copied, which is exactly wrong for secrets. Record that an account exists and where its login is stored, not the login itself.
Keeping it alive
Records decay when updating them is a chore. Pick a fixed moment, such as the day the main bills arrive, and spend five minutes adding rows. Add a "last checked" column to lists that go stale, like subscriptions, so you can see which rows you have not looked at in a year.
Lastly, do not let the records depend on one program. Now and then, save each sheet as CSV, the plain text table format that every spreadsheet program can read, and keep those copies with your backups. A tidy table exports cleanly; a decorated one does not, which is one more reason to keep it plain.
If someone else in the household may one day need the file, add a first sheet called "Read me" with three or four sentences on what each list is for. That small page is often the difference between a record and a puzzle.
Worth keeping: One row per item, one fact per cell, one header row, and totals kept on a separate sheet.