Formula Errors

Fix WPS Spreadsheet Formulas That Do Not Treat Blank Cells as Zero

Kushani NimanthikaKushani Nimanthika Nov 13, 2026 870 views

Question details

How to resolve #VALUE! errors in WPS Spreadsheets when formulas reference cells that appear blank but contain spaces, empty strings, or hidden characters instead of a numeric zero.

Fix WPS Spreadsheet Formulas That Do Not Treat Blank Cells as Zero
Product
WPS Office Spreadsheets
Device & OS
not provided
Scenario
A user is building formulas in WPS Spreadsheets and expects empty-looking cells to calculate as zero, but the formulas return #VALUE! instead.
Observed behavior
Cells that look blank are actually not empty; they contain spaces, formulas returning empty strings, or imported hidden characters, so the formula treats them as text and returns #VALUE!.
Before you start

Before editing formulas, select the affected range and check the cells with Ctrl+G > Go To Special > Constants or Formulas to confirm whether the cells are truly empty or contain text, spaces, or empty strings.

Solution 1Recommended

Use the N Function to Convert Nonnumeric or Blank-Like Cells to Zero in WPS Spreadsheets

Use this solution when you cannot easily clean the source cells and need a formula to treat any nonnumeric value as zero without changing the original data.

The N function returns the numeric value of a reference. If the referenced cell contains a number, N returns that number; if it contains text, an empty string, or another nonnumeric value, N returns 0.

1
Select the result cell

Click the cell where you currently see the #VALUE! error in WPS Spreadsheets.

2
Wrap the reference with N

Replace your formula reference with N(reference). For example, change =10*A1 to =10*N(A1).

3
Press Enter and verify

Press Enter and confirm that cells containing spaces, empty strings, or text now calculate as zero.

Use the N Function to Convert Nonnumeric or Blank-Like Cells to Zero in WPS Spreadsheets
Recommended for formula-only fix: N() returns 0 for nonnumeric values while keeping actual numbers unchanged, so it resolves the #VALUE! error without changing the source data.
Formula error fix

Fix Formula Errors in WPS Spreadsheets by Treating Blank Cells as Zero

WPS Spreadsheets supports Excel formulas and functions such as N, TRIM, and CLEAN. Use these functions to correct #VALUE! errors caused by cells that look blank but contain text, spaces, or empty strings.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx workbook that contains the #VALUE! errors.
  2. 2. Select the affected formula cell: Click the cell showing #VALUE! and inspect the referenced cells in the formula bar.
  3. 3. Wrap the referenced cell with N: Change the formula to use N(reference), for example =10*N(A1), and press Enter.
  4. 4. Clean source cells if needed: Use Ctrl+H to remove spaces and =TRIM(CLEAN(A1)) to remove hidden characters, then replace values with Paste Special > Values.
  5. 5. Save in Excel-compatible format: Save the workbook as .xlsx to keep compatibility with Microsoft Excel.
Uses familiar Excel-compatible functions such as N, TRIM, and CLEAN to fix #VALUE! errorsOpens and saves Microsoft Excel .xlsx files with full format compatibilityLets you test and correct formulas without changing the source data when neededFree alternative to Microsoft Excel for daily spreadsheet tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does my WPS formula return #VALUE! when a cell looks empty?

The cell is likely not truly empty. It may contain a space, an empty string returned by another formula, or hidden imported characters. Select the cell and check the formula bar or use Go To Special to inspect its contents.

How can I make an empty-looking cell calculate as zero without deleting the original data?

Wrap the cell reference in the N function. For example, change =10*A1 to =10*N(A1). N returns 0 for text, spaces, and empty strings while keeping actual numbers unchanged.

How do I remove hidden spaces or characters from imported data in WPS Spreadsheets?

Use the TRIM and CLEAN functions in a helper column, such as =TRIM(CLEAN(A1)), then copy the helper column and paste as values over the original range. You can also press Ctrl+H to replace ordinary spaces with nothing.

Does the N function work when the cell contains an error like #VALUE!?

No, N returns 0 for text and empty strings, but it does not suppress errors that already exist in the referenced cell. Fix the underlying error or use IFERROR if you need to handle actual errors.