Skip to Main Content

go to Indiana University Indiana University


Conditional Formatting in Excel 2007 – Formatting an Entire Row Based Upon Multiple Conditions

Conditional formatting can be a great tool to enhance the data in your spreadsheet.  There are several options for formatting a cell given the value of that particular cell.  But what if you want to format multiple cells in a row based upon the value of one of more other cells?  Well, that’s not so straightforward!

Let’s suppose that we have an Excel spreadsheet containing student data, but we want to be able to look down the list and quickly view all freshmen who are female chemistry majors:

Formatted spreadsheet

Here’s where understanding how to use logical functions, formal syntax, and cell references will help you format your data.

To begin, on the Home tab, in the Styles group, click the Conditional Formatting command button and select New Rule.  The New Formatting Rule dialog box opens.  Select “Use a formula to determine which cells to format.”

The key to setting up this formula is the use of the logical function “AND.”  Our values do not change columns (i.e., Gender is always in column C, Class in D, and Major in E), but they do change from row to row.  We will also need to use a mixed reference: the column is absolute (designated by the “$” before the column letter), but the row is relative.  Without the use of the mixed reference, the formatting would be based entirely on the specific cells in the formula.

We are searching for non-numerical values.  Therefore it will be necessary to use quotation marks.

In the “Format values where this formula is true:” field, type:

=AND($C1=”F”, $D1=”Freshman”, $E1=”Chemistry”)

To set the type of formatting, click the “Format…” button. Choose a Fill color and click OK.

To create the rule, click OK.

The rule has been created, but it has not been applied.  In the Styles group, click Conditional Formatting, click “Manage Rules…”  The Conditional Formatting Rules Manager dialog box opens. The rule is there, but is currently only applied to cell A1.  We need to change this.

Click in the “Applies to” field. Press and drag cells A1:E10.  The “Applies to” field should now read, “=$A$1:$E$10.”  Click Apply.

The spreadsheet should now be formatted correctly.


9 Responses to "Conditional Formatting in Excel 2007 – Formatting an Entire Row Based Upon Multiple Conditions" to “Conditional Formatting in Excel 2007 – Formatting an Entire Row Based Upon Multiple Conditions”

  1. kensely Says:

    hmmm…i tried this same logic with a little twist, but failed. For some reason I can get my row to highlight correctly if a third field was using a greater than or less than approach…

    =AND($A2=”blah 51ll”, $B2=”blah 512″, $C2

  2. kensely Says:

    I apologize for my last comment – it wont display my full condition due to the quotes – but i changed my mine. Is it possible to do something like this…..

    =OR( (AND(A=1,B=10,C

  3. kensely Says:

    =OR( (AND(A=1,B=10,C less than 10)) (AND(A=3,B=4,C less than 4)) (AND(A=6,B=3,C less then 100)) )

  4. April Law Says:

    I am reading this correctly that you got it to work? I’m not sure if you still need any advice, but this formula worked for me:
    =AND($A1=”ASD”,$B1=”JKL”,$C1=”>10″)
    Instead of using the words “less than…,” I used the symbols.
    I hope that helps. Or, I hope you already had it solved.

  5. Nelson134 Says:

    Hii
    really nice information has been provided I use it for many of my projects it saves time an i have also an formula which i use to duplicate the values it is :Conditional Formatting-Highlight cell rules-Duplicte values
    How to apply Conditional Formatting in Excel 2007
    1 Select the cell(s) to which you want to apply conditional formatting.
    2 Click the Conditional Formatting button in the Styles group on the Home tab.
    3 Point to Highlight Cells Rules and then select the criteria you want to use.
    4 Enter the values you want to reference in the text box.
    5 Click the drop-down arrow next to the format options and select the desired formatting.
    6 Click OK.

  6. John Says:

    I also tried this sentence but isn’t working, any ideas why ?

    =AND($B1=”V”,$D1=”DECOR”)

  7. Kamal Gurung Says:

    Perfect! help me out,,,, thanks a lot

  8. petevig Says:

    worked perfectly, even for a single cell criteria.
    just be sure to start the formula on the first row where the data starts.
    if you set up the rules first, it is easy to go to “manage” and copy paste the range they all apply to.

  9. Perry Kew Says:

    Great tip. Highlighting a row based on multiple criteria is something that I do frequently and it has been a bit of a struggle in the past. Now it should not be a problem and a lot of time will have been saved.

    Thank you.

Leave a Reply

 

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.