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.
Please read carefully this part before you start working with the add-in.
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.
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.
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.
We care about your books and always create their backup copies. By default, we store the backups for 14 days.
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 are not supported as we simply won't be able to mark differences in them :)
Worksheets with merged cells cannot be processed.
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.
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.
To run the add-in, on the Ablebits Data tab, in the Merge group, click Compare > Compare Two Sheets:
In the first window, you will see the list of limitations and requirements, please read them carefully:
Click the Agree button to continue.
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.
After selecting the sheets to be compared, click the Next button to proceed.
On this step, you choose how to compare your sheets and select the matching options.
Select the comparing mode
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.
To see how it works, watch the Compare sheets by key columns video.
Watch the Compare Excel files cell by cell video tutorial to see how to work with this 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:
Choose the appropriate comparing mode and click Next.
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:
By default, Compare Two Sheets searches for differences in values only, but you can select other options as well:
Select all the needed options and click Compare.
When you click the Compare button, Compare Two Sheets follows a certain algorithm. Here is the step-by-step description:
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.
The add-in processes your worksheets and opens them in the 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:
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.
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:
Please use the Paste values option instead if your task is to pull formula results.
There are several ways to exit the Review Differences mode and get back to your normal Excel view:
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.
If you are absolutely sure that everything is fine, click OK.
If you decide to stop reviewing differences, use the Exit Review Differences mode button:
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:
Excel will ask you to enter the password. Here it is: ablebitscomparesheets
If you experience any other difficulties or issues with Compare Two Sheets, please contact us right away.
You can exit the Review Differences mode and close your sheets, using the standard Excel Close button:
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:
Find answers to the most frequently asked questions about Compare Two Sheets:
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?
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 email@example.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.
How to compare data with two different sheets one is in 104 data and one is in 106 . How to know which is missing
Thank you for your question. If you use Compare Sheets, please remember that your worksheets should have the same layout and structure including grouping, filtering, and sorting.
Alternatively, please try out the Compare Tables tool. Hope our software will help!
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?
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 firstname.lastname@example.org.
What is the maximum file size ablebits can compare?
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.
Please i don't have ablebit on the menubar how can go about it ?
I am using MS-excel 2019
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.
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?
Thank you for your question. Please make a copy of your file and save it on your desktop, then open it from there and try to run the tool. Hope this will work for you.
If you have any other questions or need further assistance with our tool, feel free to contact us at email@example.com.
I am having a problem with compare two sheets. It says format is not supportedbut it is xlsx. What else can be the reason?
Sorry to hear that you are having difficulties with our add-in. Can you please send us the sheets you'd like to compare to firstname.lastname@example.org for testing? If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
We'll see what may be the cause of the issue.
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.
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.
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?
Thank you for contacting us.
Could you please open your Excel, go to File -> Open -> Browse, find the necessary file and make a screenshot of the Open window so that we could see the file's location and understand the problem you encountered better? Please send it to email@example.com. Thank you.
how can you compare 2 sheets to find matches, as opposed to differences?
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)
Thank you for contacting us.
Could you please send us a small sample workbook with your source data and the result you expect to get to firstname.lastname@example.org? We'll look into your task and see if our add-ins can help. Thank you.
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?
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?
Great question :-)
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.
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?
Is there a way to extract only rows that have differences to a separate worksheet leaving me with a 'changes only' worksheet?
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.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!