How to Compare Two Sheets in Excel

Compare Two Sheets is part of Ablebits Ultimate Suite for Excel that can help you quickly find and highlight different rows or cells in two Excel worksheets and merge them manually cell by cell. Find answers to the most frequently questions about this tool on the Compare Two Sheets: FAQ page.

Video: How to compare Excel sheets

Before you start

Please read carefully this part before you start working with the add-in.

Xlsx & xlsm only

The supported workbook formats are .xlsx and .xlsm. The tool won't process files of any other format, so be sure to save your workbooks as xlsx. or .xlsm. files.

Comparing worksheets from OneDrive

Using the tool, you can compare worksheets stored in OneDrive, both in My files and Shared. However, make sure that shared files are synced and opened from your local device.

Save all your changes

Before running Compare Two Sheets, save all changes in all open books and close all Excel workbooks except for those you are going to compare. If your task is to compare sheets from the same workbook, leave only this book open.

Backup copies

We care about your books and always create their backup copies. By default, we store the backups for 14 days.

Cells formatting

Compare Two Sheets marks differences with a background color, bottom border, or font color replacing your coloring, fonts, or cell borders, so the colors you choose for marking differences should not coincide with your original formatting. Your original formatting will be restored as you review the differences.

Protected workbooks

Protected workbooks are not supported as we simply won't be able to mark differences in them :)

Merged cells

Worksheets with merged cells cannot be processed.

Volatile functions

Please do not try to compare sheets that contain volatile functions, because every time Excel opens or recalculates the workbook, these functions return new values. The volatile functions are: NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT. Also, INFO and CELL can be volatile depending on their arguments.

What is ignored

Some differences are not shown: row height, column width, worksheet and workbook options and properties, all embedded objects including charts, pivot tables, images, shapes, controls, etc.

Compare Two Sheets is designed only to compare one sheet with another. Your worksheets should have the same layout and structure including grouping, filtering, and sorting. For example, we consider the rows order when comparing sheets, so sheets with different sorting order will most likely be perceived as completely different.

How to use Compare Two Sheets

Start Compare Two Sheets

Note. If you are going to work with shared OneDrive files, before running the tool, make sure they are not opened by other users.

To run the add-in, on the Ablebits Data tab, in the Merge group, click Compare > Compare Two Sheets:
Start Compare Two Sheets.

In the first window, you will see the list of limitations and requirements, please read them carefully:
Compare Two Sheets: Requirements & Limitations.

Note. To stop the add-in from displaying the Requirements and Limitations window each time you run the tool, check Do not show this next time:
Check off the Do not show this next time box if needed.

Click the Agree button to continue.

Step 1: Select your worksheets and ranges

Choose the sheets to compare.

In the list of open books, select the sheets you are going to compare. If the needed worksheet is not in the list, click the Open Workbook button above the list and open the Excel file you need.

By default, the tool compares the used ranges of the sheets. If you need to compare other ranges, click the Select range icon and choose the areas of interest. Also, you can pick a cell in the table and click the Expand selection icon to get the whole table highlighted.

Tip. Click the Ablebits logo to open a pop-up menu. Pick Options to change the storage time for backup copies and the path to the backup folder.
Note. If your workbook is greyed out, that means it cannot be selected for comparison (most likely because of the requirements and limitations discussed above):
Your book is greyed out.

After selecting the sheets to be compared, click the Next button to proceed.

Step 2: Specify the comparing mode

On this step, you choose how to compare your sheets and select the matching options.
Select the necessary comparison mode.

Select the comparing mode

  • No key columns
    The first type suits to sheet-based documents like invoices or contracts. These sheets may have a different number of rows or include one or even several tables. It is important to preserve the structure of these sheets and avoid comparing, for example, header rows with the main table rows.

    Look how it works in the Compare spreadsheets by all columns video.

  • By key columns
    Select this type if you have column-organized tables that contain row-by-row data. For example, two price lists with the same "SKU" or "Product ID" columns. Usually, these columns are called "key columns", this is why this comparing mode is called By key columns. Such sheets always have the same number of columns but may include a different quantity of rows.

    To see how it works, watch the Compare sheets by key columns video.

  • Cell-by-cell
    The third type is designed for sheets with the same layout and size, like balance sheets or year-to-year reports.

    Watch the Compare Excel files cell by cell video tutorial to see how to work with this option.

Indicate your matching option

Checking line by line, Compare Two Sheets searches for differences, looking for pairs of similar rows in the sheets. But there are cases when one row in Sheet 1 corresponds to several rows in Sheet 2 with full or partial cell match. For such situations, Compare Two Sheets provides three matching options:

  • First match
    If you select First match for AAA row in Sheet 1, Compare Two Sheets will match it to the first row that contains at least one A value. Other rows with A values will be marked as unique:
    Compare and search for a first match.
  • Best match
    If you select Best match for AAA row in Sheet 1, Compare Two Sheets will match it to the row that contains the maximum number of A values. Other rows with A values will be marked as unique:
    Find the best match when comparing sheets.
  • Full match only
    If you select Full match only for AAA row in Sheet 1, Compare Two Sheets will only mark the AAA row as a match, if finds any. Other rows with A values will be marked as unique:
    For when you need to find full matches only.
Note. Fully matching rows have top priority for the tool, regardless of the chosen option. If the documents have completely identical rows, the tool will select them as matching, ignoring the rows with the First or the Best match. All rows with a partial match will be marked as unique ones:
Fully matching rows as top priority.

Choose the appropriate comparing mode and click Next.

Step 3: Select the key columns (if there are any)

If in the previous step you chose the By key columns comparing mode, Compare Two Sheets will ask you to select the key columns. Simply pick them for Sheet 1 and the add-in will automatically find the corresponding columns in Sheet 2:
Specify key columns.

Click Next.

Step 4: Choose your comparison options

By default, Compare Two Sheets searches for differences in values only, but you can select other options as well:
Choose comparison options.

  • Show differences in formulas
    Different formulas can return the same values, that is why differences in formulas are ignored. But if they are important for you, check this option.
  • Show differences in formatting
    Select this one to search for differences in cells formatting.
  • Ignore hidden rows/columns
    By default, we ignore hidden rows and columns. If you need to find differences in hidden rows or columns, uncheck this option. But of course, you will not see these differences marked if you don't make them visible at first :)
  • Ignore empty rows/columns
    Sometimes empty rows and columns can be an important part of your data. If this is your case, leave this box unselected and they will be included in the comparison. Tick this option if you don’t want to compare empty rows.
  • Ignore leading/trailing spaces
    Sometimes you may have extra spaces at the start or at the end of text values in Excel. Tick this option if you prefer such spaces to be ignored by the add-in.
  • Add a status column
    If you pick this option, Compare Two Sheets will add a new column to your sheets and mark the found differences there as well as in the selected range. It will not only make the result of comparison more understandable but will also let you manage differences comfortably.
  • Mark with the status column only
    If you choose not to do anything with the cells, tick this option and the differences will be marked with color only in the status column. Your tables will remain intact.
  • Add text labels to status column
    Select this option if you want unique, different, and matched records to be identified with text labels in the status column.
  • Mark differences with
    Choose the marking option that suits you best. By default, we suggest using a background color. But if you need to see the original color of your cells, you can mark differences with a font color or bottom border.

Select all the needed options and click Compare.

Understanding the comparison process

When you click the Compare button, Compare Two Sheets follows a certain algorithm. Here is the step-by-step description:

  1. The compared workbooks are saved, including all the changes made before the comparison, and closed (or one workbook if it contains both of the specified sheets).
  2. The backup copies are saved. The path and store time can be found and changed in the tools' options.
  3. Compare Two Sheets loads the workbooks in memory (not in Excel, it is a separate application/process) and scans them for differences.
  4. Your sheets are processed row by row.
  5. The tool takes the first rows from Sheet 1 and Sheet 2 and compares them cell by cell.
  6. If all the cells are different, the rows are marked as unique.
  7. If at least one pair of cells in the found pair of rows matches, then the rows are deemed partially matching in both sheets, and nonidentical cells are deemed different.
  8. The tool compares the next pair of rows and repeats steps 5 - 7.
  9. When processing is finished, your workbooks are opened in the Review Differences mode.
Note. This description is simplified as much as possible with only one purpose - to improve understanding. Compare Two Sheets uses a modified, but nevertheless quite a standard algorithm for comparing two large data sets.

Over the long years of its existence, this algorithm has been sharpened and tested by many mathematicians and programmers. However, sometimes, at first glance, the result may seem wrong to you. In this case, you only need to carefully examine your cells, their values, and formatting, as well as the relative positions of the rows and the reference structure of the formulas. And in almost 100% of cases, you can find a clear mathematical explanation for the results, which at first seemed illogical.

If you run into something that you cannot explain and that will throw you into hours of meditating about the fate of the universe, please send us the samples of your sheets, and we'll help you figure it out.

How to work in the Review Differences mode

What is the Review Differences mode

The add-in processes your worksheets and opens them in the Review Differences mode:
Review differences mode.

Your sheets are opened side by side and the cells are marked according to the options chosen on step 4. In the screenshot above, you can see the default colors:

  • Blue rows - rows that exist only in Sheet 1.
  • Red rows - rows that exist only in Sheet 2.
  • Green cells - cells that differ in Sheet 1 and Sheet 2 in partially matching rows.

Each window has a vertical toolbar with several buttons. In the inactive window, the toolbar is disabled and decolorized to make you focused on the active worksheet.

Note. Please be very careful while processing your data in the Review Differences mode. If you start using the toolbar and then delete something in your table, neither the Undo button on the toolbar nor Excel's Undo will recover the deleted information.

How to use the toolbar

The toolbar's buttons are divided into three groups. Each button performs one action for the currently selected cells or rows in the active sheet. Here is a short description of the buttons from top to bottom:
How to merge Excel sheets.

  1. Use the buttons Go to the previous difference and Go to the next difference to navigate between the found differences in the active worksheet. For blue rows and red rows, the buttons select the whole previous or next row. For green cells, the buttons navigate cell by cell.
  2. The Insert the selected unique rows to the other worksheet button inserts all red or blue rows included in your selection into the inactive worksheet. After insertion, Compare Two Sheets navigates to the next difference if the Auto scroll option is on.
    Tip. If you try to transfer formulas with relative cell references from Sheet1 to Sheet2, they will be changed accordingly. The result may change or even return a formula error based on a relative position of rows and columns in another workbook.
    Please use the Paste values option instead if your task is to pull formula results.
  3. The Copy the selected cells marked as different to the other worksheet button copies all green cells included in your selection to the inactive worksheet. After copying, Compare Two Sheets navigates to the next difference if the Auto scroll option is on.
    Tip. If you try to transfer formulas with relative cell references from Sheet1 to Sheet2, they will be changed accordingly. The result may change or even return a formula error based on a relative position of rows and columns in another workbook.

    Please use the Paste values option instead if your task is to pull formula results.

  4. The Delete the selected differences button deletes all red or blue rows included in your selection (depending on the currently active sheet). Also, this button clears all green cells in both worksheets if they are included in your selection.
  5. The Ignore the selected differences and remove the difference marks from the selected cells button restores your original formatting for blue or red rows included in your selection (depending on the currently active sheet). For green cells, the button restores the original formatting in both worksheets. After ignoring, the tool navigates to the next difference if the Auto scroll option is on.
  6. Undo and Redo cancel and restore the actions of the buttons described above.
Note. Compare Two Sheets synchronizes selection in both worksheets. Unfortunately, Excel cannot highlight the selected cells in the inactive window. Thus, the tool can only automatically scroll the sheets to show you the current row in each sheet as the second row from the top.
Note. As you review the results, Compare Two Sheets removes all the reviewed differences from the internal difference list, so you won't be able to navigate between them.
At the bottom of the toolbar, you can click the Settings icon and open a pop-up menu with several options:

Compare Excel spreadsheets options.

  • Choose Options to indicate the path and storage time for the backup copies of your files.
  • If Auto scroll option is turned on, you will automatically navigate to the next difference after your each action. We recommend not turning this option on until you get comfortable with the Compare Two Sheets tool.
  • Pasting options here are similar to Excel's built-in paste options. You can use them to insert and copy blue and red rows and green cells.
Note. Please be careful when using options other than Paste all, as only for this option Excel gives an opportunity not to use the clipboard. For all the other options, Compare Two Sheets will use the clipboard, which may override the data that is already there.

How to exit the Review Differences mode

There are several ways to exit the Review Differences mode and get back to your normal Excel view:

Exit when all the differences are reviewed

By default, we assume that you will go all the way through reviewing differences in your worksheets, from the first to the last one. As soon as you are finished, Compare Two Sheets will automatically ask if you want to save your workbooks and switch to the Excel's normal view.

Note. As soon as you choose to Close review mode, Compare Two Sheets will save your books and cancel the Review Differences mode. You will not be able to undo your actions or get back to the penultimate state.

If you are absolutely sure that everything is fine, click OK.

Exit the mode without reviewing all the differences

If you decide to stop reviewing differences, use the Exit Review Differences mode button:
Exit Compare Two Sheets.

  • Pick the Save workbooks and keep difference marks option to get all the coloring made by the tool preserved in your sheets.
  • The Save workbooks and remove difference marks option lets you do exactly what its name suggests. All the changes you've made using Compare Two Sheets will be saved, and the remaining difference marks (i.e. all the remaining unreviewed differences) will get back to their initial formatting.
  • With the help of the Restore workbooks from the backup copies command, you will just get your books back to the condition in which they were before working with Compare Two Sheets.

If anything goes wrong

Say, you are working in the Review Differences mode. You insert a couple of rows or cells from one sheet to the other and save your sheets. And suddenly Excel crashes and closes your files.

If you try to open them after the crash, you will find out that the files are protected and you cannot make any changes there. To solve this issue, you can either use the backups that were created when you started using the Compare Two Sheets tool, or you can unprotect the original files.

To do this, go to the Review tab, find the Protect group and click Unprotect Sheet:
How to unprotect sheet in Excel.

Excel will ask you to enter the password. Here it is: ablebitscomparesheets
Enter the password to unprotect your sheet in Excel.

If you experience any other difficulties or issues with Compare Two Sheets, please contact us right away.

How to close your sheets

You can exit the Review Differences mode and close your sheets, using the standard Excel Close button:
Close your sheets.

If your sheets contain colored by the tool differences that you did not process with the toolbar, Compare Two Sheets will offer you four options:
Select the close option.

  • Save and close the workbook keeping the difference marks.
    Pick this option if you want to keep all the coloring made by the tool. The sheets will be closed, and the difference marks saved.
  • Close the workbook without saving. The difference marks will be kept, all your changes will be lost.
    This option makes sense if you have processed the difference marks with the toolbar (inserted rows from one sheet to another, copied cells, deleted difference marks or ignored them) but do not want to keep the result of your work. So, the coloring will be kept, but all the changes you have made with the toolbar will be lost.
  • Close the workbook and remove the difference marks.
    Select this option if you have processed some of the differences and you want to keep these changes, but at the same time, you want to remove other marks of the differences you have not processed.
  • Close the workbook and restore its contents from the backup copy.
    If you have made some changes in your sheets with the toolkit but do not want to keep them, select this option to get the initial version of your sheets.

Frequently Asked Questions

Find answers to the most frequently asked questions about Compare Two Sheets:

Responses

Omooba Olayemi says:
March 21, 2022 at 4:53 pm

What's the best function to use in Reconciliation of Bank Statement and General Ledger?
Sometimes need to add few rows to have a value in a row of General Ledger matched.
What's my best function please?

Hello Omooba,

Thank you for contacting us. For us to be able to help you better, please send us a small sample workbook with your source data and the result you expect to get to support@ablebits.com. I kindly ask you to shorten the tables to 10-20 rows.
Note! The result sheet is of great importance and often gives us a better understanding of your task rather than any text description. Please don't forget to include it.

We'll look into your task and see what option suits you best.

Saumitra Biswas says:
April 8, 2021 at 7:37 am

How to compare data with two different sheets one is in 104 data and one is in 106 . How to know which is missing

Rik de Zaaijer says:
December 23, 2020 at 3:00 pm

during comparing 2 worksheets, my PC froze. Therefore I had to restart my PC by which the worksheets are protected against change and I need to have a password which I do not have.
How can I remove this protection?

Hi Rik,

I am really sorry that you are having difficulties with our add-in. If the files were closed incorrectly after you made the changes to them with the add-in, they might get protected. To solve this issue, please unprotect the files via the standard Excel feature: Review -> Protect -> Unprotect Sheet. The password should be as follows: ablebitscomparesheets.

Please also see how to work in the Review Differences mode and how to exit it correctly to avoid the sheets being locked in this part of the user manual above.

If you have any other questions or need further assistance, feel free to email us at support@ablebits.com.

What is the maximum file size ablebits can compare?

Hello Aman,
Thank you for your question. Please note there are several points to consider:
1. The size of a file is not important since the add-in works with data ranges.
2. For 32-bit Excel, there is a limitation of 25 million cells to compare in total for all chosen data ranges.
3. No limitation for 64-bit Excel.
4. Worksheet and workbook limits depend on your operational system.
Don't hesitate to contact us again if you have any other questions or difficulties.

Good evening,
Please i don't have ablebit on the menubar how can go about it ?

I am using MS-excel 2019

Hello,

Thank you for contacting us. Please first make sure that the add-in is installed on your computer: go to Control Panel -> Programs and Features and check if you have the Ablebits add-in in the list of installed programs. If it is not there, please use this direct link to download and install Ultimate Suite on your computer.

If the add-in is installed, but you do not see the Ablebits tabs in your Excel Ribbon, please check out the solutions described on this troubleshooting page.

Good morning,
I am new to Ablebits, but I am having similar issues to some of the questions in the FAQ's above with 'Compare 2 Sheets'. I am trying the evaluation version to compare 2 sheets. The wizard says that the file has been opened from a network location but it is clearly on my own pc. What do I need to do differently?
Thanks,
Angel

Hi,
I am having a problem with compare two sheets. It says format is not supportedbut it is xlsx. What else can be the reason?
Thanks~

Hello, how can I select all the sheet 1 and sheet 2 columns during comparing 2 sheets at step 3/4 .
Let say if I have 300 matching columns, I have to tick them 1 by 1.

Hello Ayub,
Thank you for contacting us.

It looks like you should choose the "Cell by Cell" comparing mode on the 2nd step of the add-in. In this case, the tool will compare your tables by all the columns available.

If this isn't working the way you need, please give us some more details about your task and we'll try to find a better solution for you. Thank you.

Mike Sheridan says:
July 18, 2019 at 2:07 pm

Am trying the evaluation version to compare 2 sheets. The wizard says that the file has been opened from a network location but it is clearly on my own pc. what gives?

how can you compare 2 sheets to find matches, as opposed to differences?

Hi Natasha,
Thank you for the question. Please check out our Compare Two Tables tool, it lets you search for duplicate or unique values in your tables: https://www.ablebits.com/docs/excel-compare-tables/

Hi Irina, I need a help. I have data in two different spreadsheets one spreadsheet has an ID column with unique code the other one also had ID column which is NOT unique. I’m looking for a way on how can easily retrieve duplicate data in a separate column ( showing the number of instances an ID is duplicated)

Daniel Jensen says:
February 23, 2019 at 4:21 am

I am finding some needed features lacking i the 'Compare Two Sheets in Excel' add on. But it is hard to tell if they are disabled because it's a trial or if it just does not work.

Can you filter when in compare two sheets mode? Can you filter the sheets after using that feature if you save and re-open them? Why are the saved files locked? Also the forget changes and restore original files option does not seem to work. Auto save in Excel is off but it still saves the version edited by your program.

Can I just have a list of the things you disable in the trial mode?

Daniel

Hi Daniel,
Thank you for your feedback. First of all, please note that our trial version is fully-functional, so there are no features that are not available in the trial version.
Concerning your questions,
1. Can you filter when in compare two sheets mode?
No, you can’t filter when your sheets are in the review differences mode.
2. Can you filter the sheets after using that feature if you save and re-open them?
Yes, sure, you can use filter if you save the sheets, close them, and open again.
3. Why are the saved files locked?
Whether the files are locked or not, depends on the way you close the sheets, i.d. exit the review differences mode. If you edit one or both sheets, press Ctrl+S or click the Save button in Excel, and then close the sheets clicking the cross in the top right corner and selecting the first (Save and close the workbook keeping the differences marks) or the second (Close the workbook without saving. The difference marks will be kept, all your changes will be lost) option, your sheets will be locked. In this case, please follow these instructions: https://www.ablebits.com/docs/excel-compare-worksheets/#protect-sheets
4. Also the forget changes and restore original files option does not seem to work.
Please explain, why do you think this option does not work? And please clarify, when you select this option, do you close your sheets through the toolbar (https://www.ablebits.com/docs/excel-compare-worksheets/#exit-review-differences-mode) or clicking the Excel Close cross in the top right corner?
5. Auto save in Excel is off but it still saves the version edited by your program.
Again, I have to ask you for the details. What do you do before closing the files? Do you edit them anyhow and click the Save button or Ctrl+S? And how do you close the books: through the toolbar (https://www.ablebits.com/docs/excel-compare-worksheets/#exit-review-differences-mode) or clicking the Excel Close cross in the top right corner?

Thank you.

Susan Montgomery says:
February 22, 2019 at 4:48 pm

I am having trouble saving a save worksheet in excel to Ablebits. The goal is comparing commissions statements month by month

I did it once and had trouble saving the worksheet too. I know this can be easy but there is nothing in your help section.

Please help.

Hello Susan,
Thank you for using the tool and for your feedback. I will revise this help page and add info about saving the worksheets.
In the meantime, could you please describe in more detail what trouble do you have trying to save the sheets? What are you doing and what result are you getting?

Thank you.

Is there a way to extract only rows that have differences to a separate worksheet leaving me with a 'changes only' worksheet?

Hello, Misti,
Thank you for your interest in Compare Two Sheets and for the question.
Unfortunately, the tool does not let you extract anything from your sheets, it only colors the different cells/rows and lets you decide what to do with them using the toolbar in the review difference mode. So, as of now the only way to solve your task is to copy and paste the found rows with differences to a new sheet manually.
Thank you.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.