Conditional formatting not recognising text

bec74

New Member
Joined
Dec 19, 2016
Messages
2
Hi

I have a spreadsheet with about 1000 rows which contains the results of a trading marketplace (bidder details, buy bids, sell offers, prices, units bought/sold etc).

I am trying to format 1 column (bid type) with lots of different colours depending on particular characteristics of the bidder and the outcomes of their bid, ie. if they were a priority member but were successful (green), priority member but unsuccessful (red), non-priority member... etc etc.

So I have set up a whole set of conditional formatting rules, all in a similar format eg: =AND($B2="TRUE",$C2="FALSE",$E2>0)

The problem is, any rules that involve recognising text ("TRUE" or "FALSE") are just not working. I've tried taking out the text references (eg. just highlight all successful bids green) and they work fine. I've also tried testing other formulas based on the same text eg. =IF($B2="TRUE",1,0) - they don't work either.

Any ideas on why this would be happening? I've tried switching the cell formatting from "General" to "Text", which didn't make any difference. There are no spaces before or after the text either.

Many thanks
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
In Excel there are special meanings/values associated with TRUE/FALSE.

If the cell entry is in uppercase and centered in the cell TRUE/FALSE is the default Boolean value.

"TRUE" or "FALSE" are TEXT values denoted by the quotes. "TRUE" and "FALSE" are not the same as TRUE and FALSE.

Data Range

A

1

TRUE​

2

FALSE​

3

TRUE​

4

FALSE​

<tbody>
</tbody>


A1 and A2 are the Boolean values. Notice how they are centered in the cells.

A3 and A4 are the TEXT values "TRUE" and "FALSE". Notice how they are aligned to the left.

So, maybe all you need to do is to remove the quotes from around the T/F in your formulas.
 
Last edited:

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
That graphic didn't display as I had hoped and I can't figure out how to edit the post.

So, here's a better visual...

Data Range
A
1
TRUE​
2
FALSE​
3
TRUE​
4
FALSE​
5
----------------​

The Boolean values are centered (by default) in the cells.

The TEXT values are aligned to the left (by default) in the cells.
 

bec74

New Member
Joined
Dec 19, 2016
Messages
2
Oh my goodness - that works! TRUE and FALSE weren't centered in the cell, but removing the quotes still fixed the problem. Thank you so much - all that time I spent googling for an solution and you fixed it within a few minutes!!! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,705
Messages
5,597,663
Members
414,162
Latest member
jborjal1967

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top