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.
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.
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:
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:
Step 4: Then click OK. And all of the 0 values in the selection have been hidden. See screenshots:
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.
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 the 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:
Step 6: And then click OK. You will get the result that you need.
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.
Step 1: Click the Kutools > Show / Hide > Toggle Settings. See screenshot:
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:
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…
- 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