Forum Discussion
Conditional Formatting to Highlight Cells Based on Yes/No Values
In the work I am doing, I'm finding that having spreadsheets to manage specific data and responses to forms, both for analysis of frequent responses and tally of yes/no responses on forms, is exceptionally helpful. What I struggle with, being a not-to-savvy user of Excel, is how to conditionally format a row of cells based on the yes/no value of a single cell on that row. The file below is the spread I am attempting to develop and having lines highlighted depending on the response of yes or no will make further use of and analysis of the data more efficient for us. I've attempted to use (and edit for response values) the formulas and make them from scratch but I seem to be missing something in the process. To specify what I want for a reaction to "Yes" is a row that is highlighted light green and for "No", a row that is highlighted light red.
All you need is, select the range in column A:G say A2:G100 and make two New Rules for conditional formatting using the formulas given below.
For Yes (Green):
=$F2="Yes"and set the format as per your choice.
For No (Red):
=$F2="No"and set the format as per your choice.
In the attached I selected the range A2:G100 and make two New Rules for conditional formatting using the above formulas.
Please refer to the attached for more details.
Subodh_Tiwari_sktneer Thank you for answering with that very simple solution! I tried to do it without selecting the whole range the document will be using and it didn't work so I selected the range of cells to be included in the document and plugged in the formulae and it worked perfectly. Thank you again!
You're welcome! Glad it worked as desired.
Please take a minute to accept the post with the solution provided as a Best Response to mark your question as Solved.
Gabz_122 just a little to explain how custom formulas in conditional formatting work and what Subodh_Tiwari_sktneer did. A conditional formatting will apply the requested format to any cell that evaluates as "True" (which is anything except False or 0). When you create the formula you need to create the cell reference(s) in the formula based on the 1st upper left cell in the range(s) that you select. So in Subodh's example that is A2. Since you want to use column F you use $F2 because the $F will "Fix" that reference to always be column F and the 2 (without a leading $) will adjust the row for every cell the condition formatting looks at (in this case to be the same row as the cell being evaluated since the upper left cell A2 and $F2 are both in row 2). So as the conditional formatting goes cell to cell it will always look to compare the value in column F of the same row to determine if it evaluates True or False.
I hope that helps you understand conditional formatting rules.
