Your Excel sheet may show distracting zeros in cells with formulas or blank data. These zeros can clutter your view and make it harder to spot actual numbers. Excel provides two main methods to hide these zero values. This article explains how to use a global workbook setting and a more flexible custom number format.
Key Takeaways: Hiding Zero Values in Excel
- File > Options > Advanced > Display options for this worksheet: A global setting that hides all zeros in the selected worksheet.
- Custom number format using a semicolon: A cell-level method that hides zeros while showing positive and negative numbers with specific formatting.
- Ctrl+1 to open Format Cells: The fastest way to apply a custom number format to selected cells.
Two Ways to Suppress Zero Values
The first method uses an Excel option to hide every zero value in a worksheet. This affects cells containing the number 0, formulas that return 0, and blank cells formatted as General or Number. It is a simple on/off switch for the entire sheet.
The second method uses a custom number format. This gives you precise control. You can hide zeros in specific cells while keeping other number formats intact. This format uses sections separated by semicolons to define how positive numbers, negative numbers, zeros, and text are displayed. By leaving the zero section blank, you tell Excel to show nothing for a zero value.
Steps to Hide Zeros via Excel Options
- Open the Excel Options dialog
Click File on the ribbon, then select Options at the bottom of the left menu. - Navigate to the Advanced settings
In the Excel Options window, click Advanced in the left-hand pane. - Find the display settings for your worksheet
Scroll down to the section titled Display options for this worksheet. Use the drop-down menu to select the specific worksheet where you want to hide zeros. - Uncheck the box for zero values
Find the setting called Show a zero in cells that have zero value. Click the checkbox to remove the checkmark. Click OK to apply the change.
Steps to Hide Zeros with a Custom Format
- Select the target cells
Click and drag to select the cell or range where you want to hide zeros. This method works best for specific data ranges. - Open the Format Cells dialog
Press Ctrl+1 on your keyboard. You can also right-click the selected cells and choose Format Cells from the menu. - Go to the Number tab
In the Format Cells dialog, ensure the Number tab is selected at the top. - Choose Custom category
In the Category list on the left, click Custom. - Enter the custom format code
In the Type field, you will see the current format. Replace it with one of these codes:To hide zeros but keep default number formatting:
0;-0;;@
To hide zeros and show two decimal places:0.00;-0.00;;@
To hide zeros and show currency:$#,##0.00;-$#,##0.00;;@The code structure is Positive;Negative;Zero;Text. The blank section between the second and third semicolon hides the zero.
- Apply the format
Click OK. The selected cells will now display blank when their value is zero.
Common Mistakes and Formatting Limits
Hidden Zeros Still Appear in Print Preview
The Excel Options method only hides zeros on screen. They may still appear when you print the sheet. To remove them from printouts, you must also check the page setup. Go to Page Layout > Page Setup dialog launcher > Sheet tab. Check the box for Print cell errors as, and select blank from the drop-down menu next to it.
Custom Format Shows Dashes for Zeros
If you see a dash (-) instead of a blank cell, your custom format code has a dash in the zero section. Edit the custom format and ensure the section for zero values, which is the third section, is completely empty. The correct code should have two consecutive semicolons where the zero format would be.
Pivot Table Zeros Are Not Hidden
The Excel Options setting does not affect PivotTables. To hide zeros in a PivotTable, you must use its specific setting. Right-click anywhere in the PivotTable, select PivotTable Options. In the Layout & Format tab, check the box for For empty cells show and leave the input field blank.
Global Option vs Custom Format: Key Differences
| Item | Excel Options Method | Custom Number Format |
|---|---|---|
| Scope of Control | Entire worksheet | Selected cells only |
| Affects Formulas | Hides all formula results of 0 | Hides zero only in formatted cells |
| Print Behavior | Zeros may still print | Zeros are hidden in printouts |
| Flexibility | Simple on/off toggle | Allows separate formats for positive/negative numbers |
| Best For | Cleaning up an entire sheet view | Creating polished reports with specific number formats |
You can now clean up your worksheets by hiding zero values. Use the Excel Options method for a quick whole-sheet cleanup. Apply a custom number format like 0.00;-0.00;;@ for precise control in reports. For advanced control, combine the custom format with conditional formatting to change cell color based on other values.