Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Contact              
 Excel for Business
 Excel Dashboards   
 Excel Solutions   
 Exploring Excel
 BI for Excel    
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  
 
   
     
   
     
 

Home > Exploring Excel

How to Use Conditional-Format
Formulas to Change Background
Colors


"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 .

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, to use standard commands:

...In Excel 2003, choose Format, Cells.

...In Excel 2007 and above, 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.)

To set up the conditional formats for this table, you need to specify formulas rather than cell values.

Select all cells in your table. Make a mental note of where your active cell is within this selection. Then...
 

In Excel 2003...

Choose Format, Conditional Formatting, and then set up your dialog to look something like this:

There are two key points here. First, be sure to chose "Formula Is" in each dropdown list box at the left of this dialog.

Second, when you enter the formula, be sure to enter it using a mixed reference to a cell in the row of the active cell. For example, the formula shown for the first condition is:

=$C4=""

When I set this up, my active cell was in row 4, so my formula referred to row 4. But notice that there's no $ sign in front of "4". That's because I want the formula to change to row 5, 6, and so on when the conditional formatting is used in those rows. On the other hand, there is a $ in front of "C" because I want the formula always to refer to the value in that column.

To add each new condition, choose Add >>. After three conditions are defined, "Add >>" is grayed out because Excel 2003 allows a total of three conditional formats.

After you enter all three formats, choose OK.
 

In Excel 2007...

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. 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 cell 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 checkboxes 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 would work in either version of Excel.

All the best,

Charley


 
 
 
 


ExcelUser, Inc.
http://www.ExcelUser.com

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.

Excel Dashboards

Create professional quality dashboard reports with Excel.