Excel: identifying if all values in a row are not equal

microsoft excelworksheet-function

I am comparing setting files between devices and have loaded all the settings into an excel sheet.
I want to see if any setting is deviating between the devices.

Example:

Setting1: ok ok ok ok ok

Setting2: 100 100 104 104 103

Setting3: no yes no yes yes

Setting4: 30 30 30 30 30

Now i want line 2 and 3 to be highlighted because the values are not equal on those rows.
How to do that? I tried a conditional formatting with "A1=B1=C1=D1" should be green, but that did not work

Best Answer

Alright, assuming your data is set up so column A is a label row, we have data from B1:F4.

First, add a helper column in between columns A and B. In B1 enter the following formula:

 =IF(AND(C1=D1,C1=E1,C1=F1,C1=G1),TRUE,FALSE)

Then copy this formula down to row 4. Your helper column should now have a value of either TRUE or FALSE for each row.

Now highlight from B1:G4 and select Conditional Formatting. Add a new rule, and select the option "Use a formula to determine which cells to format".

On Office '11 I have to select "Style: Classic" first, I'm not sure if this is necessary in Windows versions.

In the formula bar for your rule, enter "=$B1=FALSE" and select Ok.

This should do it!

Related Question