A one-page job tracker in a spreadsheet: the columns that matter, the statuses, and a weekly review

Most small businesses already track their work, just not in one place: half in a notebook, some in text messages, a few jobs in the owner's head. On a busy week, a quote goes unanswered or an invoice goes unsent, and nobody notices until a customer calls.

A single spreadsheet fixes most of that. Not a template with forty columns, but one sheet, one row per job or order, with a short list of statuses and a fifteen-minute review once a week. This note sets one up, using features that Google Sheets and Microsoft Excel both have, and explains the few rules that keep it useful after the first month.

One row per job, one sheet for everything

The rule that makes a tracker work is boring: every job or order gets exactly one row, from first enquiry to final payment. You do not move it to another tab when it is finished. Changing the status is how a job moves.

Keeping everything on one sheet means a filter can answer any question you have later, such as "what did we finish in March" or "which customers still owe us". Splitting work across tabs breaks that.

The columns that matter

Start with these, in this order. Each one earns its place by answering a question you will actually ask.

Two of these do most of the work. Next step forces you to write down the action, not the situation: "Call to confirm Friday" is useful; "waiting on customer" is not. Due turns the sheet into a to-do list, because you can sort by it.

Resist adding columns in the first month; if you must, add them at the right-hand end.

A short, fixed list of statuses

Statuses only work if there are few of them and everyone uses the same words. Seven is enough for most service businesses and small shops:

1. New: an enquiry or order has arrived and nobody has replied yet.
2. Quoted: a price has been sent; waiting for a yes.
3. Booked: the customer said yes and the work has a date.
4. In progress: the work has started.
5. Done, to invoice: the work is finished but not billed.
6. Invoiced: the bill has gone out; waiting for payment.
7. Closed: paid, or declined, or cancelled. Note which in the Notes column.

The most valuable status on that list is number five. Finished work that has not been invoiced is the easiest money a small business loses, and giving it its own status makes it visible.

To stop the list drifting into "quoted?", "QUOTED" and "sent quote", make the Status column a dropdown. In Google Sheets, choose Insert, then Dropdown, or Data, then Data validation, then Add rule, and type your seven statuses as the options. In Excel, choose Data, then Data Validation, set Allow to List, and enter the statuses as the source. Picking from a list is faster than typing, and every row ends up using the same words.

Make the sheet show you what needs attention

Three features turn a list into a tool, and both programs have all three.

Freeze the header row. In Google Sheets, View, then Freeze, then 1 row; in Excel, View, then Freeze Panes. The column names stay visible as you scroll.

Colour by status and date with conditional formatting. Two rules are enough to start:

• colour the whole row when Status is "Done, to invoice", so unbilled work stands out;
• colour the Due cell red when the date is before today and the job is not Closed.

In Google Sheets, conditional formatting is under Format, then Conditional formatting, and Google's help explains that a custom formula can format cells based on the contents of other cells, which is how one rule colours a whole row. In Excel it is under Home, then Conditional Formatting, where a rule can likewise use a formula to determine which cells to format.

Filter, do not delete. Turn on a filter for the header row and use it to see one status at a time, or to sort by Due. In Google Sheets, note the difference Google's help draws: a plain filter is seen by everyone with access to the sheet, while a filter view applies only to your view, so you can keep a saved "Open jobs by due date" without rearranging the sheet for anyone else. In Excel, choose Data, then Filter, or format the range as a table (Home, then Format as Table, with "My table has headers" ticked), which puts filter buttons on every column heading.

Protect the sheet from the usual accidents

A shared spreadsheet's most common failure is someone sorting one column on its own, which scrambles every row. Three habits prevent it:

• Sort through the filter, never by selecting a single column.
• Protect the header row so the column names cannot be typed over by accident. In Google Sheets, Data, then Protect sheets and ranges lets you either show a warning or restrict who can edit. Google is clear that this is a guard against accidents, not a security measure.
• Know where the history is. Google Sheets keeps a version history, opened from the version history button at the top of the sheet, where anyone with edit access can name a version and restore an earlier one. Name a version before any big clean-up. If your tracker is a file on one laptop, keep a dated copy somewhere else.

The fifteen-minute weekly review

The sheet only stays true if someone looks at it on a schedule. Once a week, at the same time, go through four filters in order:

1. Status = New. Every enquiry gets a reply or a next step. Nothing should sit here longer than a day or two.
2. Due before today, not Closed. Each overdue row gets either an action or a new, honest due date.
3. Status = Done, to invoice. Send every invoice. This is the step that pays for the whole exercise.
4. Status = Invoiced, oldest first. Follow up anything past its payment terms.

Then add up the Quoted column for jobs that are Booked or In progress. That number is your work in hand, and watching it week to week tells you more about the next month than any forecast.

When to keep it, and when to move on

Keep the spreadsheet as long as one or two people use it and the weekly review takes under half an hour. The signs it is time for dedicated job or order software are practical ones: several people need to update jobs from their phones at the same time, you need customers to book or pay online, or you are copying the same information into invoices and calendars by hand every day.

Even then, use the columns above to test any software: if it cannot show you, on one screen, what is new, overdue, unbilled and unpaid, it does less than your spreadsheet.

Whatever you use, keep your business records. The Canada Revenue Agency's general rule is to keep records for six years from the end of the last tax year they relate to, and its guidance on electronic record keeping says electronic records must stay in an electronically readable format for that whole period. A tracker exported once a year to a dated file is a simple way to keep a readable copy of what you did and when.

Drafted with AI assistance.

Sources

1. Google Docs Editors Help — Create an in-cell dropdown list support.google.com
2. Google Docs Editors Help — Use conditional formatting rules in Google Sheets support.google.com
3. Google Docs Editors Help — Sort & filter your data support.google.com
4. Google Docs Editors Help — Freeze, group, hide, or merge rows & columns support.google.com
5. Google Docs Editors Help — Find what's changed in a file (version history) support.google.com
6. Google Docs Editors Help — Protect, hide & edit sheets support.google.com
7. Microsoft Support — Create a drop-down list support.microsoft.com
8. Microsoft Support — Use conditional formatting to highlight information in Excel support.microsoft.com
9. Microsoft Support — Filter data in a range or table support.microsoft.com
10. Microsoft Support — Freeze panes to lock rows and columns support.microsoft.com
11. Microsoft Support — Create and format tables support.microsoft.com
12. Canada Revenue Agency — Where to keep your records, how long to keep them canada.ca
13. Canada Revenue Agency — IC05-1, Electronic Record Keeping canada.ca

• Tools
• Small business
• Spreadsheets
• Admin