How to Hide Negative Numbers or Display Them as Zero in WPS Office Spreadsheets
Question details
The user wants to hide negative numbers in a WPS Office spreadsheet or make them display as zero while keeping the underlying values intact.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is formatting a worksheet for reporting or presentation and needs negative values to be hidden or shown as zero without deleting the original data.
- Observed behavior
- Negative numbers currently appear normally in cells, and the user needs a display-only or formula-based method to suppress them or show zero.
Open your workbook in WPS Office Spreadsheets and select the cell or range that contains the negative numbers. Decide whether you need a display-only change with custom number formatting or a calculated result that clamps negative values to zero.
Hide negative numbers with a custom number format
Use this solution when you want to keep the real cell values but hide negative results from view.
Custom number formatting changes only how a cell appears. The stored number is not changed, so formulas and totals continue to use the original values.
Open your worksheet in WPS Office Spreadsheets and select the cells or range that contains the negative numbers.
Press Ctrl+1 or right-click the selection and choose Format Cells.
Go to the Number tab, select Custom, and enter 0;;0;@ in the type field.
Click OK. WPS Office Spreadsheets now displays positive numbers, zero, and text while negative numbers are hidden.
Display negative numbers as zero with a formula or custom format
Use this solution when negative values should visibly read as zero, either as a calculated result or as a display format.
Control Negative Number Display in WPS Office Spreadsheets
Use WPS Office Spreadsheets to apply custom number formats or formulas that hide negative values or display them as zero, keeping your workbook data and formulas intact.
- 1. Open your worksheet: Launch WPS Office Spreadsheets, open the Excel-compatible workbook, and select the range with negative numbers.
- 2. Open the custom number format dialog: Press Ctrl+1 or right-click and choose Format Cells to open the Number tab.
- 3. Apply the hiding or zero-display format: Select Custom and enter 0;;0;@ to hide negatives, or enter 0;0;0;@ to display them as zero, then click OK.
- 4. Optionally add a clamping formula: Enter =MAX(0,A1) in a helper column to return zero instead of a negative value.
Frequently Asked Questions
Does the custom number format change the stored cell value?
No. In WPS Office Spreadsheets, a custom format such as 0;;0;@ only changes how the cell displays. The original value remains in the cell and is still used in formulas and totals.
How can I make a formula return zero instead of a negative number?
Wrap the calculation in the MAX function, for example =MAX(0,A1-B1). This returns 0 when the result is negative and the actual result when it is positive.
Which custom format displays negative numbers as zero but keeps positive numbers and text?
Select the cells, press Ctrl+1, choose Custom, and enter 0;0;0;@. This format shows positive values normally, zero for negative values, zero for zero, and text as entered.
Are hidden negative numbers still included in SUM and other calculations?
Yes. A custom number format only affects display, so hidden negative values are still included in SUM, AVERAGE, and other formulas.
Can I apply this format to a whole row or column?
Yes. Select the row, column, or entire sheet before opening Format Cells, then apply the custom format. It will apply to all selected cells that contain numbers.
