Compare two excel columns and highlight the row differences

Audra Breedlove
Audra Breedlove used Ask the Experts™
on
Hello,

I need a little help.  I have two columns and I'm trying to compare any changes between the cells on each row.

Can someone help me with a formula that will compare the cells and highlight both cells, if they contain different data.

Please see example attached.
Compare-columns.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Do they need to be highlighted? In the past I've done a quick and dirty formula that rights a number next to the row in a third column if they are different.  Would that be acceptable?

Use the following formula

=IF(ISERROR(MATCH(A1,B1,0)),"DIFFERENT","")

Open in new window


And drag it down the third column. This will right the word DIFFERENT next to columns that change.

Highlighting can be done but it would require a script or conditional formatting rather than a formula.
Analyst Assistant
Commented:
You can use conditional formatting for this.

Select the 2 columns of data.

Goto conditional formatting.

Select the 'use formula' option.

Enter this formula.

=$A2<>$B2

Finally format as required.
Hello!

Do you only want to highlight the differences between cell A1 and B1 and then between A2 and B2, or do you want to highlight the changes if there are any differences on any row in the column?

if just between the individual rows, use conditional formatting, as shown in the attached. cells in column F will highlight when they differ from there counter-part in E.

Josh

EDIT: Clicking attach helps ;-)
Compare-columns.xlsx
Audra BreedloveSr HRIS Analyst

Author

Commented:
This is exactly what I was looking for.  Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial