Most small businesses already have a tracker. It might be a sheet of jobs, enquiries, invoices or stock that grew one column at a time, with a different spelling of "Pending" in every row. It works until the day you need an answer from it, such as who still owes you money.
A good tracker is dull in the right way. This guide covers what that looks like, what to have ready and what WebFresh Studio builds.
What good looks like
- One row for each thing you track, such as a job or an invoice, and one column for each fact about it.
- Real dates in date columns and real numbers in number columns, so sorting and totals work.
- Drop-downs for anything with a fixed set of answers. Google Sheets data validation can reject entries that don't match the list, or show a warning instead, so "Done", "done" and "Finished" don't all appear.
- Formulas and the dashboard kept apart from the cells people type in. Google Sheets lets you protect a sheet or a range and choose who can edit it. Google is clear that this isn't a security measure, and that hidden sheets can still be seen.
- A change history you can go back to. Google Sheets keeps a version history, lets you name versions and restore an earlier one.
- A dashboard that answers a handful of questions you actually ask, not every chart a spreadsheet can draw.
In Excel, formatting your data as a table gives you filter buttons in the header row, banded rows, a total row, formulas that fill down on their own and data validation. Microsoft's overview describes each.
Where trackers usually go wrong
These are our own observations rather than anything from an outside source, but they come up again and again:
- Status typed freehand, so a filter finds half the jobs.
- Totals typed in by hand and never updated when a row changes.
- One sheet per month, so the year-end question means opening twelve files.
- Blank rows and merged cells in the middle of the data, which break sorting and formulas.
- A single copy emailed round the team, with three people each holding a different version.
Every one is fixable without starting again, which is where a clean-up can help.
A fictional example
Penhallow Cleaning (a made-up business) sends two teams to offices and holiday lets. Its tracker has one row per booking, with columns for date, client, site, service, quote, status, invoice sent and paid on. Status is a drop-down: Quoted, Booked, Done, Invoiced, Paid.
The dashboard on a second sheet answers three questions:
- How many bookings are in each status?
- How much is invoiced but not yet paid?
- What is booked for the coming week?
Nothing in it is clever. It works because every status is spelled one way and every date is a date.
Keep personal details to what you need
If the tracker holds customer names, phone numbers or addresses, data protection law applies. Article 5 of the UK GDPR says personal data must be adequate, relevant and limited to what is necessary, kept no longer than necessary, and kept secure. For a tracker, that means:
- Only the columns the job needs. A cleaning rota rarely needs a date of birth.
- A tidy-up now and then, so old rows don't sit there for years.
- Sharing with named people rather than anyone with the link.
This is general information, not legal advice.
What to have ready
- Your current sheet or sheets, however messy. If they contain customer details, tell us before sending.
- The questions you want answered, such as "what's outstanding" or "what's low".
- The list of statuses or categories you already use, even if the spellings vary.
- Whether you and your team use Google Sheets or Excel, and on a phone or a laptop.
- Who needs to type and who only needs to read.
What WebFresh Studio builds
Trackers and dashboards start from £55 for one tracker with a dashboard, in simple Google Sheets or Excel. We set up the columns, drop-downs, formulas and dashboard from what you send, protect the parts that shouldn't be typed over, and show you how it's laid out.
If you already have the data but it's in a mess, spreadsheet clean-up is from £50, for example for merging five sheets or converting five PDF pages into Excel. Some people start with the clean-up and add the tracker once the data is tidy.
We agree a fixed price before any work starts, and we don't need access to anything beyond the file you send. For the full list, see the other services.
Frequently asked questions
Should my tracker be in Google Sheets or Excel?
Either. Google Sheets suits people working together on different devices. Excel's tables suit people who already live in it. We build in the one you use now.
What does a dashboard actually do?
It summarises the tracker on one sheet: counts, totals and what's due. It updates when the tracker does, so you don't rebuild a summary each week.
Can people accidentally overwrite the formulas?
You can lower the risk. Google Sheets lets you protect sheets and ranges so only chosen people can edit them. Google says this is not a security measure, so don't rely on it for confidential information.
My tracker has customer details in it. Does that matter?
Yes. Under the UK GDPR, personal data should be limited to what is necessary, kept no longer than needed and kept secure. This is general information, not legal advice.
What do you need from me to start?
Your current sheet, if there is one, and a list of the questions you want the dashboard to answer.