To highlight zero values in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Equal To, enter 0, and choose a format. The options below cover that quick rule, formula-based exceptions, whole-row alerts, and ways to change how zero appears; they are useful for anyone reviewing reports, inventories, budgets, or formula-driven sheets.
1. Highlight cells equal to zero with the built-in rule
Select the numeric range, such as D2:D500, and choose Home > Conditional Formatting > Highlight Cells Rules > Equal To. Enter 0 in the dialog, choose a preset or custom format, and select OK; Excel applies the rule to the selected range, including numeric zeros returned by formulas. This is the fastest choice when every zero in that range deserves the same attention, but check the result if the selection includes blank cells, because a formula rule can be more precise about what counts as zero.
2. Highlight numeric zeros without coloring blank cells
For a range with empty cells, use a formula rule to make the condition explicit: with numeric values in D2:D500 and D2 at the top-left of the selected range, use =AND(ISNUMBER(D2),D2=0). Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter the formula, and set the format. ISNUMBER excludes genuinely empty cells and formulas that return an empty string, while still matching a numeric zero typed into a cell or returned by a calculation.
3. Shade an entire row when its value is zero
To draw attention to a record rather than just its value, select the table rows you want formatted, for example A2:G100. Assuming the status or quantity to check is in column D, with row 2 as the first data row, use =AND(ISNUMBER($D2),$D2=0) in a formula-based conditional formatting rule. The dollar signs lock the check to column D while leaving the row number relative, so each row is evaluated against its own D cell and the formatting extends across columns A through G.
4. Mark zeros produced by formulas, not typed zeros
Sometimes a calculated zero signals a result that needs checking, while a manually entered zero is intentional; ISFORMULA can separate the two. With results in D2:D500 and D2 as the first cell in the selected range, use =AND(ISFORMULA(D2),ISNUMBER(D2),D2=0) as the conditional formatting formula. It highlights only cells containing formulas that currently return the numeric value 0, and does not flag a typed zero or a formula returning blank text.
5. Highlight zero only for a particular status
A zero can be important only in a specific workflow state, such as a closed order that still has a remaining balance. Assuming order status is in C2:C100 and the balance is in D2:D100, select the relevant rows and use =AND(ISNUMBER($D2),$D2=0,$C2="Closed"). The rule checks both the balance and the exact status text, so a zero on an open order is left alone; change Closed to the wording actually used in your status column, including its spelling.
6. Use different formats for negative, zero, and positive values
For a reconciliation or variance column, three distinct formats make direction immediately visible: for example, red for negative, amber for zero, and green for positive. With numeric values in D2:D500, create three formula rules using =AND(ISNUMBER(D2),D2<0), =AND(ISNUMBER(D2),D2=0), and =AND(ISNUMBER(D2),D2>0), choosing a separate format for each. These conditions do not overlap for ordinary numeric values, and ISNUMBER keeps empty cells from being treated as zero or positive in the rules.
7. Catch values that are almost zero
Calculated amounts may be extremely close to zero without being exactly zero because spreadsheet calculations can retain decimal precision beyond what a cell displays. For currency values in D2:D500 displayed to two decimal places, with D2 as the first selected cell, use =AND(ISNUMBER(D2),ABS(D2)<0.005,D2<>0) to flag nonzero amounts smaller than half a cent in either direction. This is a tolerance check, not an exact-zero rule, so choose the threshold to match the precision and rounding policy of your data rather than copying 0.005 for every kind of measurement.
8. Flag a zero when another value says it should not be zero
In inventory, a zero stock count may be expected for an item with no demand, but suspicious when a positive quantity is expected. Assuming available stock is in D2:D100 and expected quantity is in E2:E100, select the rows to format and use =AND(ISNUMBER($D2),$D2=0,ISNUMBER($E2),$E2>0). This highlights only rows where the available amount is numeric zero and the expected amount is numeric and positive, making it a practical exception rule rather than a blanket warning for every zero.
9. Display zero as a dash or leave it visually blank
If a report should retain zero values for calculations but show a dash, select the cells, press Ctrl+1, choose Number > Custom, and enter #,##0.00;[Red]-#,##0.00;"—";@; the sections specify positive values, negative values, zero, and text, respectively. This is number formatting rather than conditional formatting: the stored zero remains available to formulas, but the display is clearer in a report with two-decimal figures. To suppress every zero on a worksheet instead, Windows has File > Options > Advanced > Display options for this worksheet > Show a zero in cells that have zero value; on Mac, look under Excel > Settings > View for the Show zero values setting.
10. Check rule scope and priority when a zero format seems wrong
When a zero does not change appearance, or receives an unexpected color, open Home > Conditional Formatting > Manage Rules and inspect the Show formatting rules for setting and each rule’s Applies to range. A rule may apply to the wrong cells, use a formula reference that does not match the top-left cell, or conflict with another rule that formats the same cells; correct the range or priority before adding yet another rule. In particular, Stop If True can prevent lower-priority rules from applying after a higher-priority rule matches, so review that setting when overlapping conditions are intentional.
How to Choose
Start with the simplest rule that expresses the decision you actually need. If all numeric zeros in a compact range should stand out, use Equal To and enter 0; if blank cells or formulas returning empty text are in the selection, use the ISNUMBER formula rule instead. For a record-level warning, apply a formula to the full row range and lock the check column with a dollar sign, as in $D2, so the formatting follows the row without shifting the tested column.
Next, decide whether zero is meaningful by itself or only in context. A zero balance may be normal for an open item but an exception for a closed one, while an empty inventory count is concerning only when demand is positive. Add the relevant status, expected quantity, or other condition to the formula rather than coloring every zero and training readers to ignore the signal. Use a near-zero tolerance only when the displayed precision hides meaningful small values, and set that threshold to the units and rounding rules in the workbook.
Finally, distinguish an alert from a presentation choice. Conditional formatting draws attention to values; a custom number format changes how values look without changing the underlying data, and the worksheet zero-display setting suppresses zero display broadly. If formulas, charts, totals, or exported data rely on zeros, hiding them can confuse readers even though the values remain in the cells. Check the Applies to range and rule priority before publishing a sheet, especially when several rules cover the same cells.
Frequently Asked Questions
Does conditional formatting treat a formula result of 0 as zero?
Yes. A formula that returns the numeric value 0 can match an Equal To 0 rule or a formula rule checking that the cell equals zero. A formula that returns an empty string is text, not numeric zero, and the ISNUMBER-based examples exclude it.
How do I highlight zero but not blank cells?
Use a formula rule such as =AND(ISNUMBER(D2),D2=0), with D2 as the top-left cell of the selected range. ISNUMBER makes the condition specific to numeric values, so an empty cell is not highlighted as a zero.
Can I format an entire row based on a zero in one column?
Yes. Select the full row area to format, such as A2:G100, and write the rule using an absolute column and relative row, for example =AND(ISNUMBER($D2),$D2=0). The dollar sign keeps the test in column D as Excel evaluates each row.
Will hiding zero values change calculations?
No. A custom number format or the worksheet setting that hides zero display changes what is shown, not the stored value, so formulas still calculate with zero. Conditional formatting also changes appearance only; it does not replace or remove the cell value.
Why does my zero rule color the wrong cells?
Check the rule’s Applies to range, the first-cell reference in its formula, and the order of overlapping rules in Manage Rules. A formula written for D2 must be aligned with the top-left cell of the rule’s range, and Stop If True may block later formatting rules.
Final Thoughts
For a quick zero alert, use Equal To 0; for reliable handling of blanks, formula results, or whole-row exceptions, use a formula rule matched to the worksheet layout. Choose a display format instead when the goal is a cleaner report rather than a warning, and always confirm the rule range before sharing the workbook.
Related
- Excel Knowledge for Data Analysis: Skills That Matter
- Excel for Data Analysis: What It Can and Can’t Do
- Excel Kata for Data Analysis: Practice With Purpose
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Calendar Dropdown: How to Add a Date PickerTo add a calendar picker in Google Sheets, select the cells, choose Data > Data validation > Add…
- Excel Freeze Panes: Repeat Rows on Every Printed PageNo: Excel’s Freeze Panes setting keeps rows or columns visible while you scroll, but it does not make…
- Does Excel Online Have Goal Seek? What to Use InsteadNo. Excel for the web does not currently include the Goal Seek command, so you cannot run a…
- Do Excel Cells Automatically Resize?No. In Microsoft Excel for Microsoft 365 on Windows, columns do not automatically widen and rows do not…
- Google Sheets IF Not Empty: Formulas and Examples GuideTo make Google Sheets do something when a cell is not empty, use an IF test such as…
- How to Change the Default Date Format in Google SheetsTo change the default date format in Google Sheets, open the spreadsheet and choose File > Settings, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.