Excel Zero Conditional Formatting: Highlight Zero Values

How-to basics · Updated November 18, 2026

To highlight zero values in Excel, select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Equal To, enter 0, choose a format, and select OK. For a rule that highlights numeric zeros but leaves genuinely blank cells alone, use a formula-based conditional formatting rule instead.

What zero conditional formatting does

Conditional formatting changes a cell’s appearance when its value meets a rule. It does not replace the value, alter a formula, or change what is stored in the workbook. A zero can therefore stand out with a fill color, font color, bold text, or another available format while remaining the number 0 for calculations, sorting, and charts.

This is useful when zero has a meaningful interpretation: no inventory remains, a balance has been paid, a task has no hours recorded, or a calculation has reached its target. It can also help readers distinguish zero from a missing entry. That distinction matters: zero means a measured or calculated amount is none, while a blank may mean the information has not been entered.

Apply the built-in Equal To rule

For a straightforward range, Excel’s built-in rule is usually the quickest option. In Excel for Microsoft 365 on Windows:

  1. Select the cells to check. To apply the rule to a column, select the data cells rather than the heading.
  2. Go to Home > Conditional Formatting > Highlight Cells Rules > Equal To.
  3. Enter 0 in the value box.
  4. Choose one of the preset formats, or select Custom Format to set a fill, font, border, or number format.
  5. Select OK and confirm that the intended cells are highlighted.

The rule applies to the selected cells, not automatically to every cell in the worksheet. If you select C2:C50 before creating it, Excel evaluates the values in that range. To include future rows, select a suitably larger range when you create the rule or adjust the rule’s Applies to range later.

Excel for Mac has the same basic Conditional Formatting options on the Home tab, although dialog details can differ slightly by release. Excel for the web also offers conditional formatting, but the available controls can vary; if a rule is difficult to edit there, open the workbook in the desktop app.

Use a formula to exclude blank cells

A simple Equal To 0 rule is convenient, but blank-looking cells can complicate the result. In Excel formulas, a reference to an empty cell can compare as zero in some tests. Also, a cell may look empty because a formula returns an empty string. If blanks must never be highlighted, a formula rule gives you more control.

With the range A2:A100 selected and A2 as its upper-left cell, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter this formula, choose the desired format, and confirm:

=AND(ISNUMBER(A2),A2=0)

This rule returns TRUE for a numeric zero in A2 and FALSE for a number such as 5 or for an empty cell. ISNUMBER ensures that the rule targets actual numeric values, including numeric results returned by formulas, rather than text that merely looks like a zero. Excel adjusts the relative A2 reference for each cell in the selected range, so A3 is checked on the next row.

The reference in the formula must match the top-left cell of the selected Applies to range. For example, if the selected range is D5:D40, use D5 in the rule. If you use the wrong starting row or column, the formatting may appear shifted because Excel evaluates each cell using the relative reference pattern.

Zeros from formulas, text, and blanks

Conditional formatting evaluates a cell’s result, not just what someone typed. If a cell contains a formula that calculates the numeric value 0, the built-in Equal To rule and the numeric formula rule can highlight it. The formula remains intact; changing the input cells may cause the result to stop being zero and the formatting to disappear.

Text that looks like zero is different. A cell containing the text character 0 may not behave like the number 0 in calculations or in every conditional-formatting comparison. If the data comes from an import, a leading apostrophe or text-formatted column may have stored values as text. The numeric-only formula rule deliberately does not flag text zeros. Convert such entries to numbers if they are meant to represent numeric values, or build a separate rule if text zeros should also be called out.

There is another distinction between a truly empty cell and a formula that returns an empty string. Both can look blank on the sheet, but the second cell still contains a formula. ISNUMBER excludes both because neither is a numeric zero. This makes the formula rule useful for reports where formulas leave unused rows visually empty.

Check whether a displayed zero is really zero

Number formatting can make a nonzero value appear as 0. For example, a small decimal may display with no decimal places, or a currency value may be rounded to the nearest whole unit. Conditional formatting based on equality to zero ordinarily tests the underlying value, not the rounded text shown on screen. A value such as 0.4 can display as 0 with zero decimal places but will not meet a true-zero rule.

To investigate, select the cell and look at the formula bar, or temporarily increase the displayed decimal places from the Number controls on the Home tab. If the underlying value is a small positive or negative amount, decide whether the report should highlight only exact zeros or values that round to zero at the chosen precision. Those are different business rules, so do not change the condition until you know which one the worksheet needs.

Likewise, a zero may be stored correctly but hidden by the cell’s number format. If the cell does not visibly show a zero, inspect its number format and the workbook’s display settings before assuming the conditional-formatting rule failed. Formatting and conditional formatting are separate layers: one controls how a value is displayed, and the other applies a style when a test is met.

Choose a format that communicates clearly

Use a style that makes zero noticeable without making the rest of the sheet harder to read. A pale fill or a contrasting font color often works well for a large data table. A strong red fill may imply an error or warning, so reserve it for cases where zero genuinely signals a problem. For a paid balance or completed target, a neutral or positive color may be more appropriate.

Consider accessibility and printing as well. Do not rely on color alone if readers need to interpret the sheet in grayscale or may have difficulty distinguishing similar colors. A border, bold type, or an adjacent status label can reinforce the meaning. Keep the format consistent across comparable tables so that readers do not have to infer a different meaning for the same color in each section.

Manage the rule’s range and priority

When a rule works in one area but not another, inspect where it applies. Select a cell in the affected range and use Home > Conditional Formatting > Manage Rules. In the rules manager, check the Applies to reference and the rule’s condition. Depending on the current selection and the Show formatting rules for setting, the manager may display rules for the selection or the worksheet.

Multiple conditional-formatting rules can apply to the same cells. Excel evaluates rules in an order shown in the rules manager, and the Stop If True option can prevent later rules from being applied when an earlier condition is met. If a zero has an unexpected color, another rule may be overriding or interacting with the zero rule. Review the order and formats before creating a duplicate rule.

If the range is an Excel table, consider whether the rule covers the table’s data rows and whether new rows inherit the formatting as expected. After extending a range, verify the Applies to field rather than assuming the rule expanded. A clear range, such as the whole data column below its heading or the table’s data area, makes later maintenance easier.

Common problems and practical checks

  • No cells change: Confirm that the values are numeric zeros, the rule covers the cells, and the selected format is visibly different from the existing style.
  • Blank cells are highlighted: Replace the basic equality rule with the ISNUMBER formula rule, using the correct top-left reference.
  • Only some zeros are highlighted: Check for text-formatted values, formulas returning text, a misaligned Applies to range, or another rule with priority.
  • Values that look like zero are missed: Increase displayed decimal places to see whether the stored value is actually a small nonzero number.
  • The rule changes the wrong cells: Recheck the formula’s relative reference and its relationship to the upper-left cell of the Applies to range.

A useful first diagnostic is to inspect a representative cell’s actual value and number format, then check the rule manager. This separates data problems from rule-configuration problems. If the worksheet is shared, also ask whether someone has pasted formatting over the cells or changed the conditional-formatting range.

Examples of when to highlight zero

For an inventory list, highlighting a numeric zero in the stock column can identify items that need replenishment. For a project tracker, zero hours remaining might mean a task is complete, but a blank could mean nobody has estimated it yet. The numeric-only rule preserves that distinction. For a reconciliation sheet, zero difference can mean two totals agree; using a calm, consistent format can make matching rows easier to scan.

In each case, confirm what zero means before choosing a color. A zero sales figure might be valid for a quiet day, while a zero available balance could require attention. Conditional formatting signals a condition; it cannot determine whether that condition is good, bad, or incomplete. A brief legend or a clear column heading can prevent readers from mistaking the highlight for an error indicator.

Frequently Asked Questions

How do I highlight zero but not blank in Excel?
Select the range, create a formula-based rule, and use the ISNUMBER test shown above with a reference to the selected range’s top-left cell. It highlights numeric zeros while leaving empty cells and text values unformatted.

Will conditional formatting highlight a formula result of zero?
Yes. A formula that returns the numeric value 0 can meet a zero condition. If the formula returns text that looks like zero, it is not the same as a numeric zero and may need separate handling.

Can I highlight zero values across an entire worksheet?
You can apply a rule to a large range, but selecting every cell can make the rule harder to manage and may affect performance in a complex workbook. It is usually clearer to apply it to the relevant data columns or table area.

Why does Excel highlight a value that is not exactly zero?
It may be a blank treated like zero by the rule, a displayed value rounded to zero, or a cell covered by another conditional-formatting rule. Check the stored value, number format, Applies to range, and rule priority.

Does conditional formatting change the cell’s value?
No. It changes the cell’s appearance when a condition is met. The underlying value or formula remains unchanged, and calculations continue to use that underlying content.

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.