# How to convert TRUE and FALSE to 1 and 0 in Excel

Put two minus signs in front: =--B2 turns TRUE into 1 and FALSE into 0.

**Excel:** `=--B2` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Respondent | Opted in | As number |
| R-01 | true |  |
| R-02 | false |  |
| R-03 | true |  |
| R-04 | true |  |
| R-05 | false |  |
| R-06 | false |  |
| R-07 | true |  |

Formula in C2, filled to C8: `=--B2`

Result: 1; 0; 1; 1; 0; 0; 1

## How it works

The double minus is two negations: -TRUE is -1 and -(-1) is 1, while FALSE stays 0. Any arithmetic converts a logical value to a number; this is the shortest form, and the one you will see inside SUMPRODUCT.

## Other ways

- `=IF(B2,1,0)`: Spells the conversion out and is easy to change to other values, like "Yes" and "No".
- `=B2*1`: Multiplying by 1 (or adding 0) converts the same way.

## Common mistakes

- SUM skips TRUE and FALSE in a range, which is why you convert them: `=SUMPRODUCT(--B2:B8)` counts the TRUEs.
- The text "TRUE" from a CSV import isn't a logical value, and `--"TRUE"` returns #VALUE!. Compare it instead: `=--(B2="TRUE")`.

## Questions

**How do I count TRUE values directly?**

`=COUNTIF(B2:B8,TRUE)`.

**How do I turn 1 and 0 back into TRUE and FALSE?**

Compare with 1: `=C2=1`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-true-false-to-1-and-0