Comments on: How to compare two Excel files for differences

Learn a variety of methods to compare Excel files and identify differences between them. See how to open two Excel windows side by side, how to use Excel formulas to create a difference report, highlight differences with conditional formatting, and more. Continue reading →

Comments page 2. Total comments: 50

  1. i have the data like name of items, price, date in one sheet. And another sheet i have data like name of items, sale on date. I need to know what is the exact price on sale date with related name of items?
    which function is suitable to find the price?

  2. hi..

    i have 2 excel files.
    i want to see what are the common values /matching values in both the files

    kindly help me.
    thanks in advance

  3. hi,how to match the info in different sheet in different column?for example i want to test whether if the info in sheet 1 also appear in sheet 2 but in different cloumn and row.

    1. Hi Tan ke jun,

      You can try the Compare Two Tables tool in our Duplicate Remover add-in. It can compare values in the key columns you specify and highlight either matches or differences:
      https://www.ablebits.com/excel-suite/find-remove-duplicates.php

      - Select the table where you want to see the differences first
      - then pick the second sheet for comparison
      - choose if you want to find duplicates or uniques
      - Select the columns you want to compare
      - Choose what to do with the results

  4. Hi,

    I want to compare two excel worksheets to isolate all email addresses that are on one file, but are not on the other. 24,600 on sheet 1, 19,100 on sheet 2: which are missing on sheet 2?

    thanks for your help,

    Irv

    1. Hi Irv,

      You can use Duplicate Remover for this task:
      https://www.ablebits.com/excel-suite/find-remove-duplicates.php

      Here are the steps to follow:
      - Open the sheet with 24,600 emails and run "Compare Two Tables" tool under Ablebits Data tab. It should pick the range with all your records, click Next;
      - Select the sheet with 19,100 emails as the second table and click Next;
      - Choose to find "Unique values" and click Next;
      - Select the key columns with the email addresses for comparison, click Next;
      - Choose what you want to do with the found records: highlight the found records with color, select them, or mark them in a status column and click Finish.
      I hope this helps.

  5. Hi, Svetlana!

    Could you please advise on what to do when Compare and Merge and Workbooks button appears inactive on the ribbon?

    I've added the Compare and Merge Workbooks command to a custom group on the Review ribbon. However, the added command is grayed-out.

    Any clues?

    Thank you.

    1. Hi!

      In most cases, the Compare and Merge and Workbooks button is grayed out when you are trying to combine different Excel files. Please remember, this feature allows merging only copies of the same shared workbook.

      So, if you want to use Compare and Merge and Workbooks option, share your workbook before making any changes to it (click the Share Workbook button on the Review tab, and select the 'Allow Changes by More Than One User' box). After that different users can edit your shared workbook, and save a copy using a unique file name.

  6. Thank you for making great content! I've tried "How to compare two Excel sheets for differences in values" and "Highlight differences between 2 sheets with conditional formatting." My issue is the source file row counts grow over time which means as new rows are inserted alphabetically, Conditional Formatting thinks there's a change to report. Is there a way to compare row by row and only report on those changes? My brain envisions a complicated 'match if' to isolate changes versus the same row data that just happened to get moved down a couple rows.

    1. Hello Brian,

      Thank you for your kind feedback. I believe a specialize tool like Syncronizer would be the best solution for this kind of task.

      If you are comparing the rows in 2 sheets based on some key column, which contains only unique values, then you can try creating a conditional formatting rule using one of the following formulas:
      https://www.ablebits.com/office-addins-blog/excel-compare-two-columns-matches-differences/#highlight-matches-differences

      The above article shows how to compare the values in 2 columns, but this approach also works for comparing 2 different worksheets.

  7. how to avoid circular reference

    1. If you rename
      Sheet1= abc
      Sheet2=xyz

      and leave Sheet3 as it is without renaming (Sheet3), Then the above mentioned formula will reference to sheet3 itself.

      Solution: try the formula without renaming!
      or
      Enter your Sheet names accordingly in your formula

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)