Your team has a spreadsheet, but one person writes “waiting,” another writes “pending,” and a third overwrites the formula that totals the open work. A useful tracker needs two separate controls: agreed choices for the fields people update and restricted editing for the cells that calculate results.
Start with your existing Google Sheets file. You do not need to purchase a project-management platform to make a small shared tracker more reliable. Set it up on a copy, check it with a colleague, and then apply the confirmed arrangement to the working file.
Give each row a clear job
Use one row per task or work item. Include a stable ID, a short description, a responsible person, a status, the next action, and any needed date. Keep calculated totals or summaries in separate, clearly labeled cells.
Here is a fictional starting point:
| ID | Work item | Responsible person | Status | Next action |
|---|---|---|---|---|
| W-101 | Prepare client proposal | Maya | In progress | Send draft for internal review |
| W-102 | Confirm supplied images | Jordan | Waiting | Ask the client for missing files |
| W-103 | Deliver approved files | Maya | Done | No further action |
The status describes the current state; the next action explains how work moves. Define Waiting as “a named dependency prevents the next step,” not “nobody has looked at it.” Agree what counts as Done, such as a completed delivery rather than an internally finished draft.
Use one short list of statuses
For this example, choose Not started, In progress, Waiting, and Done. Keep status separate from urgency and from the person’s name. If you need a priority field, give it its own column.
Google’s dropdown documentation describes adding choices through Insert → Dropdown or Data → Data validation. Apply the rule to the intended status range. Keep one status per row for this tracker and reject values outside the agreed list. The alternative warning option allows an invalid value to remain, so it does not enforce consistency.
Try an invented status in the test copy. Confirm that the rule rejects it. Then add a new row and check whether the intended validation range includes it; a rule covering only the original rows will not protect future entries outside that range.
Protect calculations separately
Mark the formula cells clearly and identify who is responsible for maintaining them. Through Data → Protect sheets and ranges, choose the relevant formula range and restrict who may edit it. Google’s protection guide distinguishes edit restrictions from warning-only protection. A warning is a prompt, not a blocked edit.
Have a colleague with the intended everyday permissions try changing an input and a protected formula in the test copy. The input should remain usable; the calculation should be restricted as intended. Testing only from the file owner’s account will not demonstrate the ordinary editor’s experience.
Range protection prevents some unwanted edits. It does not make confidential content secret from people who can access the spreadsheet. Use appropriate sharing permissions for that separate question.
Run a small acceptance check
Before using the tracker, verify these concrete outcomes:
- Every sample item has one responsible person and an understandable next action.
- A valid status can be selected; an invented status is rejected.
- An ordinary editor can update the intended inputs but cannot alter restricted formula cells.
- Adding a new item preserves the required rules and calculations.
- The calculated summary agrees with the visible sample rows.
Keep the example rows until the team understands the fields, then replace them with appropriate work items. When a new status is needed, update the shared definition and rule deliberately rather than accepting several spellings of the same thing.
The result is a tracker with predictable inputs and maintainable calculations. It still needs a person to keep work current, but it no longer depends on everyone remembering which cells they must avoid.