Yes. In Excel for Microsoft 365 on Windows, apply a custom number format such as 0;-0;;@ to show positive and negative numbers, leave numeric zeros blank, and display text normally. The zero is still in the cell; this changes only how Excel displays it, not the value used in calculations.
How custom number formats hide zero
An Excel custom number format can contain up to four sections, separated by semicolons. The sections apply, in order, to positive numbers, negative numbers, zero, and text. Leaving the third section empty tells Excel to display no characters for a numeric zero. In 0;-0;;@, the first section shows a positive number, the second shows a negative number with a minus sign, the third is blank, and @ displays text as entered.
The semicolons matter: they mark the boundaries between sections. A format with only two sections does not provide a dedicated zero section, so do not simply delete a digit or replace a zero placeholder and expect every type of value to behave as intended. The four-section pattern is especially useful when a worksheet contains both numbers and text labels.
Number formats affect display rather than stored data. If a cell contains the number 0, Excel continues to treat it as zero even when the cell looks empty. A formula that adds the cell, tests it, or counts it still encounters the underlying value. The same applies when a formula returns a numeric zero.
Apply the format in Excel for Windows
To hide zero values in selected cells, first select the range, then open the Format Cells dialog. On Windows, press Ctrl+1; alternatively, use Home > Number group dialog launcher and choose the Number tab. Select Custom, enter the format in the Type box, and select OK.
- Select the cells or range where zeros should appear blank.
- Press Ctrl+1 and open Number > Custom.
- Enter
0;-0;;@in the Type field. - Check the preview, then choose OK.
To show two decimal places and thousands separators instead, use #,##0.00;-#,##0.00;;@. For example, 1250.5 displays as 1,250.50, -1250.5 displays as -1,250.50, and 0 displays as a blank cell. The underlying numbers retain their original precision; the format controls the number of displayed decimal places, not the stored value.
On Excel for Mac, select the cells and open Format Cells with Command+1, then choose Number > Custom and enter the format. Ribbon labels and dialog layouts can vary slightly by Excel release. Excel for the web may display custom formats already present in a workbook, but if you cannot create or edit the desired format in its interface, open the file in the desktop app.
Choose a format that suits the data
The best code depends on how the sheet currently displays values. In the format sections, the first three portions can be customized separately, while the fourth portion governs text. Here are common starting points:
0;-0;;@shows whole numbers without commas, hides zero, and leaves text visible.#,##0;-#,##0;;@adds thousands separators while hiding zero.0.00;-0.00;;@shows two decimal places for nonzero values and hides zero.$#,##0.00;-$#,##0.00;;@adds a dollar sign and two decimal places; replace the symbol and punctuation to suit the workbook’s conventions.0%;-0%;;@displays nonzero values as percentages and hides zero. A stored value of 0.25 displays as 25%.
Custom formats can also use color and conditions, but simple formats are easier to review and maintain. If a workbook already uses a particular style for negatives, decimals, or currency, preserve that style and make its zero section empty. For instance, if you use parentheses around negative amounts, retain that negative section rather than switching to a minus sign just to hide zeros.
Be careful with percentages: percentage formats scale the displayed number by 100. Hiding zero does not change that scaling, and it does not convert a value such as 25 into 25 percent. The displayed result still depends on the number stored in the cell.
Hide zeros in one range or across a worksheet
A custom number format applies to the cells you selected, so it is a good choice when only certain columns or report areas should look blank at zero. It can also be applied to a whole worksheet by selecting the sheet first, but that may affect dates, percentages, text, and cells that need a visible zero. Consider the impact before applying a format broadly.
Excel also has a worksheet display setting for zeros. In Windows desktop Excel, use File > Options > Advanced, then under Display options for this worksheet clear Show a zero in cells that have zero value. Confirm that the correct worksheet is selected in the setting’s worksheet list. This option affects the display of zero values on that worksheet without requiring a custom format in each range. The exact location and availability of the corresponding setting can differ by platform and version.
Use the worksheet setting when the goal is to hide all zeros on one sheet. Use custom formats when the rule should apply only to specific cells, when the formatting needs to travel with a selected range, or when different parts of a sheet need different treatments. These approaches control display; neither deletes zero values.
What hiding zero does and does not change
A blank-looking cell is not necessarily an empty cell. With a zero-hiding number format, Excel can still use the value in arithmetic, sorting, filtering, and formula calculations. A formula such as SUM can include the zero, though adding zero does not change the total. The cell can also still be selected, edited, and referenced by other formulas.
This differs from deleting a value or using a formula that returns an empty string. A genuinely empty cell has no value entered. A formula returning "" returns text of zero length, not the numeric value zero, so it is not controlled by the zero section of a number format. Those cases can behave differently in functions, comparisons, charts, and data checks.
Formatting is also not a data-cleaning or privacy feature. A zero remains available to anyone who can inspect the cell or its formula bar, and it can reappear if someone changes the format. When a workbook is exported to a format that does not preserve Excel number formats, such as a CSV file, the exported data can contain the zero even though it looked blank in the worksheet.
Common problems and how to fix them
- The zero is still visible. Confirm that the selected cells have the custom format and that the format has an empty third section, such as
0;-0;;@. If the worksheet-wide zero display setting is enabled, review that setting too. - A value that looks like zero is not hidden. The cell may contain text such as the character 0 rather than numeric zero. In the example format, the fourth section uses
@, so text remains visible. Convert the data to numbers only if that is appropriate for its purpose. - Other numbers look wrong. The positive and negative sections determine how nonzero values display. Restore the original decimal places, separators, symbols, or parentheses in those sections, while leaving the zero section empty.
- A date or time seems affected. Dates and times are stored as numbers and displayed using number formats. Applying a general numeric format to date cells can change their appearance. Use a date-appropriate format with an empty zero section if that is the intended treatment.
- The workbook still prints or exports a zero. Check the print preview and the output file. A number format usually affects worksheet display and printing, but file formats that do not retain Excel formatting may expose the stored value.
A further source of confusion is rounding. A small nonzero value can display as 0.00 when shown with two decimal places, but it is not necessarily an actual zero. The zero section applies to a numeric value equal to zero; a small value rounded for display can still appear as zero because of the decimal format. If that matters, choose enough decimal places to distinguish the values or use a formula or data rule designed for the specific threshold.
Custom format compared with other approaches
Conditional formatting can make zero values hard to see by changing font color or applying another visual style. That approach is useful when a color cue is part of a broader rule, but it does not make the cell genuinely blank-looking in every context. A font color that matches the background may become visible when the background changes, and it can reduce readability or accessibility. A number format is usually more direct when the requirement is simply to omit zero from the displayed value.
You can also use a formula that returns an empty string for a zero result. That may suit a report where the formula itself should produce text when the result is zero, but it changes the formula’s result type and can affect later calculations or tests. If the numeric zero should remain available for calculations, formatting is generally the clearer choice.
Deleting zeros is different again: it removes or changes data rather than just its presentation. Before choosing an approach, decide whether the requirement is visual cleanup, a formula result that should be empty, or actual removal of values. They are not interchangeable.
Frequently Asked Questions
Does hiding zero with a custom format remove the value? No. The cell retains numeric zero, so formulas can still reference it. The format only suppresses its display in the formatted cell.
What is the simplest custom number format to hide zeros? Use 0;-0;;@ for whole numbers, with positive values shown, negative values marked with a minus sign, zero hidden, and text displayed normally.
Can I hide zero in just one column? Yes. Select that column or the relevant cells before applying the custom format. Other cells keep their existing formats unless they are included in the selection.
Why does a text zero remain visible? A character 0 stored as text is not numeric zero. The fourth section of a format handles text, and @ tells Excel to display text as entered.
Will the hidden zero still count in formulas? Yes. It remains a value in the cell. Its effect depends on the formula, but the number format itself does not remove it or change its calculation value.
Can I hide every zero on a worksheet without formatting cells individually? In Windows desktop Excel, use File > Options > Advanced and clear Show a zero in cells that have zero value for the chosen worksheet. This is a sheet-level display choice rather than a cell-specific format.
Related
- Contacts List Template Excel: Build a Useful Workbook
- Contact List Excel Template: Free Options and Setup
- Contact List Format in Excel: Columns, Setup, and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Tables Within Tables: What You Can Do InsteadNo. In Excel for Microsoft 365, you cannot place one structured Excel Table inside another: table objects cannot…
- Remove a Hanging Indent in a Word TableTo remove a hanging indent in a Word table, select the affected paragraph or paragraphs, open the Paragraph…
- Excel MAXIFS: Find the Highest Value by CriteriaTo find the largest value in an Excel range that meets a condition, use MAXIFS. For example, with…
- Outlook Block Sender Still Going to Junk Mail?Yes. In Outlook, blocking a sender normally sends that sender’s messages to the Junk Email folder; it does…
- Google Sheets Apps Script If Statements: How They WorkIn Google Sheets Apps Script, an if statement runs code only when a condition is true; use else…
- How to Add a Draft Watermark in Word on Every PageTo add a Draft watermark in Word for Microsoft 365 on Windows, open the document and choose Design…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.