To suppress zeroes in Excel, choose between hiding them on a worksheet, hiding them only in selected cells, or changing a formula so it returns an empty string when the result is zero. The first two options affect what you see while preserving numeric values; the formula option changes the result. The right choice depends on whether you need a cleaner view or a blank-looking formula output.
Choose the right way to suppress zeros
| Method | Scope | What changes | Best fit |
|---|---|---|---|
| Worksheet setting | One selected worksheet | Display only; zero values remain | Hiding all zeroes on a worksheet |
| Custom number format | Selected cells | Display only; values remain | Hiding zeroes in a particular range |
| IF formula | Formula output | Returns an empty string when the condition is true | A blank-looking result is needed from a calculation |
Microsoft documents the worksheet-wide setting for Excel for Microsoft 365 and Excel 2016, 2019, 2021, and 2024 on Windows, and provides a separate route for supported Mac versions. The instructions below are for desktop Excel; the cited Microsoft guidance does not establish the same menu paths for Excel for the web. See Microsoft’s zero-value display guidance and its Mac instructions.
1. Hide zeros across a worksheet
Windows desktop
- Open File > Options > Advanced.
- Under Display options for this worksheet, select the worksheet you want to change.
- Clear Show a zero in cells that have zero value, then select OK.
This is a display setting for the selected worksheet. It does not delete zeros or change calculations, so formulas and other operations can still use the underlying values. To show the zeros again, return to the same setting and select the checkbox.
Mac
- Open Excel > Preferences > Authoring > View.
- Clear Zero values.
The documented Mac navigation differs from Windows; use the route for your platform rather than looking for the Windows checkbox.
Recommended Free Tools
2. Hide zeros in selected cells with a custom format
- Select the cells to format.
- Open Format Cells (press Ctrl+1 on Windows).
- Choose Number > Custom.
- Enter
0;-0;;@in the format field and apply it.
The four sections in a custom number format apply, in order, to positive numbers, negative numbers, zero values, and text. In 0;-0;;@, the third section is empty, so zeroes are not displayed. The other sections retain a standard display for positive and negative numbers and text. Microsoft explains the format sections in its custom number format guidelines.
The zero is still stored in the cell: it remains visible in the formula bar and is not printed as a displayed value. If the cell’s value later becomes nonzero, Excel displays it according to the format. To reveal zeroes again, change or clear the custom format.
3. Return an empty string when a formula result is zero
Use an IF formula when the calculation itself should produce a blank-looking result for zero, rather than merely hiding a numeric zero. Microsoft’s example is:
=IF(A2-A3=0,"",A2-A3)
If A2-A3 equals zero, the formula returns "", an empty string; otherwise, it returns the calculation. Replace A2-A3 with your own expression, using that same expression for the condition and the result as appropriate. To reverse this behavior, edit the formula.
Free tools Windows power users keep installed
One-click scans. No signup required.
Rank #3
An empty string is not the same as a genuinely empty cell. A custom format hides a value without changing it, while an IF formula returns text of zero length under the specified condition. Microsoft notes that even a cell containing spaces differs from a blank cell in its conditional-formatting guidance.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Alternative: hide formula zeros with conditional formatting
Microsoft also describes using conditional formatting to make cells equal to zero display white text. This can make a zero hard to see on a white background, but it is a visual workaround rather than a change to the value. It may not work on cells with a different fill color or a design that changes. For a cleaner, more predictable result in selected cells, use the custom number format instead.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
