Formatting Issues

How to Hide Zeros and Preserve Decimals with Excel Custom Number Formats

Elise Williams Oct 1, 2026 871 views

Question details

The user needs to format spreadsheet cells to hide zero values completely while maintaining the default display of whole numbers without decimals and non-integers with their respective decimal places.

How to Hide Zeros and Preserve Decimals in Excel

Product: Excel
Device & OS: not provided
Scenario: Cleaning up a spreadsheet presentation where zero values clutter the view, but varying decimal places still need to display correctly for non-zero numbers.
Observed behavior: Standard General formatting correctly handles decimals but displays zero values instead of leaving the cell blank.

Before you start

Select the specific cells or range where you want this formatting applied. Keep in mind that hiding zeros changes the display only; the actual zero value remains in the cell for formulas and calculations.

Solution 1Recommended

Use Custom Number Format to Suppress Zeros

Apply a custom format structure to suppress zero values while keeping the default General formatting for positive and negative numbers.

Excel custom formats are divided into four sections separated by semicolons: Positive; Negative; Zero; Text. By defining the Positive and Negative sections as 'General' and leaving the Zero section empty, Excel will render zeros as blank cells.

Select your data range

Highlight the cells, rows, or columns where you want to hide the zero values.

Open the Format Cells dialog

Right-click the selected cells and choose 'Format Cells', or use the keyboard shortcut Ctrl+1 (Cmd+1 on Mac).

Navigate to Custom formatting

In the Number tab, select 'Custom' from the Category list on the left side.

Apply the format code

In the 'Type' input box, enter `General;General;` and click 'OK'.

Use Custom Number Format to Suppress Zeros

Format Breakdown: The code `General;General;` explicitly tells Excel: format positive numbers as General, format negative numbers as General, and do nothing (leave blank) for zero values.

WPS Spreadsheet Solution

Easily Hide Zeros and Format Numbers in WPS Office

WPS Spreadsheet provides robust custom cell formatting, allowing you to easily hide zero values, manage decimal places, and organize your data seamlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the zero values.
2. Select the target cells: Click and drag to highlight the specific cells that require zero suppression.
3. Access the Format menu: Right-click the selection and choose 'Format Cells', or press the Ctrl+1 shortcut.
4. Apply the custom rule: Go to 'Custom', type `General;General;` into the Type box, and click 'OK' to instantly hide the zeros.

Fully compatible with Microsoft Excel custom number formats (.xlsx).Quickly hide zero values and manage complex decimal formats via the intuitive Format Cells menu.Lightweight, fast, and free alternative for all your spreadsheet data analysis needs.

microsoft office alternative - wps office

Frequently Asked Questions

Will hiding zeros affect my formulas or sums?

No, hiding zeros with custom formatting only alters the visual display of the cell. The underlying value is still recognized as zero by Excel and functions perfectly in all calculations, formulas, and charts.

How do I hide zeros in an entire worksheet without using custom formats?

You can hide all zero values across an entire sheet by navigating to File > Options > Advanced. Scroll down to 'Display options for this worksheet' and uncheck the box labeled 'Show a zero in cells that have zero value'.

How can I revert the cells to show zeros again?

To restore the zero values, select the affected cells, press Ctrl+1 to open the Format Cells dialog, and change the category from 'Custom' back to 'General' (or your preferred standard number format).

Can I hide zeros but keep a specific decimal length like two decimal places?

Yes. Instead of using 'General', you can specify decimal places in the custom format. For example, to enforce two decimal places while hiding zeros, use the custom format code `0.00;-0.00;`.

Document & File Issues