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.
Recommended Productivity SoftwareOffice Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).
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
Is your problem solved?
Recommended Productivity Tools
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
More than 120 powerful advanced functions which designed for Excel:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...