Turn TRUE FALSE into numbers in Excel

When using Microsoft Excel you often end up with a logical answer i.e. a TRUE or a FALSE. In Excel TRUE and FALSE are actually seen as the numbers 1 (True) and 0 (False). So how can you turn TRUE FALSE into numbers in Excel?

Firstly, just typing in TRUE and FALSE does not mean Excel sees it as a logical test result. If you enter into a cell something like =1=1, the answer will show TRUE. This is generally in upper case because Excel recognises it as a logical result and interprets it as a number. If you just type in the words TRUE or FALSE, Excel may see it as a word, and therefore not treat it the way you expect.

Secondly, it is useful to note that you can perform a calculation straight against the TRUE/ FALSE and it will calculate as if it is a 1 or 0 e.g.

  • a 100 multiplied by a cell with TRUE in it will return 100
  • a 100 multiplied by a cell with FALSE in it will return 0

But perhaps you don’t want to use this feature. You want the TRUE and FALSE to be shown in Excel as 1 and 0 and you don’t want to use an IF function to achieve it.

The solution is surprisingly simple. Just put a double negative i.e. — in front of the calculation which needs to be in brackets

So for the example above we would enter this

=–(1=1)

The end result would be a 1 instead of TRUE.

Want to learn more about Microsoft Excel? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.