Build a spreadsheet tracker with a clear purpose

Decide what one row means

A spreadsheet becomes difficult to maintain when a row sometimes represents a chapter, sometimes a task and sometimes a person. Choose one unit for the sheet. A submission tracker might use one row per submission; a permissions tracker might use one row per requested use. Related information can live on another sheet when it follows a different unit.

Write a sentence explaining what you want to learn from the tracker. “Show which chapter illustrations are still waiting for a usable file” suggests columns for chapter, illustration identifier, source file, status and next action. It does not automatically require a dashboard, a progress score or a separate tab for every chapter.

Try five real entries before building the whole system. Include an awkward case, such as an illustration used twice or a submission with no response date. If the structure cannot represent that case without a paragraph squeezed into the wrong column, revise it early.

Separate facts from decisions

Keep dates, status and notes in different columns. A cell containing “Sent Tuesday, probably fine, chase next month” is readable once but difficult to sort or filter. Store the sent date as a date, the status as a consistent label and the planned follow-up separately.

Choose a small vocabulary for status. “Waiting”, “Awaiting reply” and “No response yet” may mean the same thing, but a filter treats them as different values. A dropdown can reduce accidental variation if the application supports it. Keep an explicit value for unresolved information rather than letting a blank cell mean several different things.

Do not encode the only record of a decision in colour. A yellow cell might be obvious today and mysterious later. Put the status in text, then use colour as an additional cue. The sheet should remain understandable when printed without colour or opened by somebody who did not build it.

Protect the relationships between cells

Sort the whole table, not an isolated column whose values belong with neighbouring cells. Microsoft’s Excel guidance describes sorting and filtering structured tables through their column headings. The underlying principle is that a record must stay together when its position changes.

Give important records a stable identifier. If row 23 becomes row 8 after sorting, a comment that says “see row 23” may now point to something else. An illustration number or submission ID can survive that movement. Use it in related notes and filenames where appropriate.

Keep original entry order if it matters. A numbered sequence column gives you a way to restore that order after a sort. Do not assume that clearing a filter also reverses a previous sort; filtering which records are visible and ordering those records are different operations.

Add formulas only for a real calculation

A formula can show days until a deadline or count items in a particular state, but its result is only useful if the inputs mean what you think they mean. Test it against a few cases with known answers, including a missing date and a completed item. Decide how those cases should appear before trusting the result.

Keep calculated cells distinguishable from fields you type into. If other people use the tracker, explain which columns they should update. A formula silently replaced by a typed number can leave the sheet looking normal while its logic has stopped working.

Review the tracker alongside the actual files or correspondence. Status does not update itself just because the spreadsheet is tidy. Set a modest review habit: record a response when it arrives, change the next action, and close a task when its result has been verified.

Before sharing or archiving the workbook, clear filters so that hidden records are not mistaken for missing ones. Add a short note describing the row unit, status labels and any important formulas. The best test is whether you can reopen the file after a month and understand what to do next without remembering the system’s private rules.