To highlight cells that equal zero in Excel, use the formula =A1=0 in a conditional formatting rule. Select the range, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter the formula, set a format, and click OK.
Why a Formula Instead of Built-In Rules?
Excel’s built-in Highlight Cells Rules include “Equal To,” which can highlight zeros with a few clicks. However, the formula approach gives you more control: you can highlight zeros only in certain columns, ignore blank cells that are often formatted as zeros, or combine zero with other conditions like a matching category. For example, to highlight zero sales only for the “North” region, you’d use =AND($B2="North",$C2=0). The formula method also lets you apply the rule to entire rows or dynamic ranges without re-selecting data.
How the Formula Works
Conditional formatting formulas are evaluated for the top-left cell of the selected range first, then Excel adjusts relative references as it moves across the range. If your selected range is B2:D10, entering =B2=0 will check each cell in that range against its own value. For a single column, use =B2=0 with range B2:B10. For a whole row, use a mixed reference like =$B2=0 to lock the column but allow the row to change, so the rule highlights the entire row when column B is zero. Remember that the formula must return TRUE for the cell to be formatted; any numeric comparison works.
Step-by-Step: Apply the Zero-Highlight Rule
- Select the range you want to format (e.g.,
A2:A100). - Go to Home > Conditional Formatting > New Rule.
- Choose “Use a formula to determine which cells to format.”
- In the formula box, enter
=A2=0(adjust to match your top-left selected cell). - Click “Format,” choose a fill color (e.g., yellow) and click OK.
- Click OK again to apply the rule.
On Mac, the path is the same: Home tab > Conditional Formatting > New Rule. In Excel for the web, select the range, then Format > Conditional formatting, and choose “Custom formula is” to enter the same formula.
Common Problems and Fixes
If the rule doesn’t work, check these:
- Wrong reference: The formula must match the top-left cell of your selected range. If you selected
C2:C50, use=C2=0, not=A2=0. - Text-formatted numbers: If cells contain text like “0” (apostrophe prefix), the formula
=A2=0returns FALSE. Use=VALUE(A2)=0or convert text to numbers. - Blank cells: Blank cells are not equal to zero, so they won’t be highlighted. If you want to highlight blanks as zeros, use
=OR(A2=0,A2=""). - Rule precedence: If multiple rules apply, the first rule in the list takes priority. Reorder rules via Conditional Formatting > Manage Rules.
- Entire row highlight: If you want the whole row colored when a cell is zero, select the entire row range (e.g.,
A2:D10) and use=$A2=0.
Alternatives: Built-In and Other Formulas
The built-in “Equal To” rule (Home > Conditional Formatting > Highlight Cells Rules > Equal To) works for simple cases but doesn’t ignore blanks or combine conditions. For more control, consider these formulas:
- Highlight zeros and blanks:
=OR(A2=0,A2="") - Highlight zeros in a column but only for rows where another column says “Yes”:
=AND($B2="Yes",A2=0) - Highlight zeros that are also less than the average:
=AND(A2=0,A2 - Highlight the first zero in each group:
=AND(A2=0,COUNTIF($A$2:A2,0)=1)
Costs and Versions
Conditional formatting with formulas is available in all modern desktop versions of Excel, including Excel 2010, 2013, 2016, 2019, 2021, and Microsoft 365. Excel for the web supports custom formulas as well, though the menu path differs slightly. There is no extra cost beyond your Excel subscription or license. Older versions like Excel 2007 also support this feature, but the interface may look different. No special add-ins are required.
Safety and Accuracy Tips
Conditional formatting does not change your data—it only changes the display. However, be careful when copying cells with conditional formatting: the rules may shift if you paste into a new range. To avoid errors, use absolute references where needed (e.g., $A$2) when you don't want the reference to move. Also, remember that conditional formatting rules are volatile: they recalculate when the sheet changes, which can slow down very large workbooks. If you have thousands of rules, consider using a simpler built-in rule or a helper column with a formula that returns TRUE/FALSE, then apply conditional formatting to that helper column.
Frequently Asked Questions
How do I highlight all zeros in a column including blanks?
Use the formula =OR(A2=0,A2="") with the range starting at A2. This treats blank cells as zeros for formatting purposes. If you only want actual zeros, stick with =A2=0.
Why does my formula not highlight zeros that are results of formulas?
If a cell contains a formula that returns 0, conditional formatting will still highlight it because the formula result is 0. However, if the formula returns an empty string "" or text, it won't match. Use =N(A2)=0 to treat text as 0, or check your original formula's output.
Can I highlight zeros only in a specific row?
Yes. Select the row range (e.g., A2:Z2) and use a formula like =A2=0. Because the range is a single row, the reference will adjust across columns. To lock the row, use =A$2=0.
How do I remove or edit a conditional formatting rule?
Go to Home > Conditional Formatting > Manage Rules. Select the rule you want to change or delete, then click Edit Rule or Delete Rule. You can also clear all formatting from a range with Home > Conditional Formatting > Clear Rules.
Does conditional formatting work in Excel for the web?
Yes, but the menu path is different: select the range, go to Format > Conditional formatting, and choose “Custom formula is” under the “Format cells if” dropdown. The formula syntax is the same, but you may need to use semicolons as argument separators depending on your locale.
Related
- Excel Blank Calendar Template: How to Get One
- Excel Biweekly Timesheet Template with Lunch
- X++ String Split: How to Use str2con and Other Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Divide in Excel: Formula and Keyboard TipsTo divide in Excel, type a forward slash (/) between the numbers or cell references in a formula,…
- How to Filter Dates by Quarter in ExcelTo filter dates by quarter in Excel, turn on the filter for your date column and choose the…
- Calculate Age in Excel from DOB with a Simple FormulaIf the date of birth (DOB) is in A2, enter =DATEDIF(A2,TODAY(),"Y") in another cell to return age in…
- How to Find the Minimum in Excel with the MIN FunctionTo find the smallest number in Excel, use the MIN function. With the numbers you want to check…
- Excel Autofill Keyboard Shortcuts for Fill Down and RightTo autofill a selected range in Excel, include the source cell and the cells you want to fill,…
- Excel SUMIF by Text Color: What Works and What Doesn’tExcel’s SUMIF function cannot directly test a cell’s text (font) color. To total values by color, use a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.