Office Settings & Configuration

How to Hide All Zero Values in an Excel Worksheet

WPS Editor Oct 12, 2026 871 views

Question details

The user wants to hide multiple zero values across a large worksheet quickly without modifying or deleting the original data.

How to Hide All Zero Values in an Excel Worksheet

Product: Excel
Device & OS: not provided
Scenario: Cleaning up or formatting a large dataset to make it easier to read by making zero values invisible.
Observed behavior: The worksheet displays numerous zero values that visually clutter the data presentation.

Before you start

Ensure you have the correct worksheet active before changing display settings, as this option applies on a per-worksheet basis rather than to the entire workbook.

Solution 1Recommended

Hide Zero Values via Excel Advanced Options

The most efficient way to hide all zero values in an active worksheet without deleting the underlying data.

This built-in feature allows you to toggle the visibility of zero values for an entire worksheet at once. It is completely safe, as it only affects how the data is displayed and does not delete or alter your original dataset.

Open Excel Options

Click on 'File' in the top-left corner of the ribbon, then select 'Options' at the bottom of the left-hand menu.

Navigate to Advanced Settings

In the Excel Options dialog box that appears, click on 'Advanced' in the left pane.

Locate Display Options

Scroll down the right pane until you find the section titled 'Display options for this worksheet'. Ensure your target worksheet is selected in the drop-down menu.

Clear the Zero Value Checkbox

Uncheck the box next to 'Show a zero in cells that have zero value' and click 'OK' at the bottom to apply the changes.

Hide Zero Values via Excel Advanced Options

Restoring Zero Values: The zero values remain safely in your cells. You can easily display them again at any time by returning to this menu and re-checking the option.

WPS Spreadsheet Solution

Hide Zero Values Easily in WPS Spreadsheet

WPS Spreadsheet offers an intuitive and free way to manage worksheet displays, including hiding zero values across large datasets. It features full compatibility with Microsoft Excel formats and provides a highly familiar interface.

1. Open your Worksheet: Launch WPS Spreadsheet and open the document containing the zero values you wish to hide.
2. Access the Options Menu: Click on 'Menu' in the top-left corner, then click the 'Options' button at the bottom right of the drop-down panel.
3. Navigate to View Preferences: In the Options dialog box, click on the 'View' tab located in the left sidebar.
4. Disable Zero Values: Under the Window options section, uncheck the 'Zero values' box and click 'OK' to apply.

Hide zero values with just a few clicks in the settings menu.Fully compatible with Microsoft Excel (.xlsx and .xls) file formats.Lightweight software that processes large datasets smoothly.Free to download and use for your daily office needs.

microsoft office alternative - wps office

Frequently Asked Questions

Can I hide zero values in specific cells instead of the whole worksheet?

Yes. Select the specific cells, right-click, and choose 'Format Cells'. Go to the 'Custom' category in the Number tab and type `0;-0;;@` into the Type box. This custom number format will hide zeros only in your selected range.

Will hiding zero values affect my formulas or calculations?

No. Hiding zero values only changes how the data is visually displayed on the screen. The underlying zero value remains in the cell and will still be calculated normally in any charts, formulas, or pivot tables.

How do I print a worksheet without showing the zero values?

If you hide zero values using the Excel Options method or custom cell formatting, they will automatically be hidden when you print the worksheet as well. No additional print settings are required.

Why are my zero values still showing after unchecking the option?

Ensure that you selected the correct worksheet from the drop-down list under 'Display options for this worksheet' before unchecking the box. This setting applies on a per-worksheet basis, not to the entire workbook by default.

Office App Problems