Yes. In Excel for Microsoft 365 on Windows, apply a custom number format with an empty third section to show numeric zeros as blank without changing their values. Select the cells, press Ctrl+1, choose Number > Custom, and enter 0;-0;;@. The zeros remain available to formulas and calculations; only their display changes.
How the custom number format works
An Excel custom number format can have up to four sections, separated by semicolons. Those sections control how positive numbers, negative numbers, zero, and text appear, in that order. In 0;-0;;@, the first section displays positive numbers, the second displays negative numbers, the third is empty, and the fourth displays text as entered. Because the zero section contains no format code, Excel displays a zero value as a blank cell.
The format changes presentation, not data. A cell displaying nothing may still contain the number 0, and the formula bar will show that value when the cell is selected. Functions that use the cell still see zero: for example, SUM includes it, and a test that checks whether the cell equals zero remains true. This is useful when a report is easier to read without rows of zeros but calculations must remain complete.
The placeholders in the format determine how nonzero numbers appear. The digit placeholder 0 forces a digit to display, while # displays a digit only when needed. The semicolons separate sections; the empty section between the second and fourth semicolons is intentional. Do not remove it, or Excel may assign the sections differently.
Apply the format in Excel for Windows
Use the Format Cells dialog to apply the format to one cell or a selected range:
- Select the cells whose zero values should appear blank. You can select a whole column if the rule should apply to its current and future entries.
- Press Ctrl+1 to open Format Cells. Alternatively, use Home > Number and select the small dialog box launcher in the Number group.
- On the Number tab, choose Custom in the Category list.
- Click the Type box, enter
0;-0;;@, and select OK.
On Excel for Mac, select the cells and press Command+1 to open Format Cells. The dialog uses the Number tab and Custom category as well. Ribbon details can differ slightly by Excel build, but the custom format itself is the same. In Excel for the web, existing formats may display correctly; if you cannot create or edit the custom format in your browser, open the workbook in the desktop app and apply it there.
To remove the effect, reopen Format Cells and choose a standard number format, or replace the custom type with a format that has a visible zero section. Applying the format to a new range does not alter other cells unless they share the same formatting through a table style, copied formatting, or a broader selection.
Adapt the format to decimals, percentages, and currency
The basic code uses whole numbers and shows negative values with a minus sign. Match the positive and negative sections to the way the values should normally appear, then leave the third section empty. For instance, #,##0.00;-#,##0.00;;@ displays nonzero values with thousands separators and two decimal places, while a zero remains blank. The fourth section keeps text visible.
For percentages, use 0.0%;-0.0%;;@. A value of 0.125 displays as 12.5%, while a value of 0 displays as blank. For a currency display with two decimal places and negative amounts in parentheses, use $#,##0.00;($#,##0.00);;@. Currency symbols and separators can depend on your regional settings, so check a positive and negative example after applying a format.
These are patterns to adapt, not a reason to replace a carefully designed existing format blindly. If a format already includes colors, brackets, scaling, or other rules, preserve those parts and make its zero section empty. The first section should still describe positive values, the second negative values, and the third zero. Test the result on representative cells, especially if the range contains more than one type of value.
Choose between a custom format and hiding zeros on a sheet
A custom number format is usually the better choice when only particular cells, columns, or report fields should hide zero. It travels with the cell formatting when cells are copied, and it can be tailored for values such as percentages or currency. Other cells on the same worksheet can continue to show zero normally.
Excel also has a worksheet-level setting to hide zero values across a sheet. In Windows, go to File > Options > Advanced, then under Display options for this worksheet clear Show a zero in cells that have zero value. If the workbook has multiple sheets, check the worksheet named in the setting; the choice is not a universal rule for every sheet. On a Mac, use Excel > Preferences or Settings, depending on the version, and look for the worksheet display option for zero values. The wording and location can vary between releases.
The worksheet setting is convenient when the goal is to hide all displayed zeros on one worksheet at once. However, it is less selective: it can conceal meaningful zero results in labels, summaries, or other areas where readers expect to see them. Custom formats give more control, while the worksheet option can be quicker for a sheet-wide presentation change.
Blank-looking zero is not an empty cell
This distinction matters in spreadsheets. A number formatted to look blank still contains zero. A truly empty cell has no value, while a formula that returns an empty text string contains a formula and produces text rather than a numeric zero. Those cases can behave differently in formulas, filters, charts, exports, and checks for blank cells.
For example, a SUM calculation includes a formatted zero just as it includes any other numeric zero. A count of numeric cells can also include it, even though the cell looks empty. A formula or process that specifically checks whether a cell is empty may not treat a zero-formatted cell as empty. If downstream users need to distinguish missing data from a measured value of zero, communicate that distinction rather than relying on appearance alone.
Formatting is often preferable to changing a formula when zero is a valid result. If a formula should genuinely return no value under certain conditions, that is a separate logic decision: it changes what the cell contains and can affect later calculations. Choose based on the meaning of the data, not just the visual result you want.
Common problems and ways to diagnose them
- A zero still appears: Check that the selected cell has the custom format and that the third section is empty. A format such as
0;-0;0;@explicitly displays zero. Also confirm that the cell is actually numeric zero, not text containing the character 0. - A small value looks like zero but is not hidden: A calculated value such as 0.00001 is not exactly zero, even if the chosen decimal format rounds its display to 0.00. The zero section applies to a value equal to zero, not every number that rounds to zero. If tiny values should also be hidden, consider a different display rule and decide what threshold is appropriate for the data.
- Text disappears or changes: Include a fourth section such as
@when you want text to remain visible. A custom format with fewer sections can handle text differently from the explicit text section in the example. - Dates look unexpected: Dates are stored as serial numbers and use number formats to display as dates. Do not paste a generic number format over date cells without checking the result. Preserve the date display code in the positive section and make the zero section empty, then verify a real date and a zero-valued cell.
- Another user sees a different result: Check whether they are viewing the same workbook and worksheet, whether a different number format was applied, and whether a sheet-wide zero-display setting is involved. A blank-looking cell alone does not establish that the underlying value is missing.
When this approach is useful—and when it is not
Hiding zeros works well in presentation-oriented tables where a zero adds little information, such as a monthly matrix with many periods that have no activity. It can reduce visual clutter while preserving a complete numeric grid for totals and comparisons. It is also useful in templates where formulas are already present but users should see a clean report before values are entered.
It may be a poor choice for audit trails, inventory, measurement, or financial reports where an explicit zero conveys that something was checked and found to be zero. A blank can be mistaken for missing information. In those settings, consider keeping zeros visible or adding a clear note explaining the display convention. If only some zeros should disappear, apply the format only to the intended range rather than hiding zeros across the whole worksheet.
Number formatting is also not a data-cleaning method. It will not delete zero values, convert them to blanks, or prevent a formula from producing them. If the goal is to change the underlying data, use an appropriate formula or data-cleaning step and verify its effect separately. Keep a copy of important workbooks before making broad changes, particularly when applying a format to entire columns or multiple sheets.
Frequently Asked Questions
Does hiding zero with a custom format change the cell value?
No. The cell still contains numeric zero. The format only changes what Excel displays, so calculations and references continue to use the value.
Can I hide zeros but keep negative numbers visible?
Yes. Use separate positive and negative sections followed by an empty zero section, such as 0;-0;;@. The negative section controls how negative values display; the empty third section hides only zero.
Why does a formula that looks like zero still show?
The result may be a small nonzero number rounded to zero by the displayed decimal places. A zero-only format section hides an exact zero, not all values that happen to look like zero after rounding.
Will a blank-looking zero be counted as a blank cell?
Not necessarily. It remains a numeric value, so functions and checks that distinguish zero from an empty cell can still recognize it as zero. Its appearance does not make it an empty cell.
Can I hide zero values in the entire worksheet?
Yes. Use the worksheet display option for showing zero values in Excel Options on Windows. That affects the sheet more broadly than a custom format, so use a custom format instead when only selected ranges should hide zero.
Related
- Excel Keyboard Shortcut to Delete Cells and Shift Data
- Excel Go To Referenced Cell: Shortcuts and Navigation
- Excel Delete Shortcut: Clear Cells or Remove Them?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Cell Show Current Date? TODAY and NOW ExplainedTo show the current date in an Excel cell, enter =TODAY() and press Enter. To show both the…
- Delete a Page Break in Word Without Losing Your TextTo delete a manual page break in Microsoft Word, show formatting marks, locate the line labeled Page Break,…
- Create an Outlook Signature: Setup and Management TipsTo create a signature in Outlook, open the signature settings for your version, choose to add a new…
- PowerPoint Will Not Animate Text? Causes and Fixes in SlidesPowerPoint can animate text, so if it will not, first select the text box and apply an effect…
- Excel: Change European Number Format to US SeparatorsTo change Excel from European-style numbers such as 1.234,56 to US-style numbers such as 1,234.56 in Microsoft 365…
- Outlook Share Calendar Greyed Out? Causes and FixesIf Outlook’s Share Calendar button is greyed out, the calendar or account may not support sharing, or your…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.