Conditonal Formatting Works for "No" but not "Yes"

New Contributor

Hey everyone,


I have a spreadsheet tracking responses and in my final column (L) I can toggle between Yes and No. When No is selected there is no color change to the row but when Yes is selected the entire row is supposed to turn green. 

The formula used is =$L3="Yes"


The problem is when you toggle to Yes there is no color change. I changed the formatting to =$L3="no" to give that a try and it worked.


Has anyone run into this issue before where the formatting works for one and not the other? 


Its driving me nuts and I have spent so much time trying to figure it out.



8 Replies

@AndrewC1991 Difficult to help without seeing what you exactly have down. Perhaps the attached file can help you resolve the problem.


You mention that =$L3="no" works. Should the other formula perhaps be =$L3="yes" with lower case y?

Or could there be a space before or after Yes in column L?



I have attached an example here. I had to remove some columns for privacy. You will notice in column J, when you toggle to NO the entire row goes red but when you toggle to yes nothing changes. I have the formatting set for both No and Yes in this example. 



@AndrewC1991 I see no attachment.

Hey Riny,

I DM'ed you the attachment. I couldn't find a button to upload to the thread.
Hans, I will DM you with the file. Doesn't seem to accept the attachment on this thread.


Activate the Data Validation sheet.

There is a space after the word Yes in cell L3. This causes the conditional formatting rule to fail.

If you remove the space from L3, it'll work as intended.

@Hans Vogelaar 

Andrew reported in a private message that this solved his problem.