To highlight blank cells in Excel for Microsoft 365 on Windows, select the cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =A2="" when A2 is the top-left cell of your selection. Choose a format and select OK. This rule also treats a formula result of an empty string as blank; use =ISBLANK(A2) instead if you want to format only cells that are genuinely empty.
What “blank” means in conditional formatting
Excel can display a cell as empty for more than one reason. A cell might contain nothing at all, it might contain a formula that returns an empty string, or it might contain invisible characters such as spaces. Those cases look similar on the worksheet, but a conditional formatting rule can treat them differently. Choosing the right test is the key to getting the appearance you expect.
A truly empty cell has no value and no formula. A formula-empty cell contains a formula, but its displayed result is an empty string, often written as “”. A cell containing one or more spaces is not empty either, even though the spaces may be hard to see. Think about which of these states should count as blank before you create a rule.
For a typical data-entry sheet, =A2="" is a practical choice because it catches both genuinely empty cells and cells whose formulas return an empty string. For a check that should detect only cells with no content or formula, use =ISBLANK(A2). The distinction matters in reports where formulas intentionally hide results until source data is entered.
Create a rule for blank cells
For a simple range in Excel for Microsoft 365 on Windows, the formula-based method gives you control over how blank is defined:
- Select the range to format, such as A2:A100. The active cell in the selection should be A2, the top-left cell.
- On the ribbon, select Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter
=A2=""in the formula box. With A2 empty, this test returns TRUE; with the text Paid in A2, it returns FALSE. - Select Format, choose a fill, font, border, or other available formatting, then select OK to close the format dialog and OK again to create the rule.
The formula is evaluated for each cell in the selected range. Excel adjusts the relative reference as it moves through the range: the rule tests A2 for the first cell, A3 for the next, and so on. If your selected range starts at a different row or column, change the reference to its top-left cell. A rule that starts at A2 but is applied from B2 can produce an offset result.
You can also use Excel’s built-in blank-cell rule: select the range, then choose Home > Conditional Formatting > New Rule > Format only cells that contain, and choose Blanks in the rule options. Set the format and confirm. This is quick when the goal is to identify empty cells, but a formula rule is more flexible when formula results or a related column must be considered.
Choose the right blank test
Use the test that matches the data and the outcome you want. With A2 as the cell being checked, these examples illustrate the main choices:
- Empty cells and formulas returning an empty string: For a range beginning at A2,
=A2=""returns TRUE if A2 is empty or its formula returns “”. For example, a cell containing=IF(B2=0,"",B2)can display blank, so the test still flags it. - Only genuinely empty cells: With A2 as the target,
=ISBLANK(A2)returns TRUE if A2 has no entry or formula. If A2 contains a formula returning “”, it returns FALSE. - Cells containing spaces only: With A2 as the target,
=LEN(TRIM(A2))=0returns TRUE when A2 is empty or contains ordinary spaces that TRIM removes. If A2 contains the word Note, the formula returns FALSE.
These formulas produce TRUE or FALSE, which is what conditional formatting needs to decide whether to apply the selected format. In the last example, TRIM removes ordinary leading, trailing, and repeated spaces from text. It does not remove every possible invisible character; for example, a nonbreaking space copied from a web page may remain. If a cell still appears blank but the rule does not trigger, inspect or clean the underlying content rather than assuming the rule is broken.
Highlight an entire row when a cell is blank
You can use a blank in one column to format an entire record. For example, suppose a task list occupies A2:F100 and column D contains a due date. Select A2:F100, making sure A2 is the active top-left cell, then create a formula rule with =$D2="". With this layout, the rule returns TRUE across the row when D2 is empty; if D2 contains a date, it returns FALSE. The dollar sign fixes the test to column D, while the row number remains relative so Excel checks D3 for row 3, D4 for row 4, and so forth.
This combination of fixed and relative references is useful for record-level formatting. If you wrote =D2="" without the dollar sign, Excel would shift the tested column as it applied the rule across A:F. Some cells in a row would then be formatted based on other columns, not the due date. If you fixed both the column and row as =$D$2="", every row would look only at D2. Keep the column fixed and the row flexible when each row should be checked independently.
To apply the rule to a different block, adjust both the selected range and the formula’s row reference to match the first row. If the records begin on row 5, for example, select a range starting in row 5 and use row 5 in the test. After creating the rule, open Home > Conditional Formatting > Manage Rules to inspect the Applies to range and correct it if needed.
Formatting cells that are not blank
The inverse task is common too: highlight completed entries or identify cells that have a value. For a range starting at A2, use =A2<>"". This returns TRUE for a value such as Done and FALSE for an empty cell or a formula result of “”. If a cell contains spaces, however, it is not equal to an empty string, so the rule treats it as nonblank. If spaces should count as blank, use the TRIM-based test described above and apply the format when that test is FALSE.
Built-in rules can also cover straightforward cases. Select the range and choose Home > Conditional Formatting > Highlight Cells Rules > More Rules, then choose the appropriate nonblank condition if it is offered in the rule dialog. The formula method makes the criteria explicit and is especially useful when the range includes formulas or when you are basing a row’s format on a particular column.
Manage, edit, or remove the rule
Conditional formatting is attached to a range, not to a one-time selection of cells. If data is added outside the rule’s Applies to range, those new cells will not necessarily receive the formatting. To review the rule, select a cell in the affected area and choose Home > Conditional Formatting > Manage Rules. In the manager, use the “Show formatting rules for” list to view rules for the current selection or the worksheet, then edit the formula, format, or Applies to range.
To remove a rule, select the relevant cells and choose Home > Conditional Formatting > Clear Rules. The menu includes options to clear rules from selected cells or the entire sheet, so check the scope before confirming. Clearing a conditional formatting rule removes its conditional appearance; it does not delete the cell contents. Likewise, deleting a value from a cell does not remove the rule that may format it when it becomes blank again.
When several rules affect the same cells, order and settings can influence the final appearance. In Manage Rules, review the rule order and any Stop If True settings. A blank-cell rule may be working even if another rule’s fill or font makes its result hard to see. Distinct formats and a deliberate rule scope make the worksheet easier to interpret.
Common problems and practical checks
- The wrong cells are highlighted: Check that the formula reference matches the top-left cell of the Applies to range. Relative references shift as the rule is applied.
- A formula that displays nothing is not detected by ISBLANK: That is expected. ISBLANK checks whether the cell is genuinely empty, not whether its displayed result looks empty. Use
=A2=""if formula results of “” should count as blank. - A cell looks empty but does not trigger the rule: It may contain spaces, a nonbreaking space, or another invisible character. Check the formula bar or clean the source data. For ordinary spaces,
=LEN(TRIM(A2))=0can help. - The rule works for the first row only: Confirm that the row reference is relative, such as A2, rather than fixed as $A$2, unless every row is intentionally supposed to check the same cell.
- The color is missing or unexpected: Inspect overlapping rules and the format chosen in the rule. A fill color can also be difficult to distinguish from the worksheet’s normal formatting.
One frequent misconception is that a cell displaying nothing is always an empty cell. Display is not the same as stored content: formulas, spaces, and hidden characters can all make a cell appear empty. Another is that a blank-cell rule deletes or changes data. Conditional formatting changes presentation only; it does not fill the cell, erase its contents, or alter a formula.
Excel for Mac and Excel for the web
In Excel for Mac, conditional formatting is also available from the Home tab. The ribbon arrangement and dialog labels can vary slightly by release, but look for Conditional Formatting, then a new rule or formula-based rule. Use the same formula logic and reference conventions. Keyboard shortcut details differ between Windows and Mac, but no shortcut is needed for the steps above.
Excel for the web also supports conditional formatting, though some advanced rule-management options may differ from the desktop interface. If a rule is difficult to create or manage in the browser, open the workbook in the desktop app and use Home > Conditional Formatting > New Rule. In all versions, verify the selected range and the formula’s first-cell reference; those are the most important parts of a custom rule.
Frequently Asked Questions
How do I highlight blank cells in Excel?
Select the target range and use Home > Conditional Formatting > New Rule. Choose a formula rule, enter =A2="" when A2 is the top-left cell, select a format, and confirm. Or use the built-in Blanks option for a simple rule.
Does ISBLANK detect a formula that returns an empty string?
No. A cell containing a formula is not genuinely empty, even if the formula displays nothing. Use =A2="" when a formula result of “” should count as blank.
Can I highlight a whole row if one cell is blank?
Yes. Select the rows’ full data range and use a formula with the checked column fixed and the row relative, such as =$D2="" when column D is the required field and the selected range starts on row 2.
Why does Excel not recognize a cell that looks blank?
The cell may contain a formula returning “”, spaces, or an invisible character. Check the formula bar and use a suitable test. For cells containing ordinary spaces, =LEN(TRIM(A2))=0 can treat them as blank.
Will deleting a cell’s contents remove its conditional formatting?
No. Clearing a value does not remove the rule. The rule remains attached to its Applies to range and can format the cell again when its blank condition is met.
Related
- Import CSV File Into Outlook Contacts: Steps & Tips
- Import CSV to Outlook: Contacts, Steps, and Troubleshooting
- Import CSV Calendar to Outlook: Steps, Limits, and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort Excel Spreadsheet by Date: A Clear GuideTo sort an Excel spreadsheet by date, click a date cell and choose Data > Sort Oldest to…
- Calculate Age in Excel From DD/MM/YYYY With a FormulaTo calculate someone’s age in Excel from a DD/MM/YYYY birth date, enter the birth date as a real…
- Excel Data Validation Formula: Rules, Examples & FixesAn Excel data validation formula is a custom rule that controls what users can enter in selected cells.…
- Nested IF Statements in Excel: Examples, Limits, and TipsNested IF statements in Excel put one IF function inside another, letting a formula choose among several outcomes.…
- How to Filter Rows by Color in Excel: A Practical GuideTo filter rows by color in Excel for Microsoft 365 on Windows, select a cell in your data,…
- Excel Sum of Filtered Data: SUBTOTAL, AGGREGATE & FILTERTo sum only the rows currently visible after filtering in Excel, put =SUBTOTAL(9,C2:C7) in a cell outside the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.