How to display or hide zero values in cells in Microsoft Excel?
In day-to-day work with Excel worksheets, handling the presentation of zero values is essential for readability and data clarity. There are times when zero values in cells can clutter the sheet or distract from important data, and in other situations, displaying zeros is necessary for completeness or calculation integrity. Whether you need to simply mask zeros in certain cells, adjust visibility for entire sheets, or automate this process, Excel provides several methods to suit different needs. Below, you will find multiple practical techniques to display or hide zero values in cells, along with helpful explanations and step-by-step guidance for each approach.
Display or hide zero values in a selection with Format Cells command
Quickly display or hide all zero values in active worksheet
Display or hide zero values in specified worksheet with Excel Options
Display or hide zero values in active worksheet or whole workbook with Kutools for Excel
Excel Formula - Display a blank cell instead of zero using IF
VBA Code - Automate hiding/display of zero values by cell format or worksheet settings
Display or hide zero values in a selection with Format Cells command
The Format Cells command provides a straightforward way to hide zero values in a designated range of cells. This is useful when you need to conceal zeros only within a specific data area, such as financial reports, budgeting lists, or any dataset where zeros may distract from meaningful values.
1. Select the range that contains the zero (0) values you wish to hide. Common scenarios include data tables, summary sheets, or sections where zeros are not helpful for interpretation.
2. Right-click the selected range, and choose Format Cells from the context menu. See screenshot:
3. In the Format Cells dialog box, go to the Number tab, click Custom in the Category list. In the Type box, enter 0;-0;;@. This custom format will cause positive and negative numbers to appear while zeros are hidden in the formatted cell area. See screenshot:
4. Click OK to confirm. All cells with zero values in your selected range are now hidden, making your worksheet less cluttered and easier to interpret. See screenshots:
Notes:
- Hidden zero values are still present in the cell's contents and calculation logic—they remain visible in the Formula bar and if you edit the cell directly.
- To show zero values again, re-open Format Cells and select General from the Category list, or remove the custom formatting.
- This method is ideal for selectively hiding zeros and does not affect the entire worksheet or other ranges.
- If you copy formatted cells to a new sheet, the custom format remains; check format details especially when sharing files with others.
Quickly display or hide all zero values in active worksheet with only one click
By using the Show Zero utility in Kutools for Excel, you can switch zero values on or off in the active worksheet instantly. This function is particularly useful when reviewing full-sheet data and needing a simple toggle between showing or hiding all zeros without manually adjusting each cell's format or worksheet options.
1. Click Kutools Plus > Worksheet Design. See screenshot:
2. Once the Design tab appears on the ribbon, simply check the Show Zero box in the View group to display zero values in the active sheet, or uncheck the Show Zero box to hide all zero values. This quick toggle improves worksheet readability and can prevent unnecessary distractions from zero entries.
Applicable scenarios include preparing reports for meetings, creating print-friendly versions of your worksheet, or conducting data analysis where unnecessary zeros might reduce clarity. The display can be toggled back and forth as needed, and does not change data content—only the viewing setting in Excel.
If you encounter any issues where the toggle does not work, check your worksheet protection settings or ensure that Kutools for Excel is properly installed and the worksheet is activated.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Display or hide zero values in specified worksheet with Excel Options
The Excel Options feature allows you to control the visibility of zero values for specific worksheets. This solution is recommended for cases where you routinely work with large data sheets and need consistent zero visibility settings across an entire worksheet, rather than specific cell ranges.
1. Click the File tab in Excel 2010 or newer versions, or click the 
button in Excel 2007 to access the Backstage view.
2. Select Options to open the Excel Options dialog box.
3. In the Excel Options window, navigate to the Advanced category.
4. Scroll down to locate the Display options for this worksheet section. Use the dropdown to choose the worksheet for which you want to adjust zero value display.
5. Check or uncheck the Show a zero in cells that have zero value option as needed. See screenshot:
6. Click OK to apply your changes. Zero values will be shown or hidden throughout the entire selected worksheet according to your choice.
- Changing this setting only affects the selected worksheet and not others in the workbook.
- For worksheets shared with others or printed, ensure the visibility setting matches your presentation needs. The actual cell values remain unchanged for formulas and calculations.
- If you notice zeros not being hidden or displayed as expected, double-check the chosen worksheet in the dropdown before applying the setting.
Display or hide zero values in active worksheet or whole workbook with Kutools for Excel
Kutools for Excel's View Options feature simplifies the process of displaying or hiding zero values in your current worksheet or across all worksheets. Especially when working with multiple sheets in a workbook, manually adjusting settings for each one can be time-consuming, so this utility streamlines the task.
1. Go to Kutools > Show & Hide > View Options. See screenshot:
2. In the View Options window, uncheck the Zero values box under Window options to hide all zero values. If you want to apply this hiding to all worksheets in your workbook, use the Apply to all sheets button. If only for the current worksheet, just click OK after unchecking the box.
To display all zero values again, recheck the Zero values box and apply your changes. See screenshot:
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
This method is suitable for data management tasks or when working with various worksheets that require uniform formatting. It enhances consistency and saves time, especially on larger workbooks. Note that zero values remain part of calculations and formulas—they are simply not shown in cells unless you choose to display them again.
Excel Formula - Display a blank cell instead of zero using IF
If you need to hide zero values only in certain situations—such as outcomes of calculations where zero results are not meaningful or useful—you can use an Excel formula to conditionally show blanks instead of zeros. This approach is especially useful for custom reports, dashboards, or lists where zeros are undesirable for specific cells, and you want more control over when they appear.
Typical scenario: You want column B to display the result of a calculation in column A, but if the result is zero, you'd rather display a blank cell for clarity.
1. Enter the following formula in the target cell (for example, enter in cell B1):
=IF(A1=0,"",A1)2. Press Enter to confirm. The formula will return the value from cell A1 if it is not zero; if A1 is zero, B1 will appear empty.
You can drag the formula from B1 down to other cells in column B to apply the same logic for multiple rows: select B1, then drag the fill handle (small square at bottom-right corner of B1) down to fill the formula to other rows.
- This formula is most effective when you want only certain results to appear blank, rather than hiding zeros workbook-wide.
- If working with results of multiple conditions, you can modify the formula to
=IF(A1="", "", IF(A1=0, "", A1))to accommodate blanks as well as zeros. - Remember, although a cell appears blank, it contains a formula, so copying or referencing from these cells requires attention to formulas.
VBA Code - Automate hiding/display of zero values by cell format or worksheet settings
For more advanced control, such as automating zero value hiding across large ranges or multiple worksheets—especially when frequent toggling or customization is needed—you can use a VBA macro. This is well-suited for periodic reporting, workbook templates, or automating tasks where Excel's built-in options are too limited or manual work is excessive.
Advantages: Ability to quickly process entire ranges, apply special formatting, and set workbook-wide display options without manual intervention.
1. To use VBA, click Developer Tools > Visual Basic. The Microsoft Visual Basic for Applications window will appear. Click Insert > Module, and paste the following code into the module area:
Sub HideZeroValuesWithFormat()
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.InputBox("Select Range to Hide Zeros:", xTitleId, Selection.Address, Type:=8)
If Not WorkRng Is Nothing Then
WorkRng.NumberFormat = "0;-0;;@"
End If
End Sub2. Click the 
Run button in VBA to execute. A dialog will prompt you to select a range for hiding zeros. After confirming, zero values in your selected range will be hidden using custom cell formatting.
To display zeros again: Use a similar macro to reset the format:
Sub ShowZeroValuesWithFormat()
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.InputBox("Select Range to Show Zeros:", xTitleId, Selection.Address, Type:=8)
If Not WorkRng Is Nothing Then
WorkRng.NumberFormat = "General"
End If
End SubTips and precautions:
- Macros operate on areas you select, so always confirm the necessary range before running the code.
- This macro does not physically remove zeros—it only hides their display.
- Excel security settings may restrict macro execution; enable macros for full functionality.
- If you need to process all worksheets at once, consider looping through
Worksheetscollection.
Summary and troubleshooting suggestions: When hiding or displaying zero values, always consider the context—financial statements may require zeros visible to demonstrate completeness, while data entry sheets may benefit from cleaner views. If changes are not reflected, double-check formatting, worksheet options, and the applicability of your chosen settings. For recurring needs or batch operations, automate with formulas or VBA for efficiency. Kutools for Excel remains a highly practical choice for simplifying display management across large workbooks.
Related articles:
- Show or hide formula bar
- Display or hide row & column headers
- Display Selection Pane
- Display or hide status bar
- Display or hide sheet tabs
- Show or hide Horizontal/Vertical scroll bar
- Show or hide gridlines
- Hide page breaks
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in