How to display or hide zero values in cells in Microsoft Excel?

Suppose you enter a zero value in a cell of Microsoft Excel, but this zero value does not appear; or you want to hide zero value in the worksheet. And so, here comes the following tricks to display or hide zero values in cells.

Display or hide zero values in a selection with Format Cells command

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

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Display or hide zero values in a selection with Format Cells command

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

The Format Cells command can help you hide all zero value in cells of a selection.

Step 1: Select the range that contains the zero (0) values that you want to hide.

Step 2: Right click the selected range, choose Format Cells from the context menu. See screenshot:

doc-hide-display-0-values1

Step 3: In the Format Cells dialog box, click Number button from the tab, and then click Custom from the Category list. In the Type box, type 0;-0;;@. See screenshot:

doc-hide-display-0-values2

Step 4: Then click OK. And all of the 0 values in the selection have been hidden. See screenshots:

doc-hide-display-0-values3-2doc-hide-display-0-values4

Notes: 1. Hidden 0 values will still be visible in the Formula bar, or in the cell, if you edit in the cell.

2. If you want to display the 0 values, only need to click General from the Category list of the Format Cells dialog box.


arrow blue right bubble Display or hide zero values in specified worksheet with Excel Options

With the Excel Options function, you can display or hide 0 values of the specified worksheet. Do as follows:

Step 1: Click the File tab in Excel 2010/2013, or click thedoc-office-2007-button button in Excel 2007.

Step 2: Click the Options button.

Step 3: In Excel Options dialog box, go to the Advanced category.

Step 4: Scroll down to the Display options for this worksheet section, and click the drop down list to choose the worksheet that you need to display or hide the 0 values.

Step 5: Check or uncheck the Show a zero in cells that have zero value option according to your needs. See screenshot:

doc-hide-display-0-values5

Step 6: And then click OK. You will get the result that you need.


arrow blue right bubble Display or hide zero values in active worksheet or whole workbook with Kutools for Excel

Kutools for Excel's Toggle Settings will relieve you from the complicated steps, and help you display or hide zero values in the active worksheet or whole workbook with only a few steps.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

Step 1: Click the Kutools > Show / Hide > Toggle Settings. See screenshot:

doc-hide-display-0-values6

Step 2: Check or uncheck the Display Zeros option in the Toggle Settings dialog box. Then click Close button. (If you want to display or hide all of the 0 values of the whole workbook, click Apply to all sheets button.) See screenshot:

doc-hide-display-0-values7

The Toggle Settings of Kutools for Excel makes it possible to show or hide most of Microsoft Excel settings quickly, such as Inner Tabs, Formula Bar, Status Bar, Windows in Taskbar, Gridlines, Page Breaks, Display Zeros, Vertical Scroll bar, Horizontal Scroll bar, Sheet Tab, … etc. It will save your time in looking for these setting when you need to show or hide them. Click to know more…


Related articles:


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Add comment


Security code
Refresh