Excel Number Format to Hide Zeros Without Changing Values

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, apply a custom number format with an empty zero section, such as #,##0;-#,##0;;@, to hide zero values while keeping them in the cells for calculations. The cell still contains numeric zero; only its display changes.

How an Excel number format hides zero

A custom number format can have up to four sections, separated by semicolons: positive numbers; negative numbers; zeros; and text. The example #,##0;-#,##0;;@ tells Excel to show positive numbers with thousands separators, show negative numbers with a minus sign, show nothing for zero, and display text as entered. The empty third section is the key: it is where the zero display would normally be specified.

For example, a cell containing 0 will look empty, while cells containing 1250 and -1250 will display as 1,250 and -1,250. The format does not delete or replace the stored values. If a formula refers to a hidden zero, it still receives zero. This makes number formatting useful for reports where zero is meaningful to calculations but distracting on the page.

Number formats control appearance, not the underlying value or the formula that produced it. You can reveal a hidden zero by selecting the cell and looking at the formula bar, or by temporarily changing the number format to General. That distinction matters when someone mistakes an empty-looking cell for a genuinely empty cell.

Apply the custom format in Excel for Windows

Use these steps in Excel for Microsoft 365 on Windows:

  1. Select the cells, rows, columns, or range where zeros should be hidden.
  2. Press Ctrl+1 to open Format Cells. Alternatively, right-click the selection and choose Format Cells.
  3. Open the Number tab and select Custom in the Category list.
  4. Enter #,##0;-#,##0;;@ in the Type box, then select OK.

The selected cells now hide exact numeric zeros. The format affects the selection only, so applying it to a whole column will not change unrelated cells on another worksheet. To reuse the format elsewhere in the workbook, copy a correctly formatted cell and use Paste Special > Formats, or apply the same code through Format Cells.

On Excel for Mac, select the cells and press Command+1 to open Format Cells, then use the Custom category and enter the format code. Ribbon and dialog wording can vary slightly by release. Excel for the web may show custom formats already applied in a workbook, but if you cannot create or edit the required format there, open the file in the desktop app.

Choose a format that matches the data

The example format uses whole numbers. Choose a different positive and negative pattern when your data includes decimals, percentages, or currency. The zero section stays empty in each case:

  • For two decimal places, use #,##0.00;-#,##0.00;;@. A value of 12.5 displays as 12.50; zero is hidden.
  • For percentages with one decimal place, use 0.0%;-0.0%;;@. A stored value of 0.125 displays as 12.5%.
  • For currency, use $#,##0.00;($#,##0.00);;@ to show positive amounts with a dollar sign and negatives in parentheses.

These examples use familiar US-style separators and a dollar sign. Excel may interpret or display separators and currency symbols according to the computer’s regional settings. If the format is for a shared workbook, check how it appears on the recipients’ computers, particularly when they use a different currency or locale. You can also start from an existing currency or percentage format in Format Cells and edit its zero section rather than building every detail from scratch.

Pick a pattern that matches the information readers need. For instance, showing two decimal places can distinguish 3.10 from 3.14, while a whole-number format intentionally rounds the display. Hiding zero does not alter that rounding behavior or the stored precision.

What remains true when a zero looks blank

Because the value remains numeric zero, Excel calculations continue to use it as zero. A SUM over a range can include the cell, COUNT can count it as a number, and AVERAGE includes it in the set of numeric values. A formula such as =A1=0 still evaluates as TRUE when A1 contains a hidden zero. The custom format does not turn the cell into an empty cell.

This can be exactly what a report needs, but it can surprise someone checking the sheet. A blank-looking cell may still count in a calculation or satisfy a test for zero. If the distinction matters to colleagues, add a note to the report or use a separate indicator rather than relying on appearance alone. The format is also reversible: choose General or another number format to display the values again.

Charts and other tools may still use the zero values as data. Hiding a zero in a worksheet cell does not remove that point from the chart’s source range or convert it to a missing value. If the goal is to change chart behavior, adjust the chart’s data or chart settings instead of assuming a number format will exclude the value.

Hide zeros for a whole worksheet instead

If every zero on one worksheet should disappear, Excel has a worksheet display option. In Windows desktop Excel, choose File > Options > Advanced. Under Display options for this worksheet, select the sheet and clear Show a zero in cells that have zero value. This setting hides zeros on that worksheet without changing the number formats or stored values. It is a broader choice than formatting a selected range.

Use the worksheet option when the rule should apply consistently across the entire sheet, including cells added later. Use a custom number format when only certain areas should hide zero, or when different sheets need different treatments. The display option is worksheet-specific, so changing it for one sheet does not automatically hide zeros on every sheet in the workbook. On Mac, look in Excel’s View settings or preferences for the corresponding zero-display option; its location can differ between releases.

Neither approach removes values from formulas or source data. The worksheet setting is convenient for a clean overview, while custom formatting gives more precise control and can also define how positive and negative values look.

Common problems and how to diagnose them

If a zero still appears, first check whether the cell contains a numeric zero and whether the intended format was applied to that cell. A cell containing the text character “0” is text, not numeric zero, so the numeric zero section of a custom number format does not control its appearance. Imported data, a leading apostrophe, or a formula that returns text can cause this. Select the cell and inspect the formula bar; also check the Number category and the exact custom code.

Another frequent source of confusion is a value that is close to zero but not exactly zero. Suppose a formula returns 0.004 and the cell is formatted to show two decimal places. It can appear as 0.00 because the displayed number is rounded, but Excel still treats the underlying value as nonzero. A format whose zero section is empty will not hide it: Excel selects the section based on the value, not merely on the rounded display. If tiny values should count as zero for your report, decide on a threshold and handle that rule explicitly in the calculation or with conditional formatting.

Finally, a later paste operation or a table style may change the cell’s formatting. Reapply the custom format if the zero reappears after copying data. If only some cells behave differently, compare their formats and values instead of assuming the entire column uses one consistent setting.

When a formula or conditional formatting is a better fit

Use a formula only when you actually want a formula result that is blank text rather than a numeric zero. For example, with the original value in A1 and the display formula in B1, enter =IF(A1=0,"",A1) in B1. If A1 contains zero, B1 displays as blank; if A1 contains 7, B1 returns 7. This changes the result in B1 to text when the condition is true, so it may behave differently in calculations, sorting, or charting. It is not equivalent to simply hiding a number with formatting.

Conditional formatting is useful when the display rule depends on a condition beyond the number format, such as highlighting zeros or applying a special font color only in a particular range. In Windows Excel, select the range and use Home > Conditional Formatting to create a rule. Conditional formatting can change a cell’s appearance, but it does not erase the value either. For the straightforward job of making exact numeric zeros invisible while retaining standard numeric behavior, a custom number format is usually the simplest option.

Frequently Asked Questions

Does hiding zero with a number format delete the value?
No. The cell still contains zero, and formulas and calculations can still use it. Only its on-sheet display is hidden.

Can I hide zeros without hiding other values?
Yes. Apply a custom format with an empty third section, such as 0;-0;;@, to the selected cells. Other positive and negative numbers continue to display.

Why does a cell that looks blank still count in COUNT?
It may contain a numeric zero whose display is hidden. COUNT counts numeric cells, including zero; the number format does not make the cell empty.

Will this hide zero values in every worksheet?
A custom format applies to the cells you select. The separate worksheet display option can hide zeros across one sheet, but it is not automatically a workbook-wide rule.

Can I hide a value that rounds to zero?
Not with the empty zero section alone. A small nonzero value still uses the positive or negative section even if its displayed digits round to zero. Use a defined threshold if those values should be treated as zero.

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.