Why Excel Displays 1 and 0 Instead of TRUE and FALSE in Comparison Formulas
Question details
Clarify why an Excel comparison formula shows numeric 1 or 0 rather than the logical values TRUE or FALSE, and how to restore the expected result.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working in a spreadsheet and expects a comparison formula to return logical TRUE or FALSE values.
- Observed behavior
- The formula cell shows numeric 1 or 0 instead of TRUE or FALSE.
Open the affected workbook and select a formula cell that returns 1 or 0. Inspect the formula bar for functions or operators such as N(), --, *1, +0, or IF(...,1,0) that convert logical results to numbers.
Remove Boolean-to-Number Conversions from the Comparison Formula
Use this when the formula contains N(), double unary operators, arithmetic operations, or IF(...,1,0) that intentionally or unintentionally convert logical values to numeric 1 and 0.
Excel normally returns TRUE or FALSE for a direct comparison. Boolean conversions such as --, N(), or adding 0 force the result into numeric 1 or 0.
Click the cell that shows 1 or 0 and inspect the formula in the formula bar.
Look for double unary operators like --(A1=B1), the N() function, arithmetic such as (A1=B1)*1 or (A1=B1)+0, or IF(A1=B1,1,0).
Edit the formula to a direct comparison, for example =A1=B1, and press Enter.
Select the cell, go to the Home tab, and confirm the Number format is set to General.
Return Logical TRUE or FALSE Explicitly with IF
Use this when you need to keep an existing calculation but want to ensure the final cell result is the logical value TRUE or FALSE.
Fix Comparison Formula Results in WPS Office Spreadsheets
Open the workbook in WPS Office Spreadsheets, locate the comparison formula, and remove the numeric conversion so it returns TRUE or FALSE. WPS keeps formulas compatible with Microsoft Excel while providing a familiar ribbon interface.
- 1. Open the file in WPS Office Spreadsheets: Launch WPS Office, click Menu, then Open, and select the Excel workbook that contains the affected formula.
- 2. Locate the formula cell: Click the cell displaying 1 or 0 and view the formula in the formula bar.
- 3. Edit the comparison formula: Remove conversions such as --, N(), *1, or IF(...,1,0) and enter a direct comparison like =A1=B1.
- 4. Verify the result and format: Press Enter and go to the Home tab, then set the Number format to General.
Frequently Asked Questions
Why does my Excel comparison formula show 1 and 0 instead of TRUE and FALSE?
Excel returns TRUE or FALSE for a direct comparison such as =A1=B1. If the formula contains --, N(), *1, +0, or IF(...,1,0), these operations convert logical values into numeric 1 and 0.
How do I change 1 and 0 back to TRUE and FALSE?
Edit the formula and remove the conversion. Use a direct comparison like =A1=B1, or explicitly return logical values with =IF(A1=B1,TRUE,FALSE).
Does changing the cell number format fix 1 and 0 results?
Not always. Check the formula first; if the result is actually numeric, format changes will not make it logical. After fixing the formula, set the cell format to General on the Home tab.
Will WPS Office open an Excel file that returns 1 and 0?
Yes. WPS Office Spreadsheets opens .xlsx and .xls files and lets you edit the same formulas so the result displays as TRUE or FALSE.
