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 5. Total comments: 137

  1. How about "side by side" for MacIntosh?

  2. I have a app in excel that contain 20 different sheets... some of one are menus to navigate thought the workbook.... The principal WB its a shared WB an there are 8 copies of the WB with different names in the same folder. The folder contain only the original an copies of the app wb. I need a code in vba to compare and merge the different workbooks that contain the folder with the open copy in automatic way, without the specific user have to select the other copies to compare an merge. In this moment I don't have a previous code to show you.

    Can you Help me?

  3. Hello,

    I'm trying to validate migrated data between two sheets both containing the same number of headings. The rows containing the data however could be missing lines between the source sheet and the target sheet so how to I set a formula to look over a wide range of cells? The examples above have been between the same two cells on each sheet but I need to check the cell on one sheet against a number of cells on another sheet.

    Thanks

  4. Hi, I have a parts list that is produced on a set format spreadsheet and a second spreadsheet that is used for showing other data but contains the same part numbers not in the same order. Is there a way to search the two DIFFERENT formats of spreadsheets showing part numbers that have changed, (In one but not the other).

  5. Very helpful. This made it very easy to compare 2 spreadsheets that were allegedly the same but had different time stamps. This resolved all differences quickly.

  6. hi i have very little know how using excel.can someone assist me with A TEMPLATE that can compares two or more sheets and copy the like data to onother sheet.regards ben.

    email:gold4nt@gmail.com

  7. Hi

    I tried comparing files with formulas but i get a message saying formula is incorrect

    can you say me what do you mean by copy it down and to the right

    1. Hi Preethi,

      Please make sure you enter the names of the sheets you are comparing. To copy the formula, point to the lower-right corner of the cell with the formula until you see a black plus sign, click on it and drag it to the right and down to fill all cells that you want to use for comparison. You will see that the formula changes in each cell to reflect the corresponding reference, e.g. in A1 you will see
      =IF(Sheet1!A1<> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
      in A2 it will change to
      =IF(Sheet1!A2<> Sheet2!A2, "Sheet1:"&Sheet1!A2&" vs Sheet2:"&Sheet2!A2, "")

      Thus if values differ in the compared cells, the formula will display the change.

      1. Hi,

        actually the formula is wrong. In if statements you need to use ";" instead of ",". So the correct one is:

        =IF(Sheet1!A1 Sheet2!A1; "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1; "")

  8. 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?

  9. This is exactly the information I was looking for! Options were presented, they were each explained clearly, and it worked perfectly.
    Thank you so much!!

  10. 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

  11. THanks for the sharing the tips...really no words to type here to appreciate your provided solution. It was really useful for me where I was not having the textpad to compare the files.

    1. Copy your excel file in word and then use word compare

  12. 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

  13. 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.

  14. This is a great content. Increased the productivity.
    Thanks.

  15. Very Useful!

    Excellent, quality content!

  16. Hi!
    I have tried the "Highlight differences between 2 sheets with conditional formatting", but when I click on the OK button of the "New Formatting Rule" screen, I get an error message saying "You cannot references to other worksheets or workbooks for Conditional Formatting criteria.". I am using Excel 2007. Can you help me out?
    Thanks,
    Best regards,
    Manuel.

    1. Hi Manuel,

      First please make sure both sheets are in the same file. If they are, please try to name the range in your second sheet and use this name in your Conditional Formatting formula. You can find more details in this forum thread:
      https://goo.gl/uDQXUn

      I hope this helps.

  17. Thanks for a very insightful post Svetlana. It might be also worth to add that one can compare Excel spreadsheets using sql queries as well.

  18. how to compare two excel sheets and put result in one of the excel sheet

    1. Hello Avi,

      Please see the second part of this post for the formula you can use. If your task is different, please describe it in more detail.

      1. how to add Synkronizer in MS Excel

  19. It's very useful to me

  20. 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.

  21. 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.

  22. 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

  23. Excellent tips for excel beginners like me. Thanks for sharing your knowledge...

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 :)