OmniDocConditional Formatting for Blank and Empty Cells
Learn how to highlight blank cells with conditional formatting, and how to skip cells that only look empty because they hold spaces or formulas.
If you keep a list in a spreadsheet, you probably want missing entries to stand out: an empty phone number in a contact sheet, a missing date in a tracking log, or an unfilled field in an order form. Conditional formatting can highlight those gaps automatically, so you do not have to scan every row. The catch is that a cell can look empty and still fail a simple blank check. A space, a formula that returns nothing, or a non-breaking space pasted from a web page can all slip past a basic rule.
This guide shows how to build a conditional formatting blank cell rule that catches what you actually mean by "blank," and how to avoid highlighting cells that only appear empty. The method works in any desktop spreadsheet app or web spreadsheet that supports formula-based conditional formatting. The examples use column A starting in row 2, so you can adapt the cell references to your own sheet.
Which formula should flag a blank cell? The short answer
Use =LEN(TRIM(A2))=0 for most lists, because it catches truly empty cells, cells holding only spaces, and cells holding a formula that returns nothing. Choose a narrower formula only when you need to distinguish between those cases.
| Your situation | What to do | Why |
|---|---|---|
| Cells are truly empty and nothing has ever been typed in them | Use =ISBLANK(A2) | It returns TRUE only for cells with no content at all. |
| Cells may contain a formula that returns "" | Use =LEN(A2)=0 | Empty text has a length of zero, so the rule catches it. |
| Cells may contain typed or pasted spaces | Use =LEN(TRIM(A2))=0 | TRIM removes ordinary spaces before the length check. |
| The data came from a web page and has non-breaking spaces | Wrap the cell in SUBSTITUTE to remove CHAR(160), then apply LEN | TRIM does not remove these characters, so they make a cell look blank but still count as content. |
Before you start
- Decide what "blank" means for your data. A cell with a space is blank to a person but not to ISBLANK. Settle this first, because it determines which formula you use.
- Exclude the header row. Start your range in row 2 so the column title is not highlighted. A rule that reaches the header will mark it every time.
- Check whether any cells contain formulas. If a column is calculated, a blank result may be a formula returning "", which ISBLANK will not flag.
- Work on a copy of the sheet. Conditional formatting rules are easy to edit, but a wrong range or anchor can hide problems that only show up later in a long file.
- Prepare three test cells. Put one truly empty cell, one cell with a single space, and one cell with the formula =IF(1=2,1,"") in a spare area. You will use them to confirm the rule works.
Setting up a conditional formatting blank cell rule, step by step
- Select the data range without the header. For a list of 99 entries in column A, select A2:A100. Include every row you expect to fill, or the rule will stop at the last row you selected.
- Open the conditional formatting options and create a new rule. Choose the option that lets you write a rule using a formula. This gives you control over exactly which cells are flagged.
- Enter the formula using the top-left cell of your selection. For A2:A100, type =LEN(TRIM(A2))=0. Use a relative reference (no dollar signs on the column or row) so the formula shifts down for each row.
- Choose the format. Pick a light fill color, such as pale yellow, so the highlight is visible without hiding the text. Avoid a bold font change if the cell will later hold data, because the formatting can be confusing when the cell is filled in.
- Confirm the "applies to" range. Make sure it reads A2:A100 (or your range). If it shows only A2, the rule will apply to a single cell, and every other row will be unhighlighted.
- Save the rule and test it. Type a space into one of your test cells, leave another truly empty, and add the formula returning "" to a third. All three should be highlighted. Then type a letter in a cell and confirm the highlight disappears.
Check the result
After the rule is in place, go through a few cases to confirm it behaves the way you expect.
| Check | How to see it | If it's wrong |
|---|---|---|
| Truly empty cells are highlighted | Clear a cell completely and look at it | If it's not highlighted, check that the range covers that row and that the formula is correct. |
| Space-only cells are highlighted | Type a single space into a test cell | If it's not highlighted, the rule is probably ISBLANK. Switch to =LEN(TRIM(A2))=0. |
| Formula-blank cells are highlighted | Enter =IF(1=2,1,"") in a test cell | If it's not highlighted, the rule is probably ISBLANK. Switch to =LEN(A2)=0 or the TRIM version. |
| Filled cells are not highlighted | Type a letter or a number into a blank cell | If it stays highlighted, the reference may be anchored with dollar signs, so every row checks the same cell. |
| Rows added later are covered | Add a new entry below your last row | If it's not highlighted, extend the rule's range to cover the new rows. |
Keep in mind that a highlight tells you a cell matches your formula, not that the data is correct. If a highlighted cell should contain a value that is missing from another source, you still need to fill it in or check the original data.
🎯 Which approach fits you?
Your situation changes which formula you should use:
- You enter data by hand. A basic ISBLANK check is usually enough, since typed cells are either empty or filled.
- You paste data from websites, emails, or other files. Use the TRIM version, because pasted content often brings spaces with it.
- Your sheet contains calculated columns. Use the LEN version, because formulas that return "" will be missed by ISBLANK.
❓ FAQ
1. Can I flag a row when one column is blank but another has a value?
Yes. Write a formula that checks both cells, such as =AND($A2="",$B2<>""), and apply it across the full row range. The dollar signs keep the column fixed while the row number moves down.
2. Why does my blank-cell rule skip some cells that look empty?
Those cells probably contain a space, a non-breaking space, or a formula that returns nothing. ISBLANK misses the last two cases, and a plain length check misses non-breaking spaces. Use the TRIM or SUBSTITUTE version to catch them.
3. Does a zero count as blank?
No. A cell containing 0 has a length of 1, so LEN(A2)=0 returns FALSE and the cell is not highlighted. If zeros should count as missing, you need a separate rule that checks for the value 0.
4. Does conditional formatting change what is in the cell?
No. It only changes how the cell looks when the formula is true. The contents stay the same, so you can remove the rule at any time without losing data.
📝 Summary
A reliable conditional formatting blank cell rule starts with deciding what "blank" means in your data. Use ISBLANK for truly empty cells, LEN for formula blanks, and LEN(TRIM) when spaces may be present. Test the rule with a few sample cells before you rely on it.
