Excel Number Format: Hide Zeros Without Deleting Them

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, apply a custom number format such as 0;-0;;@ to hide numeric zeros while keeping the cell values and calculations intact. Select the cells, press Ctrl+1, choose Number > Custom, enter the format, and select OK.

Apply a custom format to hide zeros

The quickest approach is to format just the cells where zero should disappear. This changes how Excel displays their contents; it does not remove data or change formulas. To apply the format in Excel for Microsoft 365 on Windows:

  1. Select the cells, range, or entire worksheet area you want to affect.
  2. Press Ctrl+1 to open the Format Cells dialog.
  3. On the Number tab, select Custom in the Category list.
  4. Enter 0;-0;;@ in the Type box, then select OK.

To remove the custom format later, reopen Format Cells, choose a standard category such as General, and confirm. On Excel for Mac, Command+1 opens Format Cells; choose Custom on the Number tab and enter the same format code. The code itself is not specific to Windows. If you prefer the ribbon, the Number Format drop-down on the Home tab offers More Number Formats, which opens the same dialog.

Try the format on a small range first if the cells already have a carefully chosen display, such as a currency or date format. Applying a new custom format replaces the existing number format for those cells. You can also use Format Painter or copy and paste formats to reuse a successful format, but check the destination cells: copying formats can replace their existing appearance.

How the custom format works

Excel custom number formats can have up to four sections, separated by semicolons. In order, they describe positive numbers, negative numbers, zero values, and text. In 0;-0;;@, the first section displays positive numbers as whole numbers, the second displays negative numbers with a minus sign, the third section is empty, and the fourth uses the @ placeholder to display text.

That empty third section is what suppresses the display of a value that is exactly zero. The cell still contains zero: the formula bar shows it when you select the cell, and formulas can still refer to it. The format is a display rule, not a deletion command or a replacement value. If you need to show text in place of zero rather than show nothing, put the desired text in quotation marks in the zero section. For example, a format can display a dash for zero instead of leaving the section empty.

Semicolons matter. A format with only two sections treats the first section as the positive-and-zero format and the second as the negative format, so it will not reliably hide zero in the intended way. Use an explicit third, empty section to specify zero behavior. Adding a fourth section preserves a defined display for text values in cells that might contain both numbers and text.

Choose a format that matches the data

The simple code 0;-0;;@ displays whole numbers without thousands separators or decimal places. Choose a format that matches the precision and conventions readers need to see. These examples each include an empty zero section:

  • 0.00;-0.00;;@ displays two decimal places for positive and negative values.
  • #,##0;-#,##0;;@ displays whole numbers with thousands separators.
  • #,##0.00;[Red](#,##0.00);;@ displays two decimal places, with negative values in red and parentheses.
  • 0%;-0%;;@ displays percentages as whole percentages for nonzero values.

Use the appropriate number of decimal places for the task. For example, 0.00;-0.00;;@ is useful for a report that normally shows hundredths, while a whole-number display is more suitable for counts. If you format values as percentages, remember that Excel stores a percentage as a decimal: a stored value of 0.25 displays as 25% with a percent format.

Formatting can also affect whether small nonzero values appear to be zero. A value of 0.004 displayed with two decimal places rounds visually to 0.00, but it is not an exact zero, so Excel uses the positive-number section and shows 0.00. The empty zero section does not hide it. Increase the displayed precision if readers need to distinguish small values, or use a separate rule or formula if your requirement is to suppress values within a particular range.

What changes—and what stays the same

A number format affects presentation, not the underlying numeric value. A hidden zero still counts in arithmetic, remains available to chart and formula calculations, and can be found by searching for the value. For instance, a SUM that includes a cell with a hidden zero includes that zero just as it would if the zero were visible. The custom format also does not make a cell empty, so tests that distinguish an empty cell from a cell containing zero continue to distinguish them.

Formatting alone does not affect formulas that use the cell. It does, however, affect what a person sees in the worksheet and on a printed page. For sharing or exporting data, do not assume the custom format will travel with the values: formats are not stored in plain-text CSV files. Check the exported file and the destination application, especially when a recipient needs to see every zero or needs the original numeric data.

One useful distinction is between a true zero and a blank-looking result. A blank cell has no entered value; a formula that returns an empty string looks blank but still contains a formula; and a zero hidden by a number format remains the numeric value zero. These states can behave differently in formulas, charts, sorting, and data validation. Choose the method according to whether you want to change only appearance or change the result itself.

Hide zeros across a whole worksheet

If the goal is to hide every zero on one worksheet, rather than only selected ranges, Excel for Windows has a worksheet-level display setting. Select File > Options > Advanced, scroll to Display options for this worksheet, choose the worksheet if necessary, and clear Show a zero in cells that have zero value. The setting affects zero display across that worksheet, including cells that do not use a custom number format. It does not change the cell values.

This broad setting can be convenient for a presentation sheet, but it offers less control than a custom format. You cannot use it to hide zeros in one table while keeping them visible in another table on the same worksheet. It can also make legitimate zero results less obvious to readers. If you use the setting, consider adding a note to the worksheet or making the rule clear in a nearby label.

Excel for Mac also provides worksheet display preferences, but the location and wording can differ across releases. Look in Excel settings or preferences under View for the zero-value display option. If you need a format that is targeted to a selection and travels with the workbook, the custom number format is usually the more predictable choice.

Common problems and alternatives

If zeros remain visible, first check that the format has three semicolons-separated sections before the text section: 0;-0;;@. Confirm that you applied it to the cells in question and that the displayed value is actually zero. A formula may return a small nonzero result that rounds to 0.00, or the cell may contain the text character 0 rather than the number zero. The custom format’s zero section applies to numeric zero, not text that merely looks like zero.

If the cells are dates, times, or specialized values, avoid replacing their format with a whole-number code without checking the result. Excel stores dates and times as numbers, so a number format can change their appearance dramatically. Build the zero-hiding format around the date or time format those cells need, and test positive, negative, and zero cases before applying it widely. Similarly, formats for phone numbers, product codes, and identifiers may need to preserve leading zeros; those are often better treated as text rather than as ordinary quantities.

A formula is an alternative when you want the output to be blank-looking based on a condition, not merely hide a numeric zero. With the source value in A2 and the result in B2, enter =IF(A2=0,"",A2) in B2. If A2 is zero, the result is an empty string; otherwise, B2 returns A2. This is a formula result, not an actually empty cell, and it creates a separate output column. It can be useful in a report, but the output may not behave exactly like a numeric source cell in every downstream task.

Conditional formatting is another option when the rule is more complex, such as changing the appearance of zero only when another cell meets a condition. It is generally a visual treatment, not a change to the value. Hiding text by making its font match the background is less robust: the content can reappear if the background changes, and it may remain exposed in other views. For straightforward numeric zeros, a custom number format is clearer and easier to maintain.

Frequently Asked Questions

Does hiding zeros with a number format delete them?
No. The cells still contain zero, and formulas can use those values. The format only changes the displayed appearance.

Will the format hide a formula result of zero?
Yes. If a formula returns the numeric value 0, the zero section applies. A formula returning an empty string is a different case and already appears blank.

Can I hide zeros but keep negative numbers visible?
Yes. Use a format with separate positive, negative, and zero sections, such as 0;-0;;@. The negative section continues to display negative values.

Why does a cell still show 0.00?
It may contain a small nonzero value rounded to two decimal places for display. The empty zero section hides exact numeric zeros, not every value that rounds to zero.

Can I hide zeros in the entire workbook?
The worksheet display setting applies to a selected worksheet, not necessarily every sheet at once. You can apply custom formatting to ranges on multiple sheets, or change each worksheet’s display setting individually.

Will other people see the zeros?
They will see the custom appearance when they open the workbook in an application that supports Excel number formats. The underlying zero remains present, and formats are not preserved in plain-text CSV files, so check the file type and recipient’s workflow.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.