How to compare two Excel files for differences

In this tutorial, you will 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.

When you have two similar Excel workbooks, or better say two versions of the same workbook, what's the first thing you usually want to do with them? Right, compare those files for differences, and then probably merge them into a single file. In addition, workbook comparison can help you spot potential problems like broken links, duplicate records, inconsistent formulas or wrong formatting.

So, let's have a closer look at various methods to compare two Excel sheets or entire workbooks and identify differences between them.

How to compare two Excel files by viewing them side by side

If you have relatively small workbooks and a sharp eye for detail, this quick and easy way to compare Excel files might work well for you. I am talking about View Side by Side mode that lets you arrange two Excel windows side by side. You can use this method to visually compare two workbooks or two sheets in the same workbook.

Compare 2 Excel workbooks

Let's say you have sales reports for two months and you want to view both of them simultaneously to understand which products performed better this month and which did better last month.

To open two Excel files side by side, do the following:

  1. Open the workbooks you want to compare.
  2. Go to the View tab, Window group, and click the View Side by Side button. That's it!

To view two Excel files simultaneously, click the View Side by Side button.

By default, two separate Excel windows are displayed horizontally.

To split Excel windows vertically, click Arrange All button and select Vertical:
Arrange two Excel windows vertically.

As the result, two separate Excel windows will be arranged side by side, like in the below screenshot.

If you want to scroll through both worksheets simultaneously to compare data row-by-row, make sure the Synchronous Scrolling option it turned on. This option resides on the View tab, in the Window group, right under the View Side by Side button, and is usually turned on automatically as soon as you activate View Side by Side mode.
Scroll through both worksheets simultaneously to compare data row-by-row.

For more information about using this Excel feature, please see View Excel workbooks side by side.

Arrange multiple Excel windows side by side

To view more than 2 Excel files at a time, open all the workbooks you want to compare, and click the View Side by Side button. The Compare Side by Side dialog box will appear, and you select the files to be displayed together with the active workbook.
Arrange multiple Excel windows side by side.

To view all open Excel files at a time, click the Arrange All button on the View tab, in the Window group, and choose your preferred arrangement: tiled, horizontal, vertical or cascade.

Compare two sheets in same workbook

Sometimes, 2 sheets that you want to compare reside in the same workbook. To view them side by side, perform the following steps.

  1. Open your Excel file, go to the View tab > Window group, and click the New Window
    Compare two sheets in same workbook.
  2. This will open the same Excel file in a different window.
  3. Enable View Side by Side mode by clicking the corresponding button on the ribbon.
  4. Select sheet 1 in the first window and sheet 2 in the second window.

How to compare two Excel sheets for differences in values

It is the simplest way to compare data in Excel that lets you identify cells with different values. As the result, you will have a difference report in a new worksheet.

To compare two Excel worksheets for differences, just open a new empty sheet, enter the following formula in cell A1, and then copy it down and to the right by dragging the fill handle:

=IF(Sheet1!A1<> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

Due to the we use of 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. The result will look similar to this:
Comparing two Excel sheets for differences in values

As you can see in the above screenshot, the formula compares 2 sheets, identifies cells with deferent values and displays the differences in corresponding cells. Please note that in the difference report, dates (cell C4) are presented by serial numbers as they are stored in the internal Excel system, which is not very convenient for analyzing differences between them.

Highlight differences between 2 sheets with conditional formatting

To highlight cells that have different values in two sheets with the color of your choosing, use the Excel conditional formatting feature:

  • In the worksheet where you want to highlight differences, select all used cells. For this, click the upper left cell of the used range, usually A1, and press Ctrl + Shift + End to extend the selection to the last used cell.
  • On the Home tab, in the Styles group, click Conditional Formatting > New rule, and create a rule with the following formula:

    =A1<>Sheet2!A1

    Where Sheet2 is the name of the other sheet you are comparing.

As the result, the cells with different values will get highlighted with the selected color:
Create a conditional formatting rule to highlight differences between 2 sheets.

If you are not very familiar with Excel conditional formatting, you can find the detailed steps to create a rule in the following tutorial: Excel conditional formatting based on another cell value.

As you see, it's very easy to compare two Excel sheets by using formulas or conditional formats. However, these methods are not well suited for all-round comparison because of the following limitations:

  • They find differences only in values, but cannot compare formulas or cell formatting.
  • They cannot identify added or deleted rows and columns. As soon as you add or delete a row / column in one sheet, all subsequent rows / columns will be marked as differences.
  • They work on a sheet level, but cannot detect workbook-level structural differences such as sheet additions and deletions.

Compare and merge copies of a shared workbook

When it comes to merging different versions of the same Excel file, the Compare and Merge feature comes in handy. It is especially useful when several users collaborate on the same Excel workbook because it lets you view the changes and comments of all users at a time. To leverage this feature, be sure to do the following preparations:

  • Share your Excel workbook before you make it available to other users.

    To share a workbook, just click the Share Workbook button on the Review tab, in the Changes group, select the Allow Changes by More Than One User… box, and click OK. Allow Excel to save the workbook if prompted. Turning on the Track Changes feature shares the workbook automatically.

  • Each person who makes any edits to the shared workbook must save a copy (.xls or xlsx file) using a unique file name.

Now that all initial preparations are done properly, you are ready to combine the copies of a shared workbook.

1. Enable the Compare and Merge Workbooks feature in your Excel

Although, the Compare and Merge Workbooks feature is available in all versions of Excel 2016, Excel 2013, Excel 2010 and lower, this command is not displayed anywhere in Excel by default. To add it to the Quick Access toolbar, perform the following steps:

  • Open the Quick Access drop down menu and select More Commands.
  • In the Excel Options dialog box, select All Commands under Choose commands from.
  • In the list of commands, scroll down to Compare and Merge Workbooks, select it and click the Add button to move it to the right-hand section.
  • Click OK.

Add the Compare and Merge Workbooks feature to the Quick Access toolbar.

2. Compare and merge workbooks

When all of the users have finished working with your shared Excel workbook, you can merge all the copies into one file.

  • Open the primary version the shared workbook.
  • Click the Compare and Merge Workbooks command on the Quick Access toolbar.
    Click the Compare and Merge Workbooks command on the Quick Access toolbar
  • In the dialog box that appears, select a copy of the shared workbook that you want to merge. To select several copies, hold the Shift key while clicking the file names, and then click OK.
    Select a copy of the shared workbook that you want to merge.

Done! The changes from each copy are merged into a single workbook.

3. Review the changes

To see all the edits by different users at a glance, just do the following:

  • Switch to the Review tab > Changes group, and click Track Changes > Highlight Changes.
  • In the Highlight Changes dialog, select All in the When box, Everyone in the Who box, clear the Where box, select the Highlight changes on screen box, and click OK.

Highlight changes by different users

To point out the row and columns with differences, Excel highlights the column letters and row numbers in a dark red color. At the cell level, edits from different users are marked with different colors. To see who made a specific change, just hover over the cell.
Edits from different users are marked with different colors.

Note. If the Compare and Merge Workbooks command is greyed out in your Excel, most likely you are trying to combine different Excel files. Please remember, the Compare and Merge Workbooks feature allows merging only copies of the same shared workbook.

Third-party tools to compare Excel files

As you have just seen, Microsoft Excel provides a handful of features to compare data in two or more workbooks. But none of the built-in options is sufficient to comprehensively compare Excel sheets, let alone entire workbooks, spotting all the differences in values, formulas or formatting.

So, if you need advanced and really efficient means to compare two Excel files, then most likely you would have to use one of the third-party tools specially designed for comparing, updating and merging Excel sheets and workbooks. Below you will find a quick overview of a few tools that, in my opinion, are best performers in this area.

Synkronizer Excel Compare: 3-in-1 tool to compare, merge and update Excel files

The Synkronizer Excel Compare add-in can quickly compare, merge and update two Excel files saving you the trouble of searching for differences manually.

If you are looking for a quick and reliable method to compare two Excel sheets or workbooks, you will certainly find helpful the following features of Synkronizer Excel Compare:

  • Identifying the differences between two Excel sheets.
  • Combining multiple Excel files into a single version without producing unwanted duplicates.
  • Highlighting the differences in both sheets.
  • Showing only the differences that are relevant to your task.
  • Merging and updating the sheets.
  • Presenting detailed and easy to read difference reports.

To get some basic idea about Synkronizer Excel Compare's capabilities and performance, let's carry out a couple of field tests.

Compare two Excel files for differences

Supposing you are organizing some event and, in your Excel table, you gather information about the participants such as a participant name, arrival date, number of seats, etc. Also, you have a couple of managers in direct contact with participants and the database, and as a result, you have 2 versions of the same Excel file.

Okay, let's see how efficiently Synkronizer can compare our two sheets and identify differences between them.

To run Synkronizer Excel Compare, go to the Add-ins tab, and click the Synchronizer 11 icon.
Run the Synkronizer Excel Compare add-in.

The Synkronizer pane will show up in the left part of your Excel window, where you do the following:

  1. Select 2 workbooks to compare:
    Select 2 workbooks to compare.
  2. Select sheets to compare.

    If the selected workbooks have any sheets with the same names, all those sheets will be matched and automatically selected for comparison (like Participants sheets in the below screenshot).

    Also, you can select worksheets manually or instruct the add-in to match sheets by other criteria, for example by worksheet type - all, protected, or hidden.

    Select the sheets to compare.

    Once you've selected the sheets, the Synkronizer add-in will open them side by side, arranged vertically or horizontally, like in Excel's View Side by Side mode.

  3. Select one of the following comparison options:
    • Compare as normal worksheets - the default option that works in most cases.
    • Compare with link options - if the selected sheets do not contain any new or deleted rows and columns, you can compare them "1 on 1".
    • Compare as database - recommended for comparing sheets that have a database structure.
    • Compare selected ranges - if you do not want to compare the entire sheets, define the ranges to be compared.
  4. Choose the content types to be compared (optional).

    On the Select tab, in the Compare group, you can choose the content type(s) relevant to your current task:

    Select the content type(s) you want to compare.

    • Under Content, you can select comments and names (in addition to cell values, formulas and calculated values that are compared by default).
    • Under Formats, you choose what cell formats such as alignment, fill, font, border etc.
    • The Filters option lets you filter out differences that you don't want to display. For example, you can ignore case, leading or trailing spaces, all formulas or different formulas with the same result, hidden rows or column, and more.
  5. Finally, click the big red Start button on the ribbon and proceed to examining the results.

Visualizing and analyzing the differences

Usually it takes Synkronizer only a few seconds to compare 2 sheets and present two summary reports on the Results tab:

  • In a summary report, you can see all difference types at a glance: changes in columns, rows, cells, comments, formats, and names.
  • To view the detailed difference report, just click a specific difference type on the summary report.

The following screenshot shows the summary report (in the upper part of the Results pane), and cell difference report (in the lower part of the pane) that were created for our sample sheets:
A brief summary report and detailed difference report are created.

Clicking on a difference in the detailed report will select the corresponding cells on both sheets (the below screenshot shows just one sheet because there's enough room to show both :)
As soon as you click on a difference, you will jump to the corresponding cells on both sheets.

In addition, you can create a difference repot in a separate workbook, either standard or hyperlinked, and jump to a specific difference with a mouse click:
A detailed difference repot in a separate workbook

Compare all sheets in two workbooks at a time

If the two Excel files you are comparing contain multiple sheets, all matching worksheet pairs will be presented in the summary report for your perusal:
Compare all sheets in two workbooks at a time

Highlighting differences between sheets

By default, the Synkronizer Excel Compare add-in highlights all found differences, like in the following screenshot:

  • Yellow - differences in cell values
  • Lilac - differences in cell formats
  • Green - inserted rows

Highlighting differences between sheets

To highlight only the relevant differences, click the Outline button on the Results tab, and select the required options:
Highlight only the relevant differences.

Update and merge sheets

The merge function is definitely one of the most useful features of the Synkronizer Excel Compare add-in. You can transfer individual cells or move different columns/rows from the source to target sheet, and have your primary sheet updated in seconds.

To update one or more differences, select them on the Synkronizer's pane and click one of the 4 update buttons - the first and last buttons update all differences, while the 2nd and 3rd buttons update selected differences only (the button arrows indicate the transfer direction):
Update and merge sheets.

Well, these are the key features of the Synkronizer add-in, but there is certainly much more to it. Want to give it a try? An evaluation version is available for download here.

Synkronizer is certainly worth your attention, but it's not the only way to compare files in Excel. A handful of other comparison tools exist, which basically provide the same set of features but in different implementations.

Ablebits Compare Sheets for Excel

The latest version of our Ultimate Suite includes over 40 new features and improvements, the most exciting of which is Compare Sheets - our own tool to compare worksheets in Excel.

To make the comparison more intuitive and user-friendly, the add-in is designed in this way:

  • A step-by-step wizard walks you through the process and helps configure different options.
  • You can choose the comparison algorithm best suited for your data sets.
  • Instead of a difference report, the compared sheets are displayed in the Review Differences mode so that you could view all the differences at a glance and manage them one-by-one.

Now, let's try the tool on our sample spreadsheets from the previous example and see if the results are any different.

  1. Click the Compare Sheets button on the Ablebits Data tab, in the Merge group:
    Ablebits Compare Sheets Wizard for Excel
  2. The wizard will show up asking you to select the two worksheets you'd like to compare for differences.

    By default, the entire sheets are selected, but you can also select the current table Select the current table. or a specific range Select a range. by clicking the corresponding button:
    Select the two worksheets you'd like to compare for differences.

  3. On the next step, you select the comparison algorithm:
    • No key columns (default) - works best for sheet-based documents like invoices or contracts.
    • By key columns - is appropriate for column-organized sheets that have one or more unique identifiers such as order numbers or product ID's.
    • Cell-by-cell - best to be used to compare spreadsheets with the same layout and size, like balance sheets or year-to-year reports.
    Tip. If you are unsure which option is right for you, go with the default one (No key columns). Whichever algorithm you select, the add-in will find all the differences, it will only highlight them differently (entire rows or individual cells).

    On the same step, you can choose the preferred match type:

    • First match (default) - compare a row in Sheet 1 to the first found row in Sheet 2 that has at least one matching cell.
    • Best match - compare a row in Sheet 1 to the row in Sheet 2 that has the maximum number of matching cells.
    • Full match only - find rows in both sheets that have exactly the same values in all the cells, and mark all other rows as different.

    In this example, we will look for Best match by using the default No key columns comparing mode:
    Select the comparison algorithm and match type.

  4. Finally, specify which differences to highlight and which to ignore, and how to mark the differences.

    Cell formatting is important to us, so we select Show differences in formatting. Hidden rows and columns are irrelevant, and we tell the add-in to ignore them:
    Choose which differences to highlight and which to ignore.

  5. Click the Compare button and allow the add-in a couple of minutes to process your data and make the backup copies. Backups are always created automatically, so you may not worry about the safety of your data.

Review and merge differences

Once the worksheets are processed, they are opened side-by-side in a special Review Differences mode, with the first difference selected:
Reviewing the differences between two worksheets

On the screenshot above, the differences are highlighted with the default colors:

  • Blue rows - rows that exist only in Sheet 1 (on the left).
  • Red rows - rows that exist only in Sheet 2 (on the right).
  • Green cells - difference cells in partially matching rows.

To help you review and manage the differences, each worksheet has its own vertical toolbar. For the inactive worksheet (on the left) the toolbar is disabled. To enable the toolbar, just select any cell in the corresponding sheet.

By using this toolbar, you go through the found differences one-by-one and decide whether to merge or ignore them:
Review and merge the differences.

As soon as the last difference is dealt with, you will be prompted to save the workbooks and exit the Review differences mode.

If you have not finished processing the differences yet but would like to take a break for now, click the Exit Review Differences button at the bottom of the toolbar and choose either to:

  • Save the changes you've made and remove the remaining difference marks, or
  • Restore the original workbooks from the backup copies.

Exit the Review differences mode.

That's how you compare two sheets in Excel with our tool (hope you liked it :) If you are curious to give it a shot, an evaluation version is available for download here.

xlCompare: compare and merge workbooks, sheets and VBA projects

Using the xlCompare utility, you can compare two Excel files, worksheets, names and VBA Projects. It identifies added, deleted and changed data and allows you to quickly merge differences. In addition, it provides the following options:

  • Find duplicate records between two worksheets and remove them.
  • Update all existing records in one sheet with values from another sheet.
  • Add unique (new) rows and columns from one sheet to another.
  • Merge all updated records from one workbook to another.
  • Sort data on the sheets by the key column.
  • Filter the comparison results to display differences or identical records.
  • Highlight comparison results with colors.

Florencesoft DiffEngineX: compare Excel workbooks for differences

DiffEngineX is another utility that can help you identify differences between cell values, formulas, comments, Excel names, and VBA code. It can compare two Excel workbooks or selected sheets.

DiffEngineX works with xls, xlsx, xlsm and xlsb files, and supports Excel 2013, Excel 2010, Excel 2007 and Excel 2003.

Change pro for Excel: compare Excel sheets on desktop and mobile devices

With Change pro for Excel, you can compare two sheets in desktop Excel as well as on mobile devices with optional server-based comparison. The key features of this tool are:

  • Find differences between 2 sheets in formulas and values.
  • Identify layout changes, including added / deleted rows and columns.
  • Recognize embedded objects such as charts, graphs and images.
  • Create and print difference reports of formula, value and layout differences.
  • Filter, sort, and search the difference report on key changes.
  • Compare files directly from Outlook or document management systems.
  • Support for all languages, including multi-byte.

Online services to compare Excel files

Apart from desktop tools and utilities, there exist a number of online services that let you quickly compare two Excel sheets for differences without installing any software on your computer. Probably it's not the best solution in terms of security, but if your Excel files do not contain any sensitive information why not use some free online service for immediate results?

For example, you can try XLComparator or CloudyExcel, or find some other similar services on the net. This is how the CloudyExcel service looks like:
Online service to compare Excel files

You just upload the two Excel workbooks you want to compare, and click the Find Difference button at the bottom of the screen. In a moment, the differences in two active sheets will get highlighted with different colors:
The differences in two active sheets are highlighted with different colors.

Well, this is how you can compare Excel files for differences. If none of the solutions described in this tutorial is suitable for your task, check out the following resources that cover other aspects of Excel file comparison. And if you know any other ways to compare two Excel files, your comments will be greatly appreciated. I thank you for reading and hope to see you on our blog next week!

Other ways to compare and merge data in Excel

86 Responses to "How to compare two Excel files for differences"

  1. Jaime Lechuga says:

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

  2. Muthu Raj says:

    how to avoid circular reference

  3. Brian Myers says:

    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.

  4. Exotic Hadron says:

    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.

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

  5. gud says:

    It's very useful to me

  6. avi says:

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

  7. Piotr says:

    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.

  8. Manuel says:

    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.

  9. Hassan says:

    Very Useful!

    Excellent, quality content!

  10. Rohit Gupta says:

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

  11. Irv says:

    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

    • Hi Irv,

      You can use Duplicate Remover for this task:
      https://www.ablebits.com/excel-remove-duplicates/index.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.

  12. tan ke jun says:

    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.

  13. ajit says:

    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.

  14. ashok says:

    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

  15. Sharlah says:

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

  16. Ram says:

    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?

  17. preethi says:

    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

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

      • Michael says:

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

  18. ben says:

    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

  19. Mark says:

    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.

  20. Steve Reed says:

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

  21. Rob says:

    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

  22. Daniel says:

    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. Jennifer says:

    How about "side by side" for MacIntosh?

  24. david says:

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

  25. TanuM says:

    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?

  26. Fadia says:

    amazing thank you so much you really save my life

  27. Flodo says:

    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.

  28. MASOOD says:

    excellent support and guideline

  29. Samson .S says:

    really useful......thanks

  30. Brenda says:

    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?

  31. Sathya says:

    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

  32. shiva says:

    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. Yaling says:

    it's really useful .

  34. jyotish says:

    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

  35. Sharath says:

    Thank you for the useful tips

  36. Amit says:

    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

  37. Phil says:

    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!

  38. Rahul Vidhate says:

    Thx :)

  39. YossiD says:

    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.

  40. Anthony says:

    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!

  41. Soori says:

    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.

  42. Michiel says:

    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. Eva says:

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

  44. Anne says:

    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

  45. Batist says:

    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

  46. Ralph Alberti says:

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

  47. Sharon Green says:

    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?

  48. FARAZ HUSNAIN says:

    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

  49. jalson says:

    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.

  50. Jalson says:

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

  51. Guido van Es says:

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

    • Guido van Es says:

      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.

  52. Dede says:

    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.

  53. Suresh says:

    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.

  54. LyndaL says:

    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.

    • LyndaL says:

      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?

  55. Farid says:

    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.

  56. I Washburn says:

    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

    • 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. The fastest way to copy the formula is to drag 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.

  57. Collen says:

    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.

  58. felicia says:

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

  59. felicia says:

    THANK YOU!

  60. Laurence says:

    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.

  61. Janak R says:

    Very Helpful.....

  62. Maya says:

    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?

  63. hg says:

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

  64. hg says:

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

  65. hg says:

    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

  66. amchugh371@gmail.com says:

    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

  67. DL says:

    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

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite 2018.4 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard