A note beside every row that is still waiting on something. Useful before sending a list on, when a single gap causes a problem downstream.
The formula
=IF(ISBLANK(B2),"Not assigned","") in column C
What each part does
ISBLANK(B2)
True only when the cell holds nothing at all.
"Not assigned"
Shown on the rows with a gap.
""
An empty result on the rows that are fine, so nothing is shown there.
How it works, and what to watch for
ISBLANK is stricter than most people expect. A cell that another formula filled with "" looks empty on screen but is not blank to Excel, so ISBLANK returns FALSE and the gap goes unnoticed.
For that reason =IF(B2="","Not assigned","") catches more cases: it treats both a truly empty cell and an empty text result as missing.
A space typed into a cell also counts as filled. TRIM(B2)="" is the test that catches those as well.
Questions
How do I count the gaps instead of marking them?
COUNTBLANK(B:B) gives the number in one cell. It follows the same strict rule about what counts as blank.
Can I colour the empty rows instead?
Conditional formatting with the same test as its rule does it without an extra column.
Why is my cell not blank when it looks it?
Something is in it: a space, an apostrophe, or an empty string from a formula. Press Delete on the cell and the test will change its mind.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
