Suggested application: Before treating a blank next-action cell as a follow-up problem, check whether the same job appears elsewhere with different instructions. This tutorial focuses on that duplicate-record audit, rather than building another missing-action queue. Use it on a small working slice of your service-business spreadsheet, compare repeated job references, and decide which action belongs to the current record. Keep the scope narrow: the audit identifies records needing a decision; it does not automatically determine whether an action is overdue.
Prepare a job-reference and next-action audit slice
Suggested application: Arrange the audit slice with Job reference in column A and Next action in column B, with headings above the records. Use a consistent reference for each job rather than relying on customer names: the same customer might have separate jobs. Start with a manageable selection from the working spreadsheet, and keep enough context in the original records to distinguish current work from completed work.
Suggested application: Define what you will review before applying formatting. A repeated reference with an empty action deserves comparison with its other entries. Repeated references with different actions deserve clarification. A unique reference with an empty action needs its own decision. Do not assume every repeated reference is an error; your workflow might intentionally contain separate entries for different stages of one job.
Highlight repeated job references
Google Sheets supports conditional formatting with custom formulas, including a documented duplicate-value rule. On your computer, select A1:A100, then choose Format, Conditional formatting. Under Format cells if, choose Custom formula is. In Value or formula, enter =COUNTIF($A$1:$A$100,A1)>1. Choose a distinctive formatting style and click Done. The rule checks for repeated values within that range; the dollar signs keep the comparison range fixed. [1]
Suggested application: Treat the highlight as a prompt to compare records, not permission to delete them. Inspect populated job references first, rather than treating highlighted empty cells as jobs. Keep the next-action wording visible while reviewing each repeated reference, and write down which entries appear current, historical, or unresolved before changing the working sheet.
Make a compact view that retains missing actions
For a small audit slice occupying A2:B10, enter =FILTER(A2:B10, NOT(ISBLANK(A2:A10))) in a separate area. This documented FILTER example returns rows whose column A cells are not blank. Because its condition examines the reference column rather than the action column, it retains identified jobs even when their next-action cell is empty. FILTER conditions must match the source range’s length, and a result with no matching rows returns #N/A. [2]
Suggested application: Use this compact view to read references and actions together, while using the original highlighted range to locate duplicates. Limit the review to the source slice actually covered by the formula. If you expand it, extend the source range and condition range together. Do not filter on populated next actions for this audit: that would leave the missing-action records outside the view you intended to inspect.
Resolve conflicting records before assigning follow-up
Suggested application: In a hypothetical cleaning business, the same job reference appears beside an empty action and beside “Confirm access with the site contact.” The reviewer checks the original records before deciding whether the blank entry is outdated or still active. If both entries remain necessary, the reviewer gives each an explicit purpose. If the current job lacks an agreed action, the reviewer asks the responsible colleague to define it rather than copying an instruction without context.
Check the audit boundaries and avoid misleading signals
Existing formatting deserves attention: Google Sheets evaluates rules in their listed order, and the first true rule determines the formatting. Copying cells that contain formatting rules also applies those rules when pasted. Check for competing rules or copied formatting before interpreting the duplicate highlight. [1]
Suggested application: Finish with this checklist: confirm the reference convention, check the covered ranges, compare every repeated populated reference, review blank actions on current jobs, and record the agreed instruction in the working sheet. Common mistakes include merging separate jobs for one customer, trusting an action on an obsolete row, and calling a job overdue without checking its agreed timing. Leave unresolved cases visibly unresolved rather than inventing an action.
Sources
- Use conditional formatting rules in Google Sheets – Computer – Google Docs Editors Help
- FILTER function – Google Docs Editors Help
Prepared with AI assistance from the linked documentation, checked on 2026-10-02. Examples are suggested workflows, not claims of hands-on product testing.