suppose I have a formula in cell C3:=AND(A1=B1) it will give TRUE:FALSE but i need Yes:No instead so how do I format the cell C3 to get so? Thanks! Regards,

  • Try writing your formula using If instead of And.
    – OSol1tair3
    Mar 24, 2015 at 15:19
  • Hi can it be done by conditional formatting? Mar 24, 2015 at 17:22
  • No. Conditional formatting is used to change the format of the cell; fill color, font weight, font color, etc. It is not used to change the value of the cell.
    – CharlieRB
    Mar 24, 2015 at 17:37

5 Answers 5


Actually, yes, it can be done through conditional formatting... Sort of. Use the screenshots below to setup a conditional format that uses a custom number format for cells that are TRUE. You should be able to extrapolate the setup for False = No.

It's important to note that it will only change the apparent value in the cell and not the actual value. If you try to do other calculations based on this cell, it will use the TRUE/FALSE values and not the YES/NO values. Therefore, this method is only helpful for when you're concerned about what's presented to the user and not what's used in underlying calculations.

Screenshot 1

Screenshot 2

Screenshot 3

See how the value would be still be TRUE but it's displaying "Yes" instead.

  • 2
    You don't actually need it to be "Yes";"Yes";"Yes";"Yes". These are four different formats for positive numbers, negative numbers, zero, and then everything else; so if your value will always be TRUE or FALSE, you could use ;;;"Yes" leaving the first three blank. Mar 24, 2015 at 22:03
  • You would also need to add another conditional format using ;;;"No" when the value evaluates to FALSE, or if you are certain the only values will ever be TRUE or FALSE, just set that as the base formatting for the cells. Mar 24, 2015 at 22:03
  • @JasonClement To your first, I know that. To your second, I mention that in the last sentence of the first paragraph, although I don't explicitly state with the format should be. Mar 24, 2015 at 22:15
  • I was only adding that for the asker's benefit. I intended the second comment to highlight that it didn't require two conditional formats. Mar 24, 2015 at 22:29
  • @JasonClement Your points are valid and accepted. Mar 24, 2015 at 22:41

Use the IF function:

=IF(A1=B1, "Yes", "No")

  • Hi can it be done by conditional formatting? Mar 24, 2015 at 17:22

Let me give my two cents.

Supposing you're just interested in the showing values, you may use the following trick.

In C3 insert the formula:


Maybe, get rid off the AND part:


Now, you can set the custom format for C3 to "Yes";;"No".

The +0 part forces Excel to treat the result as numeric, giving you the option to use the custom format.



Data Result Result

1   FALSE   NO

12  TRUE    YES

12  FALSE   NO


Formula in C3:


Formula in B3:


  • Hi can it be done by conditional formatting? Mar 24, 2015 at 17:21
  • It isn't clear why the OP inserted the AND function, it serves no purpose here. For your example of how to produce the result, consider leaving it out to demonstrate the proper way to do it. The AND is harmless, but it makes your answer look "wrong", since answers typically contain appropriate syntax to show how the problem can/should be solved. Just a suggestion.
    – fixer1234
    Mar 24, 2015 at 17:26

Use =--(A1=B1) instead of =A1=B1. The double negative flips the sign twice, and since it's a mathematical operation, it converts TRUE to 1 and FALSE to 0 (i.e. casts them as integers)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .