Use this guide when you want to keep track of something that is currently scattered across notes, emails or memory. You need any spreadsheet program and a clear idea of what questions you want the tracker to answer.
Step by step
Decide what questions it must answer
Write down three or four questions, such as what is overdue, how much was spent this month or which jobs are waiting for a reply. Each question tells you which columns you need. Anything that does not help answer a question can be left out.
Set up one row per item
Put column headings in the first row, such as Date, Item, Person, Status, Amount and Notes. Each row should record one thing only. Avoid merged cells and blank rows, because they break sorting and filtering.
Keep entries consistent
Format date columns as dates and money columns as currency. Use data validation to create dropdown lists for columns like Status, with a small fixed set of options. Consistent entries make filters and totals reliable.
Add filters and freeze the header
Turn on filters for the heading row and freeze it so it stays visible as you scroll. You can now show only open items or sort by date in a couple of clicks. Consider turning the range into a table so new rows are included automatically.
Add simple totals and highlights
Use basic formulas such as SUM or COUNTIF in a separate summary area to answer your questions. Add conditional formatting to highlight overdue dates or high amounts. Keep formulas away from the data rows so they are not overwritten.
Make it a habit
Decide when you will update the tracker, such as at the end of each day or when an invoice arrives. Save it somewhere that is backed up. Review it weekly and archive finished items to a second sheet if it gets long.
Ready-to-use checklist
- Key questions written down
- One row per item
- Clear column headings
- Dates and amounts formatted
- Dropdowns for status
- Filters on and header frozen
- Summary totals added
- File saved in a backed-up location
Practical tips
- Add a Last Updated column so you can see which rows have gone stale.
- Keep a notes column for free text rather than adding new columns for one-off details.
- If several people use it, keep it in a shared online file rather than emailing copies around.
Common problems
Sorting mixes up the rows.
This usually happens when only one column was selected before sorting. Undo, then click any cell inside the data and use the sort option so the whole table moves together, or convert the range into a table.
Totals are wrong or show zero.
Some numbers may be stored as text, often after pasting from elsewhere. Check the cell format, convert them to numbers and make sure the formula range covers all rows.
The tracker has become too big and messy.
Move completed items to an archive sheet and remove columns nobody uses. If you need several linked lists, such as customers and jobs, it may be time to consider dedicated software.