To display numeric zeros as blank in Excel without deleting their values, apply the custom number format 0;-0;;@ to the cells. The zero section between the second and third semicolons is empty, so zero is hidden while positive numbers, negative numbers, and text remain visible.
The custom format code for hiding zero
In Excel for Microsoft 365 on Windows, select the cells you want to change and open Format Cells with Ctrl+1. On the Number tab, choose Custom, enter 0;-0;;@ in the Type box, and select OK. The format affects how the selected cells display; it does not clear or replace their contents.
The four sections in a custom number format are separated by semicolons. In this code, they have specific jobs:
0formats positive numbers as whole numbers, showing at least one digit.-0formats negative numbers with a minus sign.- The empty third section tells Excel not to display numeric zero.
@displays text as entered instead of hiding it.
For example, a cell containing 0 appears empty, a cell containing 12 appears as 12, a cell containing -12 appears as -12, and a cell containing the text “pending” still shows pending. You can select a range, an entire column, or nonadjacent cells before applying the format. Use the same Format Cells dialog to change or remove it later.
How to apply it in Excel for Windows and Mac
On Windows, select the target cells, press Ctrl+1, then choose Number > Custom. Enter the code in Type and confirm. You can also reach the Format Cells dialog from the small dialog launcher in the Number group on the Home tab. The custom code is entered without an equals sign.
On a Mac, select the cells and press Command+1 to open Format Cells, then choose the custom number format option and enter the same code. The labels or dialog layout can vary slightly by Excel version. In Excel for the web, formatting support and the available controls can differ from the desktop app; if you cannot create or edit the custom code there, open the workbook in desktop Excel.
After applying the format, check a cell that contains zero and then select it. The grid may look empty, but the formula bar still shows its value or formula. That quick check helps distinguish a hidden zero from a genuinely empty cell.
How Excel decides what to show
A custom number format is a display instruction, not a calculation. Excel stores the number or formula result in the cell and uses the format to decide how to render it. In a format with four sections, the first normally applies to positive numbers, the second to negative numbers, the third to zero, and the fourth to text. An empty third section is the key to hiding zero.
If a format has fewer sections, Excel uses rules to determine which values each section covers. For instance, a one-section format is applied to all numbers; it does not provide a separate zero section. A two-section format normally separates positive and zero values from negative values. Using all four sections explicitly, as in 0;-0;;@, makes the intention easier to read and preserves text display.
The format code does not change the actual precision of a number. If a cell contains 0.004 and its format rounds the display to a whole number, it may appear as 0 and then be hidden by the zero section even though its stored value is not exactly zero. If small values matter, choose a format with appropriate decimal places, such as 0.00;-0.00;;@. That displays 0.004 as 0.00 rather than making it look blank.
Choose decimals, separators, and negative-number styles
The basic code uses whole numbers and a minus sign, which may not match the way a report presents values. You can customize the positive and negative sections while keeping the empty zero section. For example, #,##0.00;[Red](#,##0.00);;@ displays positive values with two decimal places and thousands separators, negative values in red parentheses, and zeros as blank. Text remains visible because the fourth section is @.
Another useful variation is to show a dash instead of a blank. In 0;-0;"-";@, the third section contains a quoted dash, so zero displays as “-”. This can make a table’s layout clearer while still distinguishing a calculated zero from missing data. Choose a dash only when readers will understand its meaning; in some reports, a dash can be mistaken for unavailable information.
Custom formats can also preserve currency symbols or percentages. Keep the symbol and decimal pattern in the positive and negative sections, leave the zero section empty, and retain the text section if the cells may contain text. Because format conventions can depend on regional settings, check the result in the workbook’s locale. Formatting a number as a percentage also changes its displayed scale—for example, 0.25 displays as 25%—but does not change the stored value.
What hiding zero does—and does not do
Hiding zero changes the cell’s appearance only. Excel still treats the value as numeric zero for arithmetic, references, sorting, and many other operations. A SUM over the range still includes zero, although adding zero does not change the total. A formula that refers to a hidden zero receives zero, not an empty cell. The cell can still be selected, edited, copied, and used in calculations.
That distinction matters when another process reads the workbook. A person looking at the worksheet may see a blank, but the formula bar, formulas, or data-processing tools can reveal the value. Saving a worksheet as CSV does not preserve Excel’s custom number formatting; CSV stores the underlying data as text fields, not the worksheet’s visual display rules. Do not use a display format as a way to remove confidential or unwanted data.
Likewise, a hidden zero is not necessarily treated as a blank by charts, filters, conditional formatting, or formulas. Their behavior depends on the feature and its settings, not just the number format. If you need a chart to omit zero values or a formula to return an empty string, handle that separately and verify the result for that specific use.
Zero, a blank cell, and an empty-string result
These three cases may look alike on a worksheet but are different to Excel. A numeric zero is a value. A truly blank cell has no value entered. An empty string is text with no visible characters, often produced by a formula. The custom format’s empty zero section hides numeric zero; it does not erase it or turn it into either of the other cases.
The fourth section, @, is for text. A text entry such as the character 0 is not the same as the numeric value zero, so it is not hidden by the numeric zero section. Similarly, a formula that returns an empty string is already visually empty; number formatting does not make it a genuinely blank cell. This is useful when a report needs to conceal zero values but still show text labels or status messages.
If downstream logic must distinguish “not entered” from “entered as zero,” keep that distinction in the data. Do not use the visual blank as a substitute for a missing-value marker. For example, a sales table might use a blank to mean no figure was supplied and zero to mean sales were measured and none occurred. Hiding zero can make the report neater, but it can also conceal that meaningful difference from readers unless the workbook explains the convention.
Alternatives for hiding zero values
A custom number format is usually the best fit when you want a selected range to retain numeric zeros while displaying them as blank. If you want zeros hidden across a whole worksheet, Excel has a worksheet display setting instead. In Windows desktop Excel, go to File > Options > Advanced, find Display options for this worksheet, and clear Show a zero in cells that have zero value. This affects the display of zeros on that worksheet rather than applying a chosen format to particular cells. The setting can be easy to overlook later, so a cell-specific custom format may be clearer in a shared workbook.
Conditional formatting is another option when the appearance should depend on a rule or should apply only to selected cells meeting additional conditions. On Windows, use Home > Conditional Formatting to create a rule. However, using a font color that matches the fill to hide zero can make values hard to read if the fill changes, and it is less explicit than a number format. Use conditional formatting for conditional visual emphasis, not as a replacement for deleting or changing data.
You could also use a formula that returns an empty string when a calculation is zero, but that changes the formula’s result from numeric zero to text. That can affect later calculations, comparisons, and chart behavior. Prefer the number format if the value should remain numeric. Use a formula-based blank only when the logic truly calls for an empty text result, and check how dependent formulas handle it.
Common problems and checks
- Zeros still appear: Confirm the right cells were selected and that the code has two semicolons in a row for the empty zero section. The intended pattern is
0;-0;;@. - Small values disappear: A value such as 0.004 may be rounded to zero for display by a whole-number format. Add suitable decimal places so nonzero values remain visible.
- Text “0” remains visible: That is expected when the cell contains text rather than numeric zero. Check the cell’s content or how it was imported.
- The code seems to revert: Reopen Format Cells and inspect the Type box. A format may have been applied to a different selection, or a later paste may have replaced the formatting.
- The worksheet looks inconsistent: Check whether all relevant cells use the same number format and whether the worksheet-wide zero-display option is also enabled or disabled.
When auditing a workbook, select a supposedly blank-looking cell and inspect the formula bar. You can also temporarily apply a standard number format to reveal its value, then restore the custom format. Avoid relying on appearance alone when checking totals or data quality.
Frequently Asked Questions
Does hiding zero with a custom format delete the value?
No. It only suppresses the displayed zero. The cell remains numeric zero, or continues to contain the formula that returns zero.
Will SUM ignore zeros that look blank?
No. The cells still contain numeric zeros, so they remain part of the referenced range. Including or excluding zero does not change a sum’s result, but the values are not converted to blanks.
Can I hide zero but keep decimal values visible?
Yes. Use decimal places in the positive and negative sections, such as 0.00;-0.00;;@. Choose enough places to avoid rounding small nonzero values to a displayed zero.
Why does a text zero still show?
The zero section formats numeric values. A character 0 stored as text follows the text section, which is @ in the example, and therefore remains visible.
How do I show a dash instead of a blank?
Replace the empty third section with a quoted dash: 0;-0;"-";@. The dash is a display choice; the underlying cell value remains zero.
Related
- Excel Column K Number: What It Means and How to Find It
- ICD-10 Z Codes List in Excel: Find, Import, and Check
- Excel Color Scales: How Conditional Formatting Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Avery Labels in Word: How to Create and Print ThemTo create Avery labels in Microsoft Word, open the Mailings tab, choose Labels, select the Avery product number…
- How to Add a Running Header in Word for MacTo add a running header in Word for Mac, open the document, choose Insert > Header & Footer…
- Making a Calendar in Google Sheets: A Practical GuideTo make a calendar in Google Sheets, enter a date from the month you want in a cell…
- Excel Data Model: How to Group Dates in PivotTablesExcel PivotTables built from the Data Model cannot use the ordinary right-click Group command to group dates. Instead,…
- Excel Data Validation Colour Change: Rules and OptionsExcel data validation cannot change a cell’s colour by itself. To colour cells according to a validated value—or…
- Excel Power Query: How to Keep RowsIn Excel Power Query, select the rows you want to retain in Power Query Editor, then use Home…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.