To highlight blank cells in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > New Rule > Format only cells that contain and set the rule to Blanks. If you need a formula-based rule, use =A2="" when A2 is the top-left cell of the selected range; it matches genuinely empty cells and formulas that display an empty string.
What Excel Means by a Blank Cell
“Blank” can mean different things in a spreadsheet. A cell may truly contain nothing, contain a formula whose result is an empty string, or look empty because its contents are spaces or are hidden by formatting. Those cases can look identical on screen but behave differently in conditional formatting.
The built-in Blanks rule is the simplest starting point when you want to mark cells with no entered value. A formula rule gives you more control, especially when a worksheet contains formulas that return empty text. The choice matters in data-entry forms, trackers, and reports: a formula-based cell may look empty but still not be an unused cell.
Conditional formatting changes a cell’s appearance; it does not add a value, remove a formula, or make a cell genuinely empty. A colored blank remains blank, and a formula that returns an empty string remains a formula. This distinction is useful when another formula, filter, or data-validation rule depends on what is actually stored in the cell.
Apply the Built-In Blanks Rule
For a straightforward highlight, select the target range first. For example, to format empty cells in a form’s response area, select B2:B30. Then use this sequence in Excel for Microsoft 365 on Windows:
- Choose Home > Conditional Formatting > New Rule.
- In the New Formatting Rule dialog, select Format only cells that contain.
- Set the rule description to format only cells with Blanks.
- Select Format, choose a fill, font, border, or number format, and confirm with OK.
- Check that the Applies to range is the range you intended, then select OK again.
The formatting appears on cells Excel recognizes as blank. To change the rule later, select a cell in the range and choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, you can edit the format, change the Applies to range, or delete the rule. If the manager is showing rules for the current selection, switch its display option to the worksheet when you need to inspect rules outside that selection.
On Excel for Mac, the same conditional-formatting features are available, though dialog layouts can differ slightly by release. Use the Home tab’s Conditional Formatting menu and choose New Rule; the ribbon labels and rule choices are generally similar. Excel for the web also supports conditional formatting, but its dialogs may be arranged differently from the Windows desktop dialog.
Use a Formula for More Control
Formula rules are useful when the built-in Blanks condition does not match your definition of empty. Select the full range to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that evaluates to TRUE for cells that should receive the format.
For a selected range A2:A20, where A2 is the top-left cell, use =A2="". It returns TRUE when A2 is truly empty or when a formula in A2 returns an empty string. Excel adjusts the relative row reference for other cells in the Applies to range, so the rule tests A3 for the next row, A4 for the one after that, and so on.
For a rule that should identify only cells with nothing stored in them—not cells containing formulas that display nothing—use =ISBLANK(A2) for the same A2:A20 range. ISBLANK returns TRUE for a genuinely empty cell and FALSE when a cell contains a formula, even if that formula displays no visible text. Pick this version when the distinction between an unused cell and a formula result is important.
To treat cells containing only ordinary spaces as empty too, use =LEN(TRIM(A2))=0 for a range beginning at A2. This returns TRUE for an empty cell, an empty-string result, or text made only of regular spaces. TRIM removes leading and trailing spaces and reduces repeated spaces between words, so this condition is best for fields expected to contain a single value rather than carefully spaced text.
Choose the Rule That Matches Your Task
There is no single blank rule that is best for every worksheet. Decide what “blank” means for the job, then select the condition accordingly:
- Highlight unused input cells: Use the built-in Blanks rule, or ISBLANK if you want to exclude formula cells.
- Highlight cells that look empty: Use the formula comparison with an empty string,
=A2="", when formulas may return no visible text. - Flag entries containing spaces only: Use the TRIM and LEN rule, while remembering that it tests ordinary spaces rather than every possible invisible character.
- Mark a missing value in a particular field: Apply the rule to that field’s range rather than to the entire worksheet.
A common misconception is that every cell that looks empty is a blank cell. A cell containing a space is not truly empty, and a formula returning an empty string is still a formula. If your purpose is to show where a person still needs to enter information, test the kind of content your workbook actually contains instead of relying only on its appearance.
Highlight Entire Rows When a Field Is Blank
You can use a blank condition to shade an entire record row when one important field is missing. Suppose the table occupies A2:D20 and the required value is in column B. Select A2:D20 and create a formula rule using =$B2="". The dollar sign fixes the column at B while the row number remains relative, so each row’s formatting depends on its own B cell.
Use =$B2="" if a formula returning an empty string in column B should also count as missing. If only truly empty cells should trigger the row highlight, use =ISBLANK($B2) instead. In both cases, the formula reference should match the top row of the selected Applies to range. If the range starts at row 3 but the formula refers to row 2, Excel will test the wrong row and the highlight will appear shifted.
For a more focused review, select just the required-field cells rather than the whole record. This can make a large sheet easier to scan and avoids coloring every column. If the workbook is an Excel table, adding rows may extend the table’s formatting, but confirm the conditional-formatting rule’s Applies to range if new rows do not receive the expected highlight.
Common Problems and How to Fix Them
If no cells change color, first check the rule’s Applies to range in Home > Conditional Formatting > Manage Rules. Then confirm that the formula refers to the top-left cell of that range. A formula with the wrong starting row is a frequent cause of results that appear one row out of place.
Also check whether another conditional-formatting rule is overriding the display. In the Rules Manager, rules are evaluated in order, and options such as Stop If True can affect which formats appear. If a cell meets more than one rule, the visible result may not be the fill you expected. Reorder the rules or simplify overlapping conditions rather than changing the blank test without checking.
If cells that look empty are not highlighted by the built-in rule, inspect them for formulas or spaces. Click a cell and look at the formula bar: a formula may be present even though the cell displays no text. For a cell that contains only a space, an empty-string comparison does not match, because a space is text. The TRIM and LEN formula can handle regular spaces if that is appropriate for the data.
If cells with formulas are being highlighted unexpectedly, replace a comparison with an empty string by ISBLANK when you only want genuinely empty cells. Conversely, if formulas returning an empty string need to be flagged, ISBLANK is too strict; use the comparison rule instead. Avoid deleting the formula just to make the cell appear empty, since that changes the workbook’s calculations rather than just its formatting.
Built-In Rules, Formula Rules, and Cell Values
The built-in Blanks option is quicker to set up and easier for another user to understand at a glance. It is a good default for simple forms and ranges where cells are either filled or unused. Formula rules take more care to configure, but they can distinguish truly empty cells from formula results, test for spaces, or apply formatting to a whole row based on one column.
Conditional formatting is not the same as data validation. Formatting can draw attention to a missing entry, but it does not prevent someone from leaving the field blank. If a value is required, consider using data validation or a separate check before relying on color alone. Color may be hard to notice, may be printed differently, and can be missed by people using assistive technology or viewing the workbook on a small screen.
It is also not the same as filtering for blanks. Filters change which rows are displayed; conditional formatting changes how cells look. A worksheet can use both, but each feature has its own rules. If you need to count missing entries, use a counting formula or another data-quality check rather than assuming that colored cells are automatically counted as blank.
Frequently Asked Questions
Can conditional formatting highlight a cell that contains a formula returning nothing?
Yes. Use a formula rule comparing the cell with an empty string, such as =A2="" for a range beginning at A2. ISBLANK will not identify that formula cell as blank because the cell contains a formula.
Why does Excel not highlight a cell that looks blank?
The cell may contain a space, an invisible character, or a formula. Check the formula bar and choose a test that matches the content. A rule for genuine blanks will not necessarily match text or formula results that only look empty.
How do I highlight a whole row if one cell is blank?
Select the table rows you want to format and use a formula that fixes the required column but leaves the row relative. For a range starting at A2 and a required field in column B, use =$B2="". Confirm the selected range begins on row 2.
Does formatting a blank cell put anything in it?
No. Conditional formatting changes appearance only. It does not enter a value, remove a formula, or convert a formula result into an actually empty cell.
Can I use this rule in Excel for the web or on a Mac?
Yes. Excel for the web and Excel for Mac support conditional formatting, including rules for blanks and formula-based rules. The menu and dialog presentation can differ from Excel for Microsoft 365 on Windows, so look for Conditional Formatting and New Rule in the Home tab or ribbon.
Related
- Dynamic Calendar Excel Template: Build One That Updates
- Dynamic Checklist in Excel: Build One That Updates
- Excel Dependent Dropdowns: How to Create Linked Lists
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Calculate Average Hours in ExcelTo calculate average hours in Excel, enter each duration as a time value, then use =AVERAGE(B2:B6) for durations…
- Excel Table Names: Where to Find, View, and Change ThemTo find an Excel table’s name, click any cell inside the table and open the contextual Table Design…
- Remove Formatting in Microsoft Word: A Step-by-Step GuideTo remove formatting in Microsoft Word, select the text, then choose Home > Clear All Formatting (the A…
- How to Run Goal Seek in Excel to Find a Target ValueTo run Goal Seek in Excel for Microsoft 365 on Windows, select the cell containing the formula you…
- Create Labels in Word With Mail Merge: Step-by-StepTo create a sheet of address labels in Word, open the Mailings tab, choose Start Mail Merge >…
- Excel Text to Columns: How to Split DataTo change text to columns in Excel for Microsoft 365 on Windows, select the cells, then choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.