How to Display Blank Instead of Zero in Excel Currency Cells in WPS Office
Question details
The user wants currency-formatted formula cells in an Excel-compatible spreadsheet to appear blank instead of showing a zero currency placeholder such as '$ -' when the source value is empty.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is maintaining a workbook with currency cells that pull values from another sheet and wants empty source cells to produce a blank display rather than a formatted zero.
- Observed behavior
- Zero-value currency cells currently display as a currency placeholder such as '$ -' instead of remaining blank.
Open the workbook in WPS Spreadsheets and identify the currency-formatted cells or formulas that display '$ -'. If the cells are linked to a source sheet, note the exact source cell references before editing the formula.
Hide Zero Values with a Custom Currency Number Format
Use this method when you want to keep the existing formulas intact and simply stop zero values from displaying in the selected currency cells.
A custom number format uses three sections separated by semicolons: positive;negative;zero. Leaving the zero section empty makes any evaluated zero appear blank while preserving normal currency formatting for positive and negative values.
Select the currency-formatted cells where zero values should appear blank.
Right-click the selection and choose Format Cells, or press Ctrl + 1.
On the Number tab, select Custom in the Category list.
In the Type box, enter $#,##0.00;-$#,##0.00; and click OK.
Return Blank from Formulas When the Source Cell Is Empty
Use this method when the zeros are formula results and you want the formula cell to be truly empty instead of only visually blank.
Hide Zero Currency Cells in WPS Spreadsheets
Apply a custom currency format or an IF formula in WPS Spreadsheets so formula cells with no source value stay blank instead of showing '$ -', while keeping your workbook fully compatible with Microsoft Excel.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook that contains the currency cells.
- 2. Select the currency cells: Highlight the cells or columns where zero currency values should appear blank.
- 3. Apply the custom currency format: Right-click the selection, choose Format Cells, select Custom, enter $#,##0.00;-$#,##0.00; and click OK.
- 4. Use an IF formula for linked source cells: If the zero comes from a formula, enter =IF(SourceSheet!A1="","",SourceSheet!A1) with the correct sheet and cell reference.
- 5. Save the Excel-compatible file: Press Ctrl + S to save the workbook in its existing .xlsx or Excel-compatible format.
Frequently Asked Questions
Why does my currency cell show '$ -' instead of blank?
WPS Spreadsheets treats an empty source cell used in a formula as zero, and the default currency format displays that zero as '$ -'. You can hide it with a custom format such as $#,##0.00;-$#,##0.00; or prevent it with an IF formula.
Will the custom currency format change my positive or negative values?
No. The format uses three sections: positive;negative;zero. When you enter $#,##0.00;-$#,##0.00; the positive and negative sections keep their currency formatting, while the empty zero section hides only zero values.
Does the IF formula method return a true blank cell?
The formula =IF(SourceSheet!A1="","",SourceSheet!A1) returns an empty text string, not a true blank numeric cell. It stops the zero from appearing but may affect calculations that treat the result as text.
Can I apply this to an entire column?
Yes. You can select an entire column in WPS Spreadsheets, open Format Cells > Custom, and apply $#,##0.00;-$#,##0.00;. For the formula method, enter the IF formula in the first row and drag the fill handle down the column.
Does this work for other currency symbols like € or £?
Yes, replace the dollar sign in the custom format with your currency symbol, for example €#,##0.00;€-#,##0.00; or £#,##0.00;-£#,##0.00;.
