Excel Not Blank Conditional Formatting: How to Set It Up

How-to basics · Updated November 18, 2026

To apply conditional formatting to nonblank cells in Excel for Microsoft 365 on Windows, select the range, choose Home > Conditional Formatting > New Rule, select “Format only cells that contain,” and set the cell rule to “No Blanks.” For more control—especially when cells contain formulas that display empty text—use a formula rule such as =A1<>"", with A1 replaced by the top-left cell of the selected range.

Set up a basic no-blanks rule

The built-in No Blanks rule is the quickest choice when you want Excel to format cells that contain something. For example, you might shade completed entries in a tracking column or make filled-in cells stand out in an intake form. The formatting changes the appearance of the cells; it does not enter data, remove blanks, or change the underlying values.

  1. Select the cells to format, such as B2:B50. Selecting the range first helps Excel apply the rule to the intended cells.
  2. On the Home tab, choose Conditional Formatting > New Rule.
  3. In the New Formatting Rule dialog, choose “Format only cells that contain.”
  4. In the rule options, choose “No Blanks” as the condition. Depending on the dialog layout, you may need to change the first condition dropdown from “Cell Value” to “No Blanks.”
  5. Choose Format, set a fill, font, border, or other available formatting, and select OK to save the rule.

Afterward, cells in the selected range that Excel considers nonblank receive the chosen format. If you want to check or change the range later, open Home > Conditional Formatting > Manage Rules. In the rules list, check the “Applies to” range as well as the condition.

Use a formula when “not blank” needs a precise meaning

A formula-based rule is useful when you need to decide exactly what counts as filled. To create one, select the target range, choose Home > Conditional Formatting > New Rule, then select “Use a formula to determine which cells to format.” Enter a formula that evaluates to TRUE for cells to format, choose the appearance, and confirm.

For a range starting at A1, the formula =A1<>"" tests whether the cell’s value is not an empty string. Applied to A1:A20, it formats cells containing ordinary values and also treats a formula result of empty text as blank. The reference is relative, so each cell is checked in relation to its position.

If your selected range starts at B2, use B2 in the formula, not A1. Conditional-formatting formulas are written as if they were being evaluated for the top-left cell in the selected range. A mismatched starting reference can cause the rule to format the wrong cells or appear one row or column out of alignment.

A different test is =NOT(ISBLANK(A1)). Assuming the rule applies to A1:A20, it formats a cell if that cell is not truly empty; a formula in A1 counts as content even when the formula displays an empty string. For this reason, it may highlight cells that look blank. Choose this test only when the presence of a formula itself should count as nonblank.

What Excel means by a blank cell

“Blank” can mean different things to a person looking at a worksheet and to a formula. A truly empty cell has no value or formula. A cell containing a formula that returns an empty string, such as a formula designed to show nothing until data is entered, is not truly empty, even though it can look identical to one.

That distinction explains why ISBLANK and a comparison with an empty string can produce different results. If A1 contains a formula that returns empty text, =ISBLANK(A1) returns FALSE because a formula is present. In contrast, =A1<>"" returns FALSE for that displayed empty result. In a conditional-formatting rule applied to A1, the first test does not format the cell; the second does not either, because it evaluates as not different from an empty string.

Other contents that may look empty are not necessarily blank:

  • A cell containing one or more spaces contains text. A space can be difficult to see, but it can make a no-blanks test treat the cell as filled.
  • A cell containing zero is not blank. It may look empty if number formatting hides zero values, but the underlying value remains zero.
  • A cell with a formula returning 0 contains a formula and a result, even if a custom number format hides the zero.
  • A cell with an error value, such as #N/A, is not an ordinary blank. Formula-based tests should be checked with the errors and source data used in your workbook.

Before deciding that Excel’s rule is wrong, click a cell that appears empty and inspect the formula bar. A formula, a space, or a hidden value often explains the difference.

Ignore cells containing only spaces

If users may enter stray spaces and you want to format only cells with meaningful text, use a formula that removes ordinary leading and trailing spaces before checking the length. For a range beginning at A1, =LEN(TRIM(A1))>0 evaluates as FALSE for an empty cell or a cell containing only ordinary spaces, and TRUE for text that remains after trimming. For example, “ Ready ” is treated as “Ready” for this test.

TRIM does not remove every character that can look like a space. In particular, nonbreaking spaces copied from websites or other systems may remain. If a cell still gets formatted unexpectedly, inspect its contents or clean the source data rather than assuming all invisible characters are removed by TRIM. Also consider whether numbers should count as filled: LEN converts a value to text for the length check, so a numeric zero has a nonzero text length and is treated as populated.

Format a whole row when one cell is filled

To highlight an entire record when a particular column contains an entry, select the rows and columns to format, then make a formula rule that checks the relevant column. Suppose a table occupies A2:D100 and column A holds each record’s ID. Select A2:D100 and use =$A2<>"". The dollar sign fixes the test to column A, while the row number remains relative so each row checks its own ID. The rule formats the cells across a row when its A cell is nonblank, and leaves rows with an empty ID unformatted.

To check whether a different column is filled, change the locked column reference. For example, if column D contains a required status, use =$D2<>"" for the same A2:D100 selection. The starting row in the formula must match the first row in the selection. If you select A1:D99 but begin the formula with row 2, the rule’s checks will be offset.

Locking the column with a dollar sign is important when the format applies across multiple columns. Without it, a reference such as A2 can shift as Excel evaluates cells farther across the row, so the rule may test B2, C2, and D2 rather than consistently checking the ID in column A.

Choose between the built-in rule and a formula

For a straightforward range of cells, the built-in No Blanks condition is easy to set up and simple to review later. It is a good starting point when you want to highlight ordinary entered values and do not need a special definition of “filled.”

Use a formula rule when your logic depends on a particular column, when a whole row should respond to one cell, or when formulas that return empty text should be treated as blank. A formula also lets you ignore whitespace or combine conditions, though each added condition makes the rule harder to maintain. If other people use the workbook, a clear rule and a brief note about what qualifies as populated can prevent confusion.

Conditional formatting is only a visual signal. It does not prevent someone from leaving a required cell empty, and it does not validate whether the entered information is correct. If the goal is to guide or restrict data entry, consider using Data Validation alongside formatting, or use a separate check column that explicitly reports missing entries.

Fix rules that do not behave as expected

When a rule seems not to work, start by checking its scope. Open Home > Conditional Formatting > Manage Rules and inspect “Applies to.” A correct formula applied to the wrong range will still produce the wrong result. If you selected a new range after creating the rule, confirm that the rule includes that range and that its formula is aligned with the new top-left cell.

  • Nothing is highlighted: Confirm that a format was actually selected in the Format dialog, and check whether the formula returns TRUE for a cell that should be formatted.
  • The wrong row is highlighted: Compare the formula’s starting row with the first row of “Applies to.” For row-based rules, lock the test column but leave the row relative, as in =$A2<>"".
  • Cells that look empty are highlighted: Check for formulas, spaces, zeros hidden by number formatting, or other invisible content. Decide whether to test the displayed result, the actual cell contents, or meaningful text after trimming.
  • Some formatting does not appear: Another conditional-formatting rule may apply a competing format. In Manage Rules, review the rule order and any “Stop If True” settings. Rules can interact, so make a small test change or temporarily adjust a rule to identify the conflict.
  • The rule works in one section but not another: Look for separate rules with different “Applies to” ranges and inconsistent references. Editing one rule does not necessarily update every similar rule in the workbook.

Testing on a small sample range is often quicker than changing a rule across a large table. Include at least one truly empty cell, one normal entry, and one formula that returns an empty string if those cases occur in the real data.

Excel for Mac and Excel for the web

The core conditional-formatting approach is similar in current Excel for Mac: select the cells and use the Conditional Formatting controls on the Home tab to create a rule. Dialog wording and the placement of options can vary slightly by platform or update, so look for the equivalent “Format only cells that contain” or formula-based rule. The formulas and relative-reference principles described here are the same.

Excel for the web also supports conditional formatting, though its controls and rule-management interface may differ from the Windows desktop dialog. If a complex rule is difficult to edit in the browser, open the workbook in the desktop application and inspect the full rule list. Keyboard shortcuts are not necessary for these steps, and the ribbon path on Windows is the one given above.

Frequently Asked Questions

How do I highlight cells that are not blank in Excel?
Select the range, then choose Home > Conditional Formatting > New Rule > Format only cells that contain and select “No Blanks.” Set the format and confirm. Use a formula rule if formula-generated empty text should count as blank.

Why does Excel highlight a cell that looks blank?
The cell may contain a formula returning an empty string, spaces, a zero hidden by formatting, or other content. Click the cell and check the formula bar. A truly empty cell and a cell that merely displays nothing are not the same to every Excel test.

Should I use ISBLANK or compare the cell with an empty string?
Use ISBLANK when only cells with no value and no formula should count as blank. Use a comparison such as =A1<>"" when a formula returning empty text should also be treated as blank for the formatting decision.

Can conditional formatting highlight a row if one cell is filled?
Yes. Select the full table range and create a formula rule that locks the column to check while leaving the row relative. For A2:D100 with IDs in column A, =$A2<>"" highlights a row when its ID cell is nonblank.

Does “No Blanks” treat zero as blank?
No. A zero is a value, even when number formatting makes it appear empty. If your intention is to ignore zero, create a formula rule with a condition tailored to the values in your sheet rather than relying on the No Blanks setting.

Related

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.