Conditional Formatting for Empty Cells in Excel Explained

To highlight empty cells in Excel for Microsoft 365, select the range, choose Home > Conditional Formatting > New Rule, and use the blank-cell rule or a formula. If your selection begins at A1, the formula =A1="" highlights cells that are blank or return an empty string; use =ISBLANK(A1) when you want to highlight only genuinely empty cells.

What Excel means by an empty cell

“Empty” can mean two different things in a spreadsheet, and that distinction explains many conditional-formatting surprises. A cell may be genuinely empty because nothing has been entered in it. Or it may contain a formula whose displayed result is an empty string, so it looks blank even though the cell contains a formula.

For example, a formula used to hide a result until an input is provided might display nothing. The cell still contains a formula, so Excel does not consider it empty for every purpose. A cell containing a space is not empty either, even though the space may be hard to see. Conditional formatting needs to test the kind of “empty” that matches your task.

Genuinely empty: Nothing is stored in the cell. The ISBLANK function can identify this condition.
Looks blank: The cell is empty or its formula returns an empty string. A comparison with an empty string is generally the useful test.
Contains invisible characters: A space or other character is present. The cell may look empty but will not pass a true-blank test.

Use Excel’s built-in Blanks rule

For a straightforward highlight, Excel has a built-in conditional-formatting rule that avoids writing a formula. Select the cells you want to check, then follow these steps in Excel for Microsoft 365 on Windows:

Choose Home > Conditional Formatting > New Rule.
Select Format only cells that contain.
In the rule description, choose Blanks from the available cell-condition options.
Choose Format, set the fill, font, border, or other appearance you want, and select OK.
Select OK again to create the rule.

The rule applies to the selected range and updates as cell contents change. If you fill a previously blank cell, it stops receiving the blank-cell format. If you clear its contents, the format is applied again. This is usually the simplest choice when you only need to mark cells Excel treats as blank and do not need special logic.

To remove the rule later, select the relevant cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Choose Clear Rules from Entire Sheet only if you intend to remove conditional-formatting rules throughout that worksheet.

Use a formula when the definition matters

A formula rule gives you more control over what counts as empty and lets you combine the blank test with other conditions. Select the target cells, go to Home > Conditional Formatting > New Rule, and choose Use a formula to determine which cells to format. Enter the test, choose Format, and confirm the rule.

Suppose the range is B2:F100. Because B2 is the upper-left cell of the selection, make the formula refer to B2. The reference should normally be relative, without dollar signs, so Excel can adjust it for each cell in the selected range. In this layout, use =B2="" to highlight cells that are genuinely empty or evaluate to an empty string.

If the same selected range should match only cells that contain nothing at all, use =ISBLANK(B2). This will not match a formula cell that displays an empty string, nor a cell containing a space. In a formula-based rule, Excel evaluates the reference relative to each cell in the Applies to range, so the starting reference must correspond to that range’s upper-left cell.

For example, if you select D5:D50 but write a formula referring to D1, Excel evaluates the wrong row for the first cell. Check the Applies to field in Home > Conditional Formatting > Manage Rules if the range or reference seems misaligned. The formula’s first reference and the first cell in Applies to need to agree.

Highlight missing entries across a row

Sometimes the goal is not to color each blank cell, but to flag a record when a required field is missing. A formula rule can check one column while formatting an entire row. This is useful for signup lists, inventories, and intake sheets where a missing identifier makes the rest of a record difficult to interpret.

Assume records occupy A2:D100 and column A contains a required ID. Select A2:D100, create a formula-based rule, and use =$A2="". The dollar sign fixes the test to column A while the row number remains relative. Consequently, each row is formatted according to its own ID cell, rather than every cell being tested independently. Choose a noticeable fill or border, but avoid a color so strong that it obscures other information.

If you only want to format the other fields when the ID is missing, select B2:D100 instead. Keep the same column-anchored logic: the check still refers to column A, and the row adjusts as Excel evaluates each row in the selected range. Always match the row number in the reference to the first row of the Applies to range.

Deal with cells that look blank but are not

A blank-looking cell may contain a space, an apostrophe, or a formula. First select the cell and inspect the formula bar. If a formula is present, decide whether its empty result should count as missing; if a space was entered, remove it or use a test designed for that data-cleaning problem. Conditional formatting changes appearance; it does not delete or repair the underlying content.

For a range beginning at A2 where spaces are the concern, the formula =LEN(TRIM(A2))=0 treats an empty cell, an empty-string result, and ordinary leading or trailing spaces as having no text after trimming. TRIM also collapses repeated ordinary spaces between words, so this test is not a general-purpose way to preserve and inspect spacing in text. It does not remove every possible invisible character, such as a nonbreaking space copied from a web page.

When data comes from outside sources, it may be better to clean it before applying a visual rule. Find and Replace can remove known unwanted characters, while a cleaning formula or a deliberate import process can address recurring issues. If the exact character is uncertain, inspect a few affected cells and test the source data rather than assuming that every blank-looking cell is truly empty.

Common reasons the rule misses cells

When a rule appears not to work, the cause is often its range, reference, or formatting rather than a defect in Excel. Review the following points:

Wrong Applies to range: Open Home > Conditional Formatting > Manage Rules and confirm that the rule covers the intended cells. In the Rules Manager, set the display to the current selection or worksheet as needed.
Reference starts on the wrong row or column: Formula references are interpreted relative to the top-left cell of Applies to. A mismatch can shift the test and color neighboring cells.
Absolute references lock the wrong cell: Dollar signs are useful when one column or row must remain fixed, as in the required-ID example. Locking both the row and column by mistake can make every cell test the same single location.
Another rule overrides the appearance: In Manage Rules, review rule order and the Stop If True setting. A higher-priority rule may apply a different format before the blank rule can be seen.
The chosen format is difficult to notice: A font color may match the cell fill, or the formatting may change a property you are not looking at. Choose a clear fill or border and inspect the result on a known blank cell.
The cell is not actually blank: A formula, space, or hidden character can make a blank-looking cell fail a true-empty test. Check the formula bar and choose the test that reflects the data.

Choose between a built-in rule and a formula

Use the built-in Blanks condition when the requirement is simply “format blank cells.” It is easier to create and maintain, especially for a basic input template. Use a formula when “blank” needs a specific definition, when a row depends on a required field, or when the rule must combine multiple conditions.

There is also a practical difference between highlighting empty cells and preventing them. Conditional formatting is a visual signal: it does not stop someone from leaving a required field blank, and it does not add a value. If blank entries must be blocked or controlled, consider Data Validation, instructions near the input area, or a review process in addition to formatting. Data Validation settings depend on the kind of entry you need to require, so test the setup with realistic inputs.

Formatting can also become hard to manage when many overlapping rules are added over time. Keep the Applies to ranges focused, use descriptive worksheet labels to explain the highlight, and remove obsolete rules. If a workbook is shared, tell collaborators what the color means; a colored blank might indicate a required value, a pending decision, or simply an unused optional field, and those meanings should not be confused.

Excel for Mac and Excel for the web

In current desktop versions of Excel for Mac, conditional formatting is also available from the Home tab, and the New Rule dialog provides rule types for blanks and formulas. Dialog wording and layout can vary slightly by release, but the same core approach applies: select the range, create the rule, specify the condition, and choose the format. The formulas and relative-reference behavior described above are the same.

Excel for the web supports conditional formatting, but its interface may offer a different set of controls or a more compact rule editor than desktop Excel. If a particular formula-rule option or rule-management control is not available in the web interface you are using, open the workbook in the desktop app to create or inspect the rule. In either version, verify the target range and test the rule by entering and clearing a value in a sample cell.

Frequently Asked Questions

Can conditional formatting highlight blank cells automatically?
Yes. Create a Blanks conditional-formatting rule for the selected range. Excel updates the formatting when a cell becomes blank or receives a value.

Does ISBLANK detect a formula that displays nothing?
No. A cell containing a formula is not truly empty, even if its displayed result looks blank. Use a comparison with an empty string when that result should also be highlighted.

Why is a blank cell not changing color?
Check the Applies to range, confirm that the rule’s reference matches the range’s top-left cell, and inspect for a formula or hidden space. Also review other rules that may apply a competing format.

Can I highlight an entire row when one cell is blank?
Yes. Select the rows or row range you want formatted, then create a formula rule that checks the required column with a fixed column reference and a relative row reference. Make the reference’s row match the first selected row.

Does conditional formatting remove spaces or fill missing values?
No. It changes how cells look; it does not clean data or enter values. Remove unwanted characters or supply required information separately.

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.