Excel Is Not Zero: Filter, Count, and Test Cell Values

Errors & fixes · Updated November 21, 2026

In Excel, “is not zero” means a value is anything other than numeric 0. Use the not-equal operator <>=A2<>0 returns TRUE when A2 is nonzero and FALSE when it is zero. You can use the same comparison in formulas, filters, and conditional formatting, but blank cells and text values can affect the result.

What “not zero” means in Excel

Excel does not use the mathematical symbol ≠ as an operator in formulas. Its equivalent is the two-character operator <>, which means “not equal to.” Put it between the value or cell reference being tested and the value you want to compare it with.

For example, =A2<>0 tests the value in A2 against numeric zero. If A2 contains 8, the result is TRUE; if it contains 0, the result is FALSE. Negative numbers and nonzero decimals also meet the test: -4 and 0.01 are both not zero.

This is a logical test, so Excel returns TRUE or FALSE. Those results can be used directly or passed to another function, such as IF. The phrase “not zero” does not mean “positive”: a negative value is still not zero.

Use the not-equal operator in a formula

To check one cell, enter =A2<>0 in another cell and press Enter. In Microsoft 365 for Windows, you can select the cell, type the formula in the formula bar or directly into the cell, and press Enter. Excel displays TRUE or FALSE. On a Mac, the formula is identical; the keyboard and ribbon details may differ, but the comparison syntax does not.

To show a readable message instead, assume the value to check is in A2. Enter =IF(A2<>0,"Nonzero","Zero") in B2. If A2 contains 12, B2 displays Nonzero; if A2 contains 0, it displays Zero. Drag the fill handle from B2 down to apply the formula to other rows. A negative value such as -3 also produces Nonzero.

For a column of values, the row reference should usually remain relative. With values in A2:A20, entering =A2<>0 in B2 and filling down changes the test to A3, A4, and so on. If you need to keep a reference fixed while copying a formula, use dollar signs—for example, =$A$2<>0 always tests A2.

Filter out zero values

To display only records whose numeric value is not zero, use a number filter on the relevant column. Select a cell in the data, then choose Data > Filter if filter arrows are not already visible. Open the arrow on the number column, choose Number Filters > Does Not Equal, enter 0, and confirm. Excel hides rows whose values match zero; it does not delete them.

To show only zero values instead, choose Number Filters > Equals and enter 0. The filter choices are useful when you want to inspect a table without adding a helper column. Clear the filter from the column menu, or use Data > Clear to remove filters from the range. On Excel for Mac, the Data tab also provides filtering, though the placement and wording can vary slightly by release.

Check the results if the column includes blanks, formulas returning empty text, or numbers stored as text. Filtering and formulas do not always treat these entries like ordinary numeric zeros. If the goal is specifically “show numeric, nonblank values other than zero,” first clean or categorize those entries rather than assuming a simple zero filter handles every case.

Count or add values that are not zero

For a range containing only numeric values and no blanks, COUNTIF can count the entries that are not zero. With values 0, 12, -3, and 0.5 in A2:A5, enter =COUNTIF(A2:A5,"<>0"). The result is 3: the nonzero values are 12, -3, and 0.5.

To add those same nonzero values, with the same values in A2:A5, use =SUMIF(A2:A5,"<>0",A2:A5). The result is 9.5, because 12 + (-3) + 0.5 = 9.5. The criteria are written as text inside quotation marks because COUNTIF and SUMIF expect a criteria argument. The comparison operator itself is still <>.

These examples assume a clean numeric range. If the range contains blanks, text, or formulas that return an empty string, decide whether those entries should count before choosing a method. For more control, a helper column can classify each row with an IF formula, after which you can filter or count the classifications. That makes the treatment of unusual entries visible to anyone reviewing the workbook.

Highlight nonzero cells with conditional formatting

Conditional formatting can mark nonzero values without changing the underlying data. Select the cells to format, such as A2:A20, then choose Home > Conditional Formatting > Highlight Cells Rules > More Rules. Alternatively, choose Home > Conditional Formatting > New Rule, then select the formula-based rule option.

For a formula-based rule applied to A2:A20, use =A2<>0, choose a format such as a fill color, and confirm. A2 is written as a relative reference so Excel adjusts the row for each selected cell. If the selection starts at a different row, make the formula’s row match its first cell. For example, a selection beginning at C5 should use =C5<>0.

Conditional formatting changes only how qualifying cells look; it does not remove zeros or alter calculations. To highlight an entire row according to the value in column C, select the row range and use a formula with a locked column, such as =$C2<>0, adjusting the row number to match the first selected row. The dollar sign keeps the test in column C as the rule is evaluated across the row.

Blanks, empty strings, text, and errors

A genuinely blank cell is not the same thing as a cell containing numeric zero. In a direct comparison such as =A2<>0, Excel treats a blank reference like zero for this test, so it returns FALSE. That is often useful when blank means “no value yet,” but it can be surprising if you want to distinguish missing data from zero.

A formula that returns an empty string is a different case. For example, a cell containing =IF(B2="","",B2) looks blank but contains a formula. Because the result is text, a comparison with numeric zero can behave differently from a genuinely empty cell. If you want to require both a nonblank entry and a nonzero value, assume the cell being checked is A2 and use =AND(A2<>"",A2<>0). This returns FALSE for an empty cell or an empty-string result, and TRUE for ordinary nonzero numeric values.

Imported data can also contain the text character string “0” rather than the number 0. Text and numbers are different data types, and comparisons, filters, and calculations can behave differently depending on how data was imported or formatted. If a test seems inconsistent, check the cell’s actual value and data type; changing a cell’s number format alone does not convert text into a number. Convert the data appropriately before relying on numeric comparisons.

Finally, a cell containing an error such as #N/A does not produce a normal TRUE or FALSE result from a comparison; the error can flow into dependent formulas. Address the error separately if the data may contain errors. Do not treat “not zero” as meaning “valid”: a text label, blank-looking formula, or error may need its own rule.

Common mistakes and how to fix them

  • Using <> without a comparison value: a complete test needs both sides, as in =A2<>0.
  • Forgetting quotation marks in a criteria function: use =COUNTIF(A2:A5,"<>0"), not an unquoted criteria string.
  • Confusing nonzero with positive: the test includes negative numbers. To test for a positive value, use =A2>0 instead.
  • Including blank-looking cells unintentionally: use an explicit nonblank condition, and decide how to handle formulas that return an empty string.
  • Assuming formatting changes the value: displaying zero as a dash or hiding it with a number format does not make the underlying value nonzero.

When a result is unexpected, test a few representative cells directly in a spare column. Include a positive number, a negative number, a real zero, a blank, and any imported text values. Seeing TRUE or FALSE beside each case helps identify whether the issue is the comparison itself or the data’s contents.

Frequently Asked Questions

What is the Excel formula for not equal to zero?
Use =A1<>0 to return TRUE when A1 is not equal to numeric zero and FALSE when it is zero. Replace A1 with the cell you need to test.

How do I count cells that are not zero?
For a numeric range with no blanks, use =COUNTIF(A2:A20,"<>0"). If blanks, text, or formula-generated empty strings are present, decide whether they should be included and test the range before relying on the count.

Does “not zero” include negative numbers?
Yes. Negative numbers, positive numbers, and nonzero decimals all differ from zero. For instance, -2 satisfies A1<>0; it does not satisfy a positive-only test such as A1>0.

How can I exclude both zeros and blanks?
For a single cell A2, use =AND(A2<>"",A2<>0). This tests that the cell is not blank or an empty-string result and is also not numeric zero. Consider errors and text separately if they occur in the data.

Can I filter a list to hide zeros without deleting them?
Yes. Apply a filter, open the number-column menu, and choose Number Filters > Does Not Equal, then enter 0. The filter hides matching rows; clearing the filter makes them visible again.

Why does a cell that looks like zero behave differently?
It may contain text “0” rather than numeric zero, or its displayed value may be rounded by number formatting. Check the underlying value and data type, because formatting can change what you see without changing what Excel compares.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.