4

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 '15 at 15:19
  • Hi can it be done by conditional formatting? – Hemant Rupani Mar 24 '15 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 '15 at 17:37
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.

| improve this answer | |
  • 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. – Jason Clement Mar 24 '15 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. – Jason Clement Mar 24 '15 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. – Engineer Toast Mar 24 '15 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. – Jason Clement Mar 24 '15 at 22:29
  • @JasonClement Your points are valid and accepted. – Engineer Toast Mar 24 '15 at 22:41
3

Use the IF function:

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

| improve this answer | |
  • Hi can it be done by conditional formatting? – Hemant Rupani Mar 24 '15 at 17:22
2

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:

=AND(A1=B1)+0

Maybe, get rid off the AND part:

=(A1=B1)+0

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.

| improve this answer | |
0

Example:

Data Result Result

1   FALSE   NO

12  TRUE    YES

12  FALSE   NO

33      

Formula in C3:

=IF(AND(A4=A5),"YES","NO")

Formula in B3:

=AND(A3=A4)

| improve this answer | |
  • Hi can it be done by conditional formatting? – Hemant Rupani Mar 24 '15 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 '15 at 17:26
0

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.