If an Excel formula returns 0 and you want the cell to look blank, wrap the calculation in an IF function that returns an empty string when the result is zero. For example, if the formula in D2 calculates the difference between A2 and B2, use =IF(A2-B2=0,"",A2-B2); it displays a blank when the difference is zero and the calculated difference otherwise.
How the IF formula hides a zero
Excel’s IF function checks a condition and returns one value when the condition is true and another when it is false. In this case, the condition is whether the calculation equals zero. The two quotation marks with nothing between them, "", represent an empty text string, which Excel displays as an empty-looking cell.
For instance, with quantities in A2 and B2, a formula in C2 can subtract the second quantity from the first. If both quantities are 12, the difference is zero, so the cell appears blank. If A2 is 15 and B2 is 12, the same formula displays 3. It still calculates the result; it only changes what is returned when the result is zero.
To adapt the approach, put your original calculation in both places where the example uses the calculation: once as the condition and once as the value to show when the condition is false. If the calculation is a sum instead of a subtraction, the structure is the same.
Wrap an existing formula without changing its calculation
Suppose your existing formula totals the values in A2 through B2, and you want a zero total to appear blank. Replace the formula in C2 with the following. If A2 contains 8 and B2 contains -8, the total is zero and C2 displays blank; if the values are 8 and 5, it displays 13.
With the values to add in A2:B2 and the total formula in C2, use:
=IF(SUM(A2:B2)=0,"",SUM(A2:B2))
The inner SUM is the calculation being evaluated. The first SUM supplies the test, and the second supplies the result when the test is false. This repeats the calculation in the formula, so for complicated expressions it can be easier to use a helper cell or, in newer Excel versions, LET to name the result once.
With values in A2:B2 and the result formula in C2, this LET version names the sum as total. It requires Excel 2021 or Microsoft 365. For 8 and -8 in A2 and B2, respectively, it returns an empty string.
=LET(total,SUM(A2:B2),IF(total=0,"",total))
Make a formula cell look blank versus make it truly empty
A formula that returns "" does not make its cell genuinely empty. The cell still contains a formula, and the formula’s result is zero-length text. This distinction matters when other formulas, data checks, charts, or exports use the cell.
- ISBLANK: A cell containing a formula that returns
""is not considered blank by ISBLANK, because the formula is present. - COUNTA: COUNTA can count a formula cell returning
""as a nonempty cell. If you are counting displayed results, choose a counting approach that matches what you intend to count. - SUM and AVERAGE: When a formula result of
""is in a referenced range, it is text rather than a numeric zero, so it is generally ignored by these functions. Check how your downstream formula handles the range, especially if it mixes text and numbers.
Use the IF method when the formula’s result should be treated as textually absent for display, but remember that it is not a way to clear the cell. A formula cannot both remain in a cell and leave that cell truly empty. If another process requires a truly empty cell, such as a data import with strict blank-field rules, you may need to change that process or use a separate output range.
Hide zero values across a worksheet instead
If the goal is simply to hide every numeric zero on one worksheet, a display setting may be more convenient than editing many formulas. In Excel for Microsoft 365 on Windows, choose File > Options > Advanced. Under Display options for this worksheet, clear Show a zero in cells that have zero value, then select OK. The setting applies to the selected worksheet, not just the formula cells.
This changes how zeros appear, not their underlying values. A zero remains numeric zero, so calculations, ISBLANK checks, and other formulas continue to treat it as zero. The trade-off is that it hides all zero values on that sheet, including zeros typed directly into cells or produced by unrelated formulas. In Excel for Mac, the corresponding zero-display preference is in Excel’s View settings; the exact settings labels can vary by release. Excel for the web may not expose the same desktop options, so a formula or cell format is a more portable way to control specific results.
Choose between a formula, a setting, and number formatting
The best method depends on whether you want to change the value returned, the way a value is displayed, or every zero on a sheet. Consider these practical distinctions:
- Use IF returning an empty string when only selected formula results should appear blank. It is explicit and travels with the formula if you copy it to another workbook.
- Use the worksheet zero-display setting when all zeros on that sheet should be hidden, regardless of how they were entered or calculated. It is quick, but can conceal meaningful zero balances or counts.
- Use a custom number format when a numeric zero should remain numeric but not be printed visibly. Number formats can show positive, negative, and zero values differently, but the format applies only to the cells you select and can be less obvious to someone inspecting the workbook.
For reports, decide whether a zero is genuinely uninformative. A zero sales total, for example, can mean no sales occurred; hiding it may make a reader think the data is missing. A blank can instead mean “not applicable” or “not entered.” Use a consistent convention and, where needed, explain it in a report note.
Common problems and how to fix them
The cell still shows 0. Check whether the formula was replaced with the IF version and whether the test covers the actual calculation. If the original formula rounds or applies other operations, compare the final result rather than an earlier input. Also check whether the cell contains a typed value rather than a formula, or whether a different cell is being displayed.
A tiny amount is displayed instead of blank. The underlying result may be a small nonzero decimal that is formatted to show no decimal places. A value such as 0.004 may look like 0 while not equaling zero. If it is appropriate to treat values rounded to two decimal places as zero, test the rounded result instead. With the calculation in A2 and the display formula in B2, this returns blank for a result that rounds to 0.00, and otherwise displays the original value:
=IF(ROUND(A2,2)=0,"",A2)
For example, if A2 is 0.004, ROUND(A2,2) is 0.00, so B2 is blank; if A2 is 0.016, the test is not zero and B2 displays 0.016. Choose the number of decimal places to match the meaning of your data, not merely the cell’s visual format.
A blank-looking cell behaves unexpectedly in a later formula. Remember that "" is text, not a truly empty cell. A formula checking ISBLANK will not treat it as empty, and COUNTA may include it. If you need to distinguish a missing input from a calculated zero, test the input cells separately rather than relying on the displayed appearance.
Excel shows a formula error. The IF formula does not automatically handle errors in the calculation. If the original calculation returns an error, the zero comparison also encounters that error. Diagnose the source of the error first; use error-handling functions only if you have decided what an error should mean. Do not hide all errors as blanks by default, because that can conceal broken references or invalid data.
Copy the formula down a column
When you fill the formula down, ordinary relative references adjust automatically. If the formula in C2 refers to A2 and B2, copying it to C3 changes the references to A3 and B3. In Windows, select the formula cell and drag its fill handle, or copy and paste it; the usual copy and paste shortcuts are Ctrl+C and Ctrl+V. On Mac, use Command+C and Command+V. If you use the fill handle, verify the first few copied rows to ensure the references moved as intended.
If one part of your calculation must stay fixed, use absolute references with dollar signs. For example, a tax rate stored in F1 might need to remain fixed as a formula is copied down. Be careful to preserve those references when you wrap the original calculation in IF; changing the reference pattern can produce incorrect results even if the zero-hiding logic is right.
Frequently Asked Questions
Can I make a formula return a blank instead of zero?
Yes. Test whether the calculation equals zero with IF and return "" for that case. The cell looks blank, but it still contains the formula.
Does an empty string count as a blank cell in Excel?
No. A formula returning "" leaves a formula in the cell, so functions such as ISBLANK do not treat it as a genuinely empty cell. COUNTA can also count it.
How do I hide only some zeros and keep other zeros visible?
Use an IF formula in the cells whose zero results should be hidden. The worksheet setting hides zero values across the sheet, while an IF formula targets the results you choose.
Why does my formula show a small value instead of a blank?
The calculated value may be close to zero but not exactly zero. If your rules define values rounded to a particular precision as zero, compare the rounded result, as in the ROUND example above.
Can I hide zero without changing the formula?
Yes. You can turn off zero display for the worksheet or apply a custom number format to selected cells. Those options change appearance rather than making the formula return an empty string, so the underlying zero remains available to calculations.
Related
- Excel Date Number to Text: Convert Dates Clearly
- Excel Data Validation List: Create a Yes/No Dropdown
- Excel Data Validation Simple List: Create and Manage It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Use XLOOKUP in Excel: A Step-by-Step Formula GuideTo retrieve a value with XLOOKUP, identify the value to search for, the range where it appears, and…
- Dynamic Named Range Excel Formula: Setup and ExamplesA dynamic named range in Excel is a named reference that expands or contracts as entries are added…
- Excel IFS Function Examples for Multiple ConditionsExcel’s IFS function checks conditions in order and returns the value paired with the first condition that is…
- Excel 2013 Array Formulas: How to Enter and Use ThemIn Excel 2013, an array formula performs calculations on one or more sets of values and may return…
- COUNTIF in Excel: Find and Flag DuplicatesTo find duplicate values in Excel, use COUNTIF to count each value within the list: with values in…
- SUMIF With Multiple Criteria: Examples and AlternativesFor multiple criteria in Excel, use SUMIFS rather than SUMIF. With dates in A2:A8, regions in B2:B8, products…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.