Compare multiple cells and highlight differences

[ez]

Hello,

I have two long lists (same template) I need to compare cell to cell (on the
same row) to find any differences. It there an easy way to compare the two
lists and have the system to highlight the cells that are different? Again
note that the comparison is between cells similarly positioned in the two
lists.
Thanks,
Elena

 

[JLatham]

Assuming your lists are in columns A and B, you can put this formula in
column C (assuming first entry is on row 2), and copy/fill it down the sheet:

=IF(A2<>B2,"NOT EQUAL","")

Or, you can do it with conditional formatting:

Select all of the cells in the 2nd column. Choose Format --> Cells -->
Conditional formatting
Choose Cell Value Is and "not equal to" and then move to the last entry area
and click on the topmost cell in the first column (as A2). Now, Excel is
going to enter a formula in that last entry area like =$A$2. Click right
next to that 2nd $ symbol and delete it so that the formula looks like =$A2.
Then use the [Format] button to set special formatting and click the [OK]
buttons to exit the tool. Cells in the 2nd column that are not equal to the
one on the same row in the first column will take on the special format you
chose.

 

[ez]

I used the conditional formatting and it is not working. When I click ok, all
cells on the second column are turning yellow (formatting I selected) even if
the cell value is the same. Any suggestion?

Assuming your lists are in columns A and B, you can put this formula in
column C (assuming first entry is on row 2), and copy/fill it down the sheet:

=IF(A2<>B2,"NOT EQUAL","")

Or, you can do it with conditional formatting:

Select all of the cells in the 2nd column. Choose Format --> Cells -->
Conditional formatting
Choose Cell Value Is and "not equal to" and then move to the last entry area
and click on the topmost cell in the first column (as A2). Now, Excel is
going to enter a formula in that last entry area like =$A$2. Click right
next to that 2nd $ symbol and delete it so that the formula looks like =$A2.
Then use the [Format] button to set special formatting and click the [OK]
buttons to exit the tool. Cells in the 2nd column that are not equal to the
one on the same row in the first column will take on the special format you
chose.

Hello,

I have two long lists (same template) I need to compare cell to cell (on the
same row) to find any differences. It there an easy way to compare the two
lists and have the system to highlight the cells that are different? Again
note that the comparison is between cells similarly positioned in the two
lists.
Thanks,
Elena

 

[Jacob Skaria]

Say if you are comparing Columns A and B

1. Select both Columns
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=$A1<>$B1
4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

If this post helps click Yes

 

[JLatham]

Well, either you got something wrong or inn some fashion they're not actually
equal (such as comparing numbers formatted as text to real numbers).

You might remove the conditional formatting I provided and try using the
formula that Jacob Skaria has provided instead and see if the results are any
different. It should pretty much be the same thing, just expressed a
different way.

I used the conditional formatting and it is not working. When I click ok, all
cells on the second column are turning yellow (formatting I selected) even if
the cell value is the same. Any suggestion?

Assuming your lists are in columns A and B, you can put this formula in
column C (assuming first entry is on row 2), and copy/fill it down the sheet:

=IF(A2<>B2,"NOT EQUAL","")

Or, you can do it with conditional formatting:

Select all of the cells in the 2nd column. Choose Format --> Cells -->
Conditional formatting
Choose Cell Value Is and "not equal to" and then move to the last entry area
and click on the topmost cell in the first column (as A2). Now, Excel is
going to enter a formula in that last entry area like =$A$2. Click right
next to that 2nd $ symbol and delete it so that the formula looks like =$A2.
Then use the [Format] button to set special formatting and click the [OK]
buttons to exit the tool. Cells in the 2nd column that are not equal to the
one on the same row in the first column will take on the special format you
chose.

Hello,

I have two long lists (same template) I need to compare cell to cell (on the
same row) to find any differences. It there an easy way to compare the two
lists and have the system to highlight the cells that are different? Again
note that the comparison is between cells similarly positioned in the two
lists.
Thanks,
Elena

 

[ez]

Thank you Jacob! It took a little bit of editing but it worked.