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