Conditional Formatting to Highlight Blank Cells

Yes. In Excel for Microsoft 365 on Windows, select the range and choose Home > Conditional Formatting > New Rule > Format only cells that contain > Blanks, then choose a format. A formula-based rule is useful when you need to distinguish truly empty cells from formulas that display nothing, spaces, or other special cases.

What a blank cell means in Excel

A cell can look empty without being empty in the way a rule expects. A genuinely empty cell has no value or formula. But a cell might also contain a formula whose result is an empty text string, a space character, or formatting that makes its contents hard to see. These cases can look identical on the worksheet while behaving differently in conditional formatting.

This distinction matters because the choice of rule determines which cells receive the highlight. If you are marking missing entries in a form, a genuinely empty cell may be the only one you want. If a formula leaves a result blank until someone enters source data, you may want to treat that displayed blank as missing too. Decide what “blank” means for your task before applying a rule.

Conditional formatting changes how qualifying cells look; it does not fill them, delete their contents, or change their underlying values. The color is a visual cue, so you can still type into a highlighted cell or use its existing value in calculations.

Use Excel’s built-in blank-cell rule

For ordinary empty cells, the built-in rule is the quickest choice. In desktop Excel for Microsoft 365 on Windows:

Select the cells you want to check. For example, select B2:B100 to check one column, or B2:F100 to check a block.
Choose Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog, choose Format only cells that contain.
In the rule description, choose Blanks from the available cell-content options.
Choose Format, set a fill color or another visual style, and select OK. Select OK again to create the rule.

The rule applies to the selected range, which you can review later under Home > Conditional Formatting > Manage Rules. If you chose a range before creating the rule, check the Applies to field to confirm that it covers the intended cells. The dialog’s layout can differ slightly between desktop Excel and Excel for the web, but the key task is to create a rule that formats blank cells in the selected range.

Use a formula when blank has a specific meaning

A formula rule gives you more control than the built-in option. To identify truly empty cells, use ISBLANK. With the range A2:A50 selected and A2 as its top-left cell, enter =ISBLANK(A2) in the formula-rule box. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format, enter the formula, and set the format. Excel adjusts the relative reference for the other cells in the selected range.

To treat a truly empty cell and a cell containing a formula that returns an empty text string as blank, compare the cell with an empty string. With A2:A50 selected and A2 at the top of the range, use =A2="". This tests what the cell evaluates to, rather than whether the cell is physically empty. It is often the more useful test for report columns that use formulas to show results only after inputs are provided.

References must line up with the selection. If your selected range begins at C5, write the formula using C5 as the relative reference, not A1. Excel evaluates the formula for each cell as it applies the rule. A mismatched starting reference can shift the highlight to the wrong cells.

Highlight a whole row when a cell is blank

You can use one column as a checklist and highlight the rest of its row when that field has no entry. Suppose a table runs from A2:F100 and column C contains a required status. Select A2:F100, create a formula-based conditional formatting rule, and use =$C2="". The dollar sign fixes the test to column C, while the row number stays relative so each row checks its own status cell.

This is useful for spotting incomplete records without coloring only the missing cell. If your goal is instead to highlight a row only when every cell from A through D is blank, select the rows to format and use a count-based test. With A2:D100 as the data and A2:D2 as the first row being checked, use =COUNTBLANK($A2:$D2)=4. The number 4 represents the four cells that must be blank; change it if the checked span has a different number of columns.

For a row that should be highlighted if any cell in A through D is blank, the condition is different. With A2:D100 selected for formatting and row 2 at the top, use =COUNTBLANK($A2:$D2)>0. Be aware that counting blanks is a broader test than checking for cells with no content: Excel’s COUNTBLANK function also counts formulas that return empty text.

Spaces and other values that look blank

A cell containing a space is not empty. A simple blank rule may therefore leave it unhighlighted, even though there is no visible text. This can happen when data is pasted from another source or when someone presses the space bar instead of leaving a field untouched.

If you want to treat a cell containing only ordinary spaces as blank, use TRIM and LEN. With A2:A50 selected and A2 at the top, use =LEN(TRIM(A2))=0. TRIM removes leading and trailing standard spaces and reduces repeated internal standard spaces; LEN then checks whether anything remains. This formula also returns true for an actually empty cell.

TRIM does not remove every invisible character that can arrive through imported data. For example, a nonbreaking space may remain and prevent the test from identifying a cell as blank. If a cell looks empty but will not highlight, inspect or clean the source data rather than assuming the formatting rule is broken. You may need to replace the unwanted character or use a formula designed for that specific character.

Choose the right rule for the task

The built-in Blanks rule is convenient when you want a quick visual check and the cells are expected to be genuinely empty. A formula rule is preferable when a worksheet uses formulas, when a particular column determines whether a record is complete, or when spaces should count as blank. The best choice depends on whether you care about the cell’s contents or what it displays.

Truly empty cells: use the built-in blank rule or ISBLANK.
Empty cells and formulas displaying an empty result: compare the cell with an empty string.
Rows controlled by a required field: use a formula with a fixed column reference and a relative row reference.
Cells with only standard spaces: use a TRIM-and-LEN test, and check for other invisible characters if needed.

Conditional formatting can also be combined with other rules, such as highlighting duplicate values or values outside an expected range. If two rules apply to the same cell, the displayed result can depend on rule order, the formats each rule sets, and whether Stop If True is selected. Review the rules in Manage Rules if a cell’s appearance does not match your expectation.

Common problems and how to fix them

The wrong cells are colored: check the rule’s Applies to range and confirm that the formula reference matches the range’s top-left cell.
A formula-generated blank is missed: test the cell with an empty-string comparison instead of relying on a test for physical emptiness.
A cell looks empty but is not highlighted: check for spaces, nonbreaking spaces, or other invisible characters.
Existing colors remain: conditional formatting does not remove a cell’s ordinary fill color. Review the rule and the cell’s regular formatting to see which appearance is visible.
New rows are not included: inspect Applies to and extend the range. If the data is an Excel table, check whether the rule’s range covers the added table rows.

To edit or remove a rule, select a cell in the affected range and choose Home > Conditional Formatting > Manage Rules. In the dialog, set the display choice to the current selection or the relevant worksheet as appropriate, then edit the rule or delete it. Removing a conditional formatting rule removes its visual effect; it does not erase cell contents.

Frequently Asked Questions

Can conditional formatting highlight blank cells automatically?
Yes. Create a blank-cell rule for the range you want to monitor. The formatting updates when cell contents change, provided the cell remains within the rule’s Applies to range.

Why does Excel not highlight a cell that looks empty?
It may contain a space, an invisible character, or a formula. Check what is in the cell and choose a rule that matches the case: a physical-blank test, an empty-string comparison, or a whitespace-aware formula.

Does ISBLANK recognize a formula that returns an empty string?
No. ISBLANK checks whether a cell is genuinely empty, so a cell containing a formula is not blank to that function even if the formula displays nothing. Use an empty-string comparison when the displayed result is what matters.

Can I highlight an entire row when one cell is blank?
Yes. Select the full rows or data block to format, then use a formula that fixes the required column while letting the row reference adjust. This applies the highlight across each row based on its own checked cell.

Will the rule change or delete the cell’s data?
No. Conditional formatting changes appearance only. To fill or remove values, use a separate editing action; the highlight itself does not alter the cell’s contents.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.