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

  1. Great post and well explained!!!
    Thanks a ton! :)

  2. Hi Svetlava,
    Thanks for your nice post. I am looking for a tool to compare two worksheets that would recongnize if a row (or column) was inserted (or deleted). I have used spreadsheet compare, however that works on a cell by cell comparison. If one row is inserted that row and all following rows are marked as changed. Do you have any advice?
    Thanks, Michiel

  3. Hi Svetlana - I have a task to to check for 'new' or 'updates' from the last week's data, and the data is huge, mostly the updates are change in dates, or some minor details. new updates/new info comes in a different workbook each, can you suggest me a quick solution to compare and identify the data between old massive listing and the new short listing of 'new and updated info'? can I amend your above formula to compare between two diff workbooks than sheets?
    Thanks so much in advance.

  4. HI, I'm trying to compare two sheets to match date and time data. So I have one sheet with phone response times and another with TV advert times and I want to match up the phone response with the TV adverts that went out within 8 minutes of that advert. Do you know what sum I should use? Thanks!

    1. Hi, Anthony,

      it's very difficult to come up with any particular formula since we don't know how your data is stored.
      If you could send us a small sample workbook with data and the result you expect to get to support@ablebits.com, we would be able to suggest you better.
      If you send the file, don't forget to link this comment in your email.

  5. For small worksheets, rather than side-by-side comparison, I open both files and display them full page. Then I switch back and forth between them using Ctrl-F6, and differences are easy to see as they literally "jump out." Make sure column sizes are the same.

  6. Thx :)

  7. I needed to compare two sheets and spent about 30 min writing code in Stata to do it. Then, even though I knew there were no differences, I decided there must be an easier way and found your tutorial. It took about 1 minute using conditional formatting. I never would have thought to look for that. Thank you!

  8. Hi,
    This is a useful information. Sometimes i have to compare two unsorted large files with many columns(say 100 columns) and it will be difficult for me to sort both the files before comparing it. (i use custom sort but it takes lot of my time and effort). So do you have better and efficient solution for this problem which can easy my task.

    Thanks
    Amit

  9. Thank you for the useful tips

  10. Hi
    How to compare two excel file with different cell addresses ? Will above mention solution work if the cell address of two excel sheet are different?
    Please help

  11. it's really useful .

  12. Hi ,

    I am sending files which has different cell values , i want Highlight the cell which has got new value .

    i mean to say , cell color should change if any one changes that cell value .

    Thank & regards
    shiva

  13. Hi,

    I would like to compare two excel files , which has many sheets within and merge it in the base file. These are not shared workbook but people have modified and saved it in different name. It would be of great help, if you could help out on this.

    Thanks

  14. I am trying to compare data from two workbooks. looking up Book1 column C and E to Book2 column L and B then matching those columns within the same row to what is in Book2 column N?

  15. really useful......thanks

  16. excellent support and guideline

  17. Microsoft has now a very comprehensive tool for comparing two Excel spreadsheets for differences. Home-grown solutions are not required. Upgrade to Office 365 ProPlus.

  18. amazing thank you so much you really save my life

  19. Hi,
    I have tried "Highlight differences between 2 sheets with conditional formatting", it worked but the data in two sheets that I have used are not in same order. For example one sheet in alphabetical order by name and other by IDs.
    How to compare two sheets when the data is not in same order?

  20. I tried the conditional formatting technique, however Excel generates an error saying that referencing different worksheets is not allowed with conditional formatting formulas.

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

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

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

  24. 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).

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

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

  27. 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; "")

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

  29. 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!!

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

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

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

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

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

  35. Very Useful!

    Excellent, quality content!

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

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

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

  39. It's very useful to me

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

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

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

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