Excel: Compare multiple columns to see if less than value in another column for all rows

conditional formattingmicrosoft-excel-2010

I am trying to compare data across multiple columns to see if each cell is less than a value in a single cell on the same row.

What my spreadsheet looks like

Column B contains the mean across all employees for the variable in Column A (there are 12 variables). Columns C through N contain the mean for each individual employee. For each of the variables, I would like to highlight the cell for employees whose mean is less than the mean of all employees.

I can figure out how to do this for each row (compare the values in columns C through N with the value in column B) using conditional formatting using a formula:

If Cell Value is <$B$2, apply highlighting to =$C$2:$N$2.

I don't want to have to do this row by row if at all possible.

Best Answer

Select the data from C2 to N13. Create a new conditional formatting rule with a formula. Use this formula

=C2<$B2

and apply a format. Note the placement of the dollar signs. As the conditional format is applied across and down, the reference C2 will be adjusted to whatever the current cell is. The comparison will always look at column B, since the $ sign makes the column an absolute reference, whereas the row number will adjust to the current row.

If you want to use the Highlight Cell rules dialog, just change the $B$2 to $B2 and apply the format to $C$2:$N$13

Related Question