Conditional Formatting Only If a Cell Has Data

Yes. In Excel for Microsoft 365, select the cells to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and use a test such as =A2<>"". This applies formatting only when the corresponding cell is not empty; the right test depends on whether you also want to count formulas that display nothing, spaces, or zero as data.

What conditional formatting checks

Conditional formatting changes how a cell looks when a rule’s condition is true. It can add a fill, font color, border, or other available format, but it does not enter data, delete data, or change the value stored in a cell. A rule can check the cell being formatted or use a formula to check another cell, such as a status column that controls whether an entire row is highlighted.

“Has data” can mean different things in a spreadsheet. A cell may be genuinely empty, contain a number or text, contain a formula whose displayed result is an empty string, or contain a space that is difficult to see. Decide which cases count before choosing a rule. This distinction is why a rule that works for ordinary blank cells can still color a cell that appears empty.

For a straightforward list, the built-in No Blanks condition is often enough. A formula-based rule gives you more control when you need to treat formula results, spaces, or related cells in a particular way.

Use the built-in No Blanks rule

For a simple range, Excel can apply formatting to cells it recognizes as nonblank without requiring you to write a formula. In Microsoft 365 for Windows:

Select the cells to format, such as C2:C100.
Choose Home > Conditional Formatting > New Rule.
Select Format only cells that contain.
In the rule description, choose Blanks or No Blanks as appropriate. Choose No Blanks to format cells with content.
Select Format, choose the appearance you want, and select OK to create the rule.

The exact layout of the New Formatting Rule dialog can vary slightly with updates, but the rule type and blank-condition choices are available in desktop Excel for Microsoft 365. This option is convenient when the same cell both holds the data and receives the formatting. If you need to test a different cell, or distinguish a formula result of an empty string from a truly empty cell, use a formula rule instead.

Create a formula rule for cells with content

To control formatting with a formula, select the range first and make the formula refer to the top-left cell of that selection. For example, with data in A2:A100 and A2:A100 selected, create a formula rule using =A2<>"". It returns TRUE for a cell whose value is not an empty string, so Excel applies the chosen format to populated entries as the rule is evaluated down the selected range.

In Windows desktop Excel, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula in the formula box, choose Format to set the appearance, and select OK. Confirm that the Applies to range shows the intended cells; you can review or edit this under Home > Conditional Formatting > Manage Rules.

For a range beginning at a different cell, change the reference to its top-left cell. For example, with D5:D50 selected, use =D5<>"". Keep the reference relative when each cell should test itself. If the selection starts at D5 but the formula says A2, Excel will evaluate the wrong relative position and the results may appear shifted.

Choose how to treat formulas, spaces, and zero

Not all cells that look blank are empty in the same technical sense. A formula might return an empty string, often written as two quotation marks, while a cell containing one or more spaces has text in it. A cell may also contain the number zero, which is a real value even if the worksheet’s number format hides it.

Ordinary values, including zero: With A2:A100 selected, use =A2<>"". A number such as 0 is not equal to an empty string, so it qualifies as data.
Any cell that is not truly empty: With A2:A100 selected, use =NOT(ISBLANK(A2)). ISBLANK is FALSE when a cell contains a formula, even if that formula displays an empty string.
Ignore cells containing only regular spaces: With A2:A100 selected, use =LEN(TRIM(A2))>0. TRIM removes ordinary leading and trailing spaces and reduces repeated internal spaces, so a cell made only of regular spaces will not qualify.

These tests are not interchangeable. The first test treats a formula returning an empty string as blank for this comparison, while the ISBLANK test treats the formula cell as occupied. TRIM does not remove every possible invisible character, including some nonbreaking spaces copied from websites. If imported text still triggers the rule, inspect or clean the source data rather than assuming every invisible character is an ordinary space.

Format a row when one cell has data

A common request is to shade an entire record row only when its key field is filled in. Suppose column A contains a name and columns B through G contain the rest of each record. Select B2:G100, then create a formula rule using =$A2<>"". The dollar sign fixes the test to column A, while the row number stays relative so each row checks its own name cell.

The same principle works if column A contains an order number, project name, or other field that indicates a record exists. Select the full range that should receive the formatting, then anchor the test column with a dollar sign. If you want to test a specific fixed cell instead, lock both its column and row; otherwise, let the row reference adjust as the rule moves down the range.

To require more than one condition, combine tests with AND. For instance, with B2:G100 selected, and with column A holding a name and column B holding a status, use =AND($A2<>"",$B2="Complete") to format a row only when it has a name and its status is Complete. Check that the status text matches the worksheet value; spelling differences or extra spaces can prevent the condition from matching.

Check the applies-to range and references

A correct formula can still produce confusing results if the selected range and references do not line up. Excel evaluates a formula rule relative to the upper-left cell in its Applies to range. If that range begins at row 2, a row-based formula should generally refer to row 2; if it begins at row 5, use row 5 in the formula’s starting reference.

When a rule formats the wrong cells, open Home > Conditional Formatting > Manage Rules and inspect both the formula and Applies to. In the dropdown at the top of the Rules Manager, you may need to show rules for the current selection or the worksheet. Look for overlapping rules, an unintended range, or a reference that has been locked with dollar signs when it should move.

Copying and pasting cells can also copy conditional formatting, creating duplicate rules or expanding the applied range. If the worksheet has several similar rules, edit or remove unnecessary ones in Manage Rules and keep one rule covering the complete intended range where practical. The Conditional Formatting Rules Manager is also useful for changing the format without rebuilding the condition.

Common problems and useful distinctions

Why is an apparently blank cell colored? It may contain a formula returning an empty string, a space, or another invisible character. Check the formula bar or use a test that matches your definition of blank. A cell can also be colored because the rule’s Applies to range includes it and the formula is evaluating a neighboring cell.

Why is zero formatted? Because zero is data, not an empty cell. If the goal is to exclude zero as well as blanks, use a condition that tests for a positive or nonzero value, depending on the data. For example, with A2:A100 selected, =AND(A2<>"",A2<>0) excludes both an empty-string result and numeric zero. It is not appropriate if zero is meaningful and should receive formatting.

Why does a rule seem to do nothing? Verify that the chosen format is visually noticeable, the cell is in the Applies to range, and the formula is valid for the range’s first cell. Also check for another conditional formatting rule that may apply a competing format. Rules can coexist, and their order or settings can affect the final appearance.

Conditional formatting is a visual cue, not a data-validation or data-cleaning tool. It will not prevent someone from entering a value in a supposedly empty row, and it will not remove spaces or correct formulas. If the distinction between blank and nonblank matters for reporting, calculations, or later data import, clean or validate the underlying data separately.

Excel for Mac and Excel for the web

The core formula approach works in desktop Excel for Mac as well as Microsoft 365 for Windows. On Mac, use the Home tab’s Conditional Formatting controls to add a rule; dialog wording and screen layout can differ slightly from Windows. Make sure the formula is entered as a conditional formatting formula and that the applies-to range begins where the formula expects it to.

Excel for the web also supports conditional formatting and formula-based rules, though the available controls and rule-management interface may differ from the desktop application. If a complex workbook has many overlapping rules or needs detailed troubleshooting, opening it in desktop Excel can make the rule manager easier to inspect. Regardless of platform, the key ideas remain the same: select the correct range, use the right top-left reference, and choose a test that matches what “has data” means for your sheet.

Frequently Asked Questions

Can I highlight a cell only when it is not blank?
Yes. Select the target range and use Home > Conditional Formatting > New Rule. Choose the built-in No Blanks condition, or use a formula such as =A2<>"" when A2 is the first cell in the selected range.

Does conditional formatting count a formula that displays a blank?
It depends on the test. A comparison with an empty string generally treats a formula returning an empty string as blank, while ISBLANK returns FALSE because the cell contains a formula. Choose the behavior that fits your worksheet.

How do I highlight a whole row only when a cell has data?
Select the rows’ full formatting range and use a formula that locks the test column but leaves the row relative. For example, if column A is the required field and the selected range starts in row 2, use =$A2<>"".

Will a cell containing zero count as having data?
Yes. Zero is a value, so a nonblank test normally includes it. If zero should not be formatted, add a separate zero check or choose a comparison that reflects the intended rule.

Can I ignore cells that contain only spaces?
For ordinary spaces, a formula using LEN and TRIM can exclude cells that contain no other text. TRIM does not handle every kind of invisible whitespace, so copied or imported data may require additional cleanup.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.