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 attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.