Conditional Format to compare two worksheets

I received an email today, asking how to compare two worksheets for differences.  Of course, we could easily reach to VBA to do this, but we can also use Conditional Formatting to make these kind of comparisons.  I figured that I'd knock up a quick example, so here goes:

First I set up the workbook so that I'd have two worksheets with similar, but not completely duplicated data:

  • On Sheet1, in cells A5:G23, I entered a block of random numbers
  • I copied those numbers to Sheet2, cells A5:G23
  • I changed some of the numbers on Sheet2, to give me some cells that wouldn't be identical

Now that I was set up to prove it out, I selected Sheet2's A5:G23 range and created a new conditional formatting rule using the following formula:

=A5<>Sheet1!A5

Wouldn't you know it, though, it came back with an irritating error:

cferror.png

Okay, so that's irritating, but not insurmountable by any means.

Next, I set up a named range.

  • On Sheet2 select cell A5
  • Create a named range with the following formula:
    • =Sheet1!A5
  • For reference, I created it using the name "rngSht1"

So then, after selecting Sheet2's range of A5:G23, we again head back to the conditional formatting wizard.  This time the rule we use is as follows:

  • Criteria  =A5<>rngSht1
  • Fill colour  Light red

And now, after applying the format, we end up with a nice display of the cells on Sheet2 that don't match those on Sheet1:

cfcomparison.png

5 thoughts on “Conditional Format to compare two worksheets

  1. I can use this approach for reconciling billing that comes to me in excel format? Each spreadsheet will have for columns:

    employee ID, last name, first name and cost.

  2. I'd have to see the data setup, but I can't think why you couldn't. If you can write a vlookup test against a listing of those columns, you should be able to build a conditional format from it.

  3. Pingback: Merging Files using Employe ID Number | keyongtech

  4. Ken,

    took me about 4 hours to find your thread but well worth it
    simple elegant and does the job.

    lowell

  5. That's ingenious. I've been looking for a worksheet comparison capability for ages. If only I'd know you wrote about this 3 and a half years ago!

    Works like a charm!

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *