excel training in uk - IF Statements

The answer to this is to use the Conditional Formatting feature in the Format menu of Excel.

Once you have written your IF statement e.g.

=IF(C2>200,"Pass","Failed")

and have used the Autofill handle to copy the result of your formula down the worksheet, e.g. C2:C20

Keep these cells e.g. C2:C20 selected and select Format menu, Conditional Formatting...

Choose, Formula Is from the drop down, just under Condition 1
Now re-write your IF statement e.g.

=IF(C2>200,

When writing the True and False statement into the Conditional Formatting box you MUST not type in the original True and False statements as this will not work, instead substitute Pass with True and Fail with False.

So your statement will now read as:

=IF(C2>200,True,False)

Click the format button in the dialog box, click the font tab to choose a font colour e.g. red

Click OK

Your True statement e.g. Pass will now appear in Red text.

To display your False statement e.g. Fail in another font colour e.g. blue

Keep your range highlighted e.g. C2:C20

Select Format menu, Conditional Formatting...

Click Add to add a second condition

Choose, Formula Is from the drop down, just under Condition 2
Now re-write your IF statement e.g. - this time noting that False comes before True.

=IF(C2>200,False,True)

Click the format button in the dialog box, click the font tab to choose a font colour e.g. blue

Click OK

Your False statement e.g. Fail will now appear in Blue text.


Hope this helps
If you want to find out more about IF Statements, please check the syllabus for our Excel Advanced course.

Sandy