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 →

Table of contents

Comments page 3. Total comments: 137

  1. I am using Excel 2010 and could not get the Conditional formatting to work at all even when copying formula into the dialog box. To get the IF stmt to work, you will need to format it exactly as written below. There are missing characters in the formula suggested above.
    =IF('Sheet1'!A1'Sheet2'!A1, "Sheet1:"& 'Sheet1'!A1&" vs Sheet2:"&'Sheet2'!A1, "")

  2. Thank you for this great help. Do you know if there is any third party tool for Mac that could do the compare & merge function?

  3. Very Helpful.....

  4. Thank you for the excellent article and for providing your Ablebits Data and Tools add-ins for Excel. I got in trouble recently by unknowingly editing a version of a spreadsheet saved in user\AppData\Roaming\Microsoft\Excel instead of the original file and successfully used Ablebits Data to update the original.

  5. THANK YOU!

  6. Awesome help for a non-expert Excel user. THANK YOU!

  7. Hi there Svetlana

    I have two spreadsheets that I Moved into one Worksheet. I have sheet1 that is my original and sheet2 that is from our client with updated amounts of properties. I need to update our original sheet1 with the sheet2 data.

    I tried if-condition formula i am struggling kindly assit.

  8. In "How to compare two Excel sheets for differences in values", this instruction is unclear:

    "...then copy it down and to the right:"

    Paste it in cell A1 and then B1, C1, A2, B2,, C2 etc? If that is the case, it doesn't work for me. It copies the same formula in the other cells.

    The Excel version is 2010

    1. Hello!

      Yes, you copy the formula to A2, A3, etc, and then to B2, C2 etc, to as many rows and columns as you want to compare. But you should not paste it in other cells! In Excel, a formula is copied by dragging the fill handle. The detailed instructions can be found here: How to copy a formula in Excel.

      Because we use relative cell references, the formula will change based on a relative position of the column and row. As the result, the formula in A1 will compare cell A1 in sheet1 and sheet2, the formula in B1 will compare cell B1 in both sheets, and so on.

  9. The rows are highlighting with colors showing mismatch even though the data in the rows are are identical in the two sheets while comparing with the "Compare sheets Tab". Kindly revert me back.

    1. Hello, Farid,

      Please specify what tool you're using to compare your data sets. If it is Ablebits Compare Sheets for Excel, then please specify what options you choose on each step of the Wizard and send us your workbook to support@ablebits.com.

      I kindly ask you to shorten your table to 10-20 rows / columns and include the link to your comment in the email.

      Thank you.

  10. Hi, I'm not seeing reply to question similar to my own from Amit, August 13, 2017: "...compare two unsorted files with...say 100 columns". TanuM, March 2, 2017 appears to have similar question. Is there an efficient way to compare two Excel sheets with same formatting and columns but rows may be sorted differently? I'm currently using this:
    How to compare two Excel sheets for differences in values
    (=IF(Sheet1!A1 Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")) and this:
    Highlight differences between 2 sheets with conditional formatting.

    1. continuing...
      I'm currently using these two methods but it seems there is manual work required in that the two sheets' rows must be aligned exactly the same prior to using these formulas?

  11. Hi i would like to know how to match one sheet to another sheet with all the cells. I need variation for each cells when comparing each sheet. If anyone know please send me the exact formula.

  12. I would like to know which of these apps to compare excel workbooks is free?

    Cloudy Excel worked well for a couple tabs and then crashed and I haven't been able to get it to work again.

    I'm on Windows 10 with Microsoft Office 365.

    Thanks if anyone can help! I'm mid-project now and STUCK.

  13. Just wanted to say THANK YOU, this works great and saves a lot of work!!

    1. Sorry that was a quick post.
      What I needed was comparing two Excel sheets (a duplicate was created by cloud software and two people had two different versions). So I copied the sheet of one, into the other Excel. And then did the trick highlighting the differences with conditional formatting. Worked out great.

  14. And how do I merge both excel sheets into one and highlight the differences...

  15. hello,
    I need to compare two excel sheets with both sheets have little difference having addition rows and missing rows. in that case how can compare it.

  16. AOA:
    HEY HELLO I WANT TO KNOW ABOUT SOME EXCELL TIPS AS YOUR INSTRUCTION IS VERY USE FULL FOR US PLEASE TELL ME CAN I CHECK BOTH EXCEL SHEET COMPARISON ON LINE

  17. I am trying to find a duplicate program on Excel like Word Doc compare. Our group sends out budgets to sites and the sites make changes without highlighting the changes. We must go line by line to see if there have been any changes. They also add lines so we cannot compare worksheet to worksheet. Is there an easy way to do this?

    1. Copy your files in word and then use compare in word

  18. Thanks for the tips. I used the comparison between two worksheets and it worked perfectly.

  19. Can I merge 2 excel csv and get all the different rows as a result?
    Seems to be possible in Synkronizer but I work on a Mac and this add-on only seem working on windows.
    Or are there other add-ons available running on Mac who can do this?

    Thanks

  20. Can you also illustrate the "Spreadsheet Compare" tool in Microsoft Office Tools 2013 and 2016? I get quite confused by the various comparison options. If there is another tutorial, I haven't found it. Thanks - Anne

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

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

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

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

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

  26. Thx :)

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

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

  29. Thank you for the useful tips

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

  31. it's really useful .

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

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

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

  35. really useful......thanks

  36. excellent support and guideline

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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