Fix Excel Showing 1 or 0 Instead of the Calculated Result in WPS Office Spreadsheets
Question details
The user needs to stop Excel cells from displaying 1 or 0 when the formula bar shows the correct calculated result, and to apply the correct number format or formula settings.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains formulas whose calculated results should be visible, but affected cells show only 1 or 0.
- Observed behavior
- Cells display 1 or 0 instead of the calculated result while the formula bar still shows the correct value. The issue may come from number formatting, a logical formula wrapper, calculation mode, or column width.
Open the workbook in WPS Office Spreadsheets and identify the cells or columns that display 1 or 0. Check the formula bar to confirm the underlying calculated result is correct before changing formats.
Change the Cell Number Format to General or Number
Use this first when a cell displays 1 or 0 while the formula bar shows the correct calculated value.
The 1 or 0 display often happens because the cell is formatted as a logical, text, or custom format, or because the formula returns TRUE/FALSE. Changing the number format fixes the display without altering the formula result.
Open the workbook in WPS Office Spreadsheets and select the affected cells, for example column K.
Right-click the selection and choose Format Cells, or press Ctrl+1.
On the Number tab, select General or Number. If you choose Number, set at least one decimal place.
Click OK and verify that the cell now shows the expected calculated result instead of 1 or 0.
Remove Logical Test Wrapping from the Formula
Use this when the formula is written as a comparison or IF condition, which returns TRUE/FALSE as 1/0 in the cell.
Set Calculation Mode to Automatic and Adjust Column Width
Use this after the number format is correct but the cell still shows 1 or 0 due to calculation or display constraints.
Fix 1/0 Display by Reformatting Cells in WPS Office Spreadsheets
Use WPS Office Spreadsheets to open the Excel workbook, change the cell number format to General or Number, and confirm the calculated result appears correctly instead of 1 or 0.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office and open the file where cells display 1 or 0 instead of calculated results.
- 2. Select the affected cells: Click and drag or press Ctrl+A to select the affected range, such as column K.
- 3. Open Format Cells: Right-click the selection and choose Format Cells, or press Ctrl+1.
- 4. Apply General or Number format: On the Number tab, choose General or Number with at least one decimal place, then click OK.
- 5. Verify the result: Check that the cell now displays the calculated result and matches the formula bar.
Frequently Asked Questions
Why does Excel show 1 or 0 in a cell when the formula bar shows the correct result?
The cell number format may be set to a logical or text format, or the formula may return TRUE/FALSE. Select the cell, press Ctrl+1, and change the format to General or Number.
How do I change a column like K from showing 1/0 back to decimal numbers in WPS Office Spreadsheets?
Select column K, right-click and choose Format Cells, then select General or Number with at least one decimal place and click OK.
Will changing the number format affect the underlying formula result?
No. The number format only changes how the value is displayed; the formula and calculated result remain unchanged.
What if the cell still shows 1 or 0 after applying General format?
Click the cell and check the formula bar for an IF or comparison expression, then go to Formulas > Calculation Options and make sure Automatic is selected.
Can I prevent this issue when opening Excel files in WPS Office?
Yes. Keep the source columns formatted as General or Number and avoid wrapping numeric calculations in logical tests that return TRUE/FALSE.
