How to Compare Two Sheets in Excel

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.

Start Compare Two Sheets

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:

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:

Click the Agree button to continue.

Step 1: Select your worksheets and ranges

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.

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

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.

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

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:

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:

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:

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

What is the Review Differences mode

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:

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

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:

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.

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

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:

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:

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.