Conditional Formatting & Audits
Highlight Strictly Unique (Once-Only) Values with Blank-Safe COUNTIF Rules
Flag single-occurrence IDs, unmatched reconciliation entries, or one-time buyers using COUNTIF($A$2:$A$7, A2) = 1 while preventing blank rows from highlighting.
Distinct values vs. strictly once-only (singleton) values
In reconciliation audits—such as matching bank statement references against ERP invoice references stacked in one list—items that appear twice are matched pairs, whereas items appearing strictly ONCE (COUNTIF = 1) are unposted exceptions requiring investigation.
Always wrap the conditional formatting test in NOT(ISBLANK(A2)) so pre-formatted blank rows at the bottom of the range always evaluate to FALSE.
| Reference Code | COUNTIF($A$2:$A$7, A2) | Once-Only Rule (= 1) | Interpretation |
|---|---|---|---|
| TXN-401 | 2 | FALSE | Matched pair |
| TXN-402 | 1 | TRUE (Highlight) | Unmatched singleton |
| TXN-401 | 2 | FALSE | Matched pair |
| (Blank cell) | 0 | FALSE | Ignored empty row |
| TXN-409 | 1 | TRUE (Highlight) | Unmatched singleton |
Blank-Safe Singleton Highlight Rule
Returns TRUE strictly when A2 is populated and appears exactly once inside locked range $A$2:$A$7.
=AND(NOT(ISBLANK(A2)), COUNTIF($A$2:$A$7, A2) = 1)Extract All Singleton Values into an Exception List
Pairing FILTER with the helper boolean column B2:B7 lists only the unmatched once-only codes.
=FILTER(A2:A7, B2:B7 = TRUE)Try the example
Use the original values or change them to test your understanding.