“I have an Excel table with a column that will be filled with ‘yes’ or ‘no. When a user enters ‘yes’ to a cell, I need the row that includes the cell to be all the same colour. And if ‘no’ the row should be a different colour. I can do this for the cell with conditional formatting. But I want to copy the cell’s colour to the rest of the row. How can I do it?” — Volkan Ö.
The image below provides a simple illustration of what I think you want to achieve.
Here, the entire row of the table is green when Yes is entered and pink when No is entered. Otherwise, the row is white.
As I’ll explain, you can use conditional formats to do what you want. But first, let me make a suggestion…
It’s never a good idea to ask users to enter “yes” or “no” in a cell. Not only does this take them more time than is necessary, it significantly increases the chance of errors.
Errors are important here because your conditional formatting only will work if you can rely on accurate entries in the Yes-No column.
So, instead of entering text, I would ask users to enter 0 (zero) for “no” and 1 (one) for “yes”. If you want your spreadsheet to display the answer differently, you can do so with number formatting.
To assign a number format that would display “Yes” for 1 and “No” for 0, select the column where you want Yes or No to be displayed. Press Ctrl+1 to display the Format Cells dialog. Or choose Home, Cells, Format, Format Cells.
In the Number tab, choose the Custom category. Then enter this custom number format in the Type edit box:
“Yes”;;”No”;
Excel number formats use this syntax:
[positive numbers];[negative numbers];[zero];[text]
So the yes-no format above displays “Yes” for a positive number, nothing for negative numbers, “No” for zero, and nothing for text. (By displaying nothing for incorrect entries, we provide automatic error-checking.)
This figure shows the results we want. Here I’ve used light orange for No and light green for Yes.
To set conditional formatting, choose the range B4:C7. And note which row your active cell is in.
Choose Home, Styles, Conditional Formatting, Manage Rules. Then choose New Rule.
In the New Formatting Rule dialog, choose “Use a formula to determine which cells to format.” In the edit box, enter this formula:
=$C4=””
We don’t need to specify any formats for this condition. It’s purpose is to keep Excel from treating blank cells and cells with zeros in them as the same thing. So just choose OK.
(Again, when I created these formulas my active cell was in row 4. If your active cell is in a different row, you’ll need to modify your formulas as needed.)
Repeat the process of setting up a new rule. Enter this formula:
=$C4=0
This time, specify the formats you want for the No response. Then choose OK.
Repeat the process for Yes, entering this formula:
=$C4>0
Specify the format for Yes.
After you choose OK, compare your Rules Manager dialog to this example:
You should see two critical differences between your dialog and this one.
First, the first formula you entered is at the bottom. This is a problem because Excel needs to process this rule first. The order of the other two rules doesn’t matter.
To correct this problem, we need to move the first rule we created to the top of the list. To do so, select the rule in this dialog and click twice on the blue up-arrow.
Second, your dialog probably doesn’t show the Stop If True check boxes checked. You need to add checks to them all. That way, when Excel finds one condition that works it doesn’t continue to process the other rules, messing up our results.
Choose OK to complete your conditional formatting with formulas.
Finally, if you prefer to enter “no” and “yes” into cells, rather than 0 or 1, you can set up the conditional formatting with two formulas:
=$C4=”yes”
=$C4=”no”
This approach work in all versions of Excel, beginning with Excel 2010.
