Calculation Issues

How to Fix Excel Displaying Zeros as Blank Cells

WPS Editor Oct 9, 2026 869 views

Question details

The user needs to understand and control why calculated zero values in an Excel worksheet are displaying as blank cells.

How to Fix Excel Displaying Zeros as Blank Cells

Product: Microsoft Excel
Device & OS: not provided
Scenario: The user wants to display calculated zero results in specific cells without making every empty or zero-value cell display a zero across the entire worksheet.
Observed behavior: When the 'Show a zero in cells that have zero value' setting is disabled, cells with formulas returning zero appear completely blank. When enabled, unwanted zeros appear in other empty-looking cells.

Before you start

Determine if you want zero values to be visible across the entire worksheet or only in specific cells, as Excel offers both global settings and cell-specific formatting options.

Solution 1Recommended

Enable Zero Values for the Entire Worksheet

Use this method if you want all cells containing a zero value to display the number '0' instead of appearing blank.

This is a worksheet-level setting. Changing this option will only affect the currently active worksheet.

Open Excel Options

Click on the 'File' tab in the top-left corner and select 'Options' at the bottom of the menu.

Access Advanced Settings

In the Excel Options dialog box, click on 'Advanced' in the left-hand pane.

Toggle Zero Value Display

Scroll down to the 'Display options for this worksheet' section. Check the box next to 'Show a zero in cells that have zero value' and click 'OK'.

Enable Zero Values for the Entire Worksheet

Tip: To apply this to multiple worksheets, you must repeat these steps for each sheet individually.

Solution 2

Hide or Show Zeros Using Custom Number Formatting

Apply a custom number format when you only want to change how zeros are displayed in selected cells, rather than the whole worksheet.

Solution 3

Use an IF Function to Control Zero Display

Adjust your formula to specifically return a blank or a zero based on your calculation needs without changing global settings.

WPS Spreadsheet Solution

Easily Manage Zero Values with WPS Spreadsheet

WPS Spreadsheet offers a highly compatible and intuitive interface for managing your data. You can easily control how zero values are displayed across your entire workbook or in specific cells using its straightforward built-in options.

1. Open your workbook: Launch WPS Spreadsheet and open the file where zero values are displaying as blanks.
2. Navigate to Options: Click on 'Menu' in the top-left corner and select 'Options' from the drop-down list.
3. Adjust View Settings: Go to the 'View' tab in the Options dialog box.
4. Enable Zero Values: Under the 'Window options' section, check the box for 'Zero values' to display them, or uncheck to hide them, then click 'OK'.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls).Easily toggle zero value visibility in the intuitive View settings.Free and lightweight alternative for everyday spreadsheet tasks.Familiar user interface ensuring seamless migration without a learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel formula result in a blank instead of a zero?

This usually happens when the 'Show a zero in cells that have zero value' setting is disabled in the Advanced Excel Options. Turning this setting on will restore the zeros to your formula results.

Can I hide zeros in just one specific column in Excel?

Yes. Instead of changing the worksheet settings, select the column, open the Format Cells dialog by pressing Ctrl + 1, choose Custom, and apply the format '0;-0;;@' to hide zeros in that column only.

Will printing be affected if zeros are displayed as blanks?

Yes, if Excel is set to hide zero values in the worksheet settings or via custom formatting, those cells will also appear as completely blank on the printed page.

Excel & Data Problems