Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'



IF not working with TRUE/ FALSE in Excel

If you already make use of the IF function you may have come across the situation where IF does not seem to work, particularly when you are comparing to True and False answers.

YouTube IF not working with TRUE/ FALSE in Excel

Why is the TRUE ignored in Excel

So below in row 14 (cell B14) it clearly says true and in the Formula we have typed B14=”TRUE”, yet Excel wants to give us a zero answer as if it is false.

If not working with true false in Excel

The reason for this is that the ‘words’ TRUE and FALSE are actually the numbers 1 and 0 in Excel (in most cases). So when you are creating the logical test in the IF function, you must treat it like a number i.e. no inverted commas.

The syntax should be

=IF (B14=TRUE, $C$7,0 ) – notice no inverted commas

You can even test it by using a number rather i.e.

=IF (B14=1, $C$7,0 )

The ‘number’ words happen if you get excel to spit out a TRUE/ FALSE answer. For Excel it is a 1 or 0 .

It also happens when you type it into a cell. The clue would be if I type in True (notice the lowercase) and when I click enter it changes on its own to TRUE (all uppercase). It also typically goes from left aligned to center aligned.

Where you need to be careful is if someone has typed true in some inverted commas. Then Excel sees it as a word in which case your IF needs to have inverted commas.

If not working with true false in Excel

So all in all quite a difficult thing to spot. A way to possibly check is to create a calculation that multiplies the ‘word’ with a one. As shown below, the TRUE FALSE that it sees as numbers turn into numbers (1 x 1= 1 and 0 x 1= 0) and the ones that are actually words give us a #VALUE error message (Excel asking you why you are trying to multiply with words).

If not working with true false in Excel

Once you know what you are dealing with, you will get the right version in you IF function i.e.

NO INVERTED COMMAS- when Excel recognises it as a number (0= False, 1=True)


INVERTED COMMAS- when Excel sees it as we do, which is as a word.

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.

IF function not working with numbers

Finding the IF errors in Financial Models

Alternative to multiple IF statements