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

  1. I love you! Made a vast difference in validation and confidence in ongoing formal report submission!

  2. hi
    i want to know about that how can i check same alphabets and numbers in one column ,like same values can check with highlight but i need alphabets and numbers in select column.

  3. Hi, I need to compare the two sheets but the data is in random order. Eg In the names column both sheet have names but not sorted. Which works best for that. Thanks

  4. We have 2 Excel files. One is called week 16 and the other is called week 17. Visibly they look the same but week 16 is 2.5mb and week 17 is 17mb. To create week 17, we save week 16 and then rename. Then we would use week 17 for that week before we create week 18. How can I tell what has changed between week 16 and 17 that has caused it to balloon in size? There's a lot of information and used cells on the visible page as well quite a few hidden tabs. Would take me forever to sit and compare visually.

  5. Hello,
    I worked in excel daily same pattern. I have based on Previous file opend, save as current date and working daily. Is there any solution i dont opened previous file and continue with my work??

  6. Svetlana, you are a hero.

  7. your title is misleading. what you have posted about is CONTRASTING two sheets/files. Looking for differences. Comparing means to identify what is similar. I was looking for finding similarities between two sheets and wasted time on your post.

    1. "Comparing means to identify what is similar" - No it doesn't!

  8. Can we extend the formula "=A1sheet_name!A1" to refer another workbook for executing conditional formatting ?

  9. When I use the solution for comparing worksheets I get the following error:

    "You may not use references to other workbooks for conditional formatting criteria"

    his is after highlighting entire 2-2019 worksheet, and creating new conditional formating rule to "use a formula to determine which cells to format" with "format values where this formula is true" set to =A11-2019!A1 .. where 1-2019 is the worksheet I'm trying to find to compare 2-2019 to.

    1. I have the same question as raised by the post 8, 75 and 76. It seems to me that both sheets have to be in the same file or workbook. To get around, open a new Sheet2 in workbook 2-2019 and copy and paste the entire Sheet1 of workbook 1-2019 over with Ctrl+Shift+End. Then you can use =A1Sheet2!A1 to highlight the differences with conditional formatting.

  10. Hi- I have to compare 2 spreadsheets to find discrepancies. The problem with the standard way for our spreadsheets is that since we are receiving it from 2 different sources ( ex. 1 from a client and the other from their payroll company) the same data is called 2 different terms but for our purposes it is actually the same. For examplez the payroll company calls the insurance plan they are on "02371" but the client calls it "PPO". In the normal format, that is a discrepancy. For our purposes this is actually a match.

    Thank you.

  11. Copy paste your excel files to a word document and then use compare in word.

  12. Good Afternoon!
    I am a neophyte in using formulas in excel.
    In this formula, I have no idea what values to use.

    Do I assume that [ Sheet1!A1 ] is the name of sheet 1 in my comparison and [ Sheet2!A1 ] is the name of sheet 2 in my comparison?
    =IF(Sheet1!A1 Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

    What about [ ]?

    How to I designate the path for the file names?

    Thx!

    -- Dave

  13. My issue is I converted 2 large invoices (for 2 different months) from PDF to Excel and I want to see all differences between the 2, almost like a third generated file which has the differences in text and values. Best solution for? Thank you.

  14. First of all, any line of information is very meaning full. consequently, are they going to function on MacBook as well as windows?
    Thanks

    1. Hello Tammy,

      All of the third-party solutions described in this tutorial function in Excel for Windows only. The built-in features will probably work in Excel for Mac too, though we have not tested it. Out blog focuses solely on Excel for Windows, sorry.

  15. Dear Svetlana,

    the advice is very useful, helped a lot, saves hours of work!
    Only one point, the formula for the differences in value I had to change a bit, because it didn't work:
    =IF(Sheet1!A4 Sheet2!A4; "Sheet1:"&Sheet1!A4&"vs Sheet2:"&Sheet2!A4;""). - MS EXCEL 2010
    Anyway we are using the highlighted values, thank you very much!
    Peter

    1. Hi Peter,

      It looks like the List Separator in your Regional Settings is set to semicolon, so Excel uses it for separating arguments in formulas. On my computer, it is set to comma :)

  17. Hi

    I have two worksheets with different values and I am trying to compare if worksheet 2 (new code)data in column c matches the value of the original code in worksheet 1. I am currently going through each line and comparing because the rows and columns are not identical using the find function. Can you please share a simple methos or whether the conditional formatting is ideal? Thank you

  18. Hi,

    I am trying to find the best excel version that will compare bank cash to what is in our system. We have to compare the daily deposits to what is posted in the system. the problem is there may be several checks that have been posted and I have to go one by one and add all of them to see if it equals what the bank has posted. Is there a version that will do this for me? Thanks

    1. hi can you please tell me if you found the that excel version

  19. I think this editor doesn't accept the mathematical operator that is used to mean "not equal to" because it keeps removing it. It belongs after =IF('Sheet1'!A1...and is shown correctly in the original formula.
    Sorry I can't enter it here.
    Best of luck
    hg

  20. Sorry, here's the right formula. Looks like in my previous attempt there was a missing operator.
    =IF('Sheet1'!A1'Sheet2'!A1, "Sheet1:"& 'Sheet1'!A1&" vs Sheet2:"&'Sheet2'!A1, "")

  21. 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, "")

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

  23. Very Helpful.....

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

  25. THANK YOU!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  46. Thx :)

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

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

  49. Thank you for the useful tips

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

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