How to Compare Two Sheets in Excel

The Compare Sheets tool 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.

Please note, the add-in is compatible only with Excel 2016 and 2013. Excel 2010 and 2007 are not supported.

Video: How to compare Excel sheets

Before you start

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

Microsoft Excel 2016 - 2013

The add-in is compatible only with MS Excel 2016 and 2013. MS Excel 2010 and 2007 are not supported.

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.

Save all your changes

Before running Compare 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 Sheets marks differences with a background color, bottom border, or font color replacing your coloring, fonts, or cell borders. But you won't lose them forever: as you review the differences, the tool automatically restores your original formatting.

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 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 the Compare Sheets wizard

Start Compare Sheets

To run the tool, click the Compare Sheets button on the Ablebits Data tab in the Merge group:

Start Compare Sheets.

In the first window you will see limitations and requirements, please read them carefully.

Click the Agree button.

Step 1: Select your worksheets and ranges

Choose the sheets to compare.

In the list of open books, choose the sheets you are going to compare. If the needed workbook is not in this list, open it using the Open workbook button.

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.

Click the Next button.

Step 2: Specify the comparing mode

On this step, you choose the comparing mode and matching option.

Select the necessary comparing 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.
  • 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.
  • Cell-by-cell
    The third type is designed for sheets with the same layout and size, like balance sheets or year-to-year reports. Use the Cell-by-cell comparing mode for sheets of this type.

Indicate your matching option

Checking line by line, Compare 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 Sheets provides three matching options:

  • First match
    If you select First match for AAA row in Sheet 1, Compare 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 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 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.

Choose the appropriate comparing mode and click Next.

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

If you chose the By key columns comparing mode, Compare 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 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, untick this checkbox and they will be included into the comparison.
  • Add a status column
    If you pick this option, Compare Sheets will add a new column to your sheets and mark the found differences there. It will not only make the result of comparison more understandable but will also let you manage differences comfortably.
  • Mark rows with differences with the status column only
    You can choose not to do anything with your cells, but instead, mark all the differences in a special column. Tick this option and the differences will be marked only in the status column. Your tables will remain intact.
  • 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.

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 the 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 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 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 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 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 to turn this option on until you get comfortable with the Compare 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 that Paste all, as only for this option Excel gives an opportunity not to use the clipboard. For all the other options, Compare 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 Sheets will automatically ask if you want to save your workbooks and switch to the Excel's normal view.

Tip. Do not accept the default action in a rush. Take a deep breath and make sure everything has been done correctly. As soon as you choose to Close review mode, Compare Sheets will save your books and cancel the Review Differences mode. You will lose the opportunity to undo your actions or at least get back to the penultimate state.

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

Exit the mode without reviewing all the differences

If you are sick and tired of reviewing differences or just decide to go another way, use the Exit Review Differences mode button:
Exit Compare Sheets.

  • The Save workbooks and remove difference marks option lets you do exactly what its name says. All the changes you've made using Compare 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 Sheets.

If something goes wrong

Say, you are working in the Review Differences mode. You insert a couple of rows or cells from one sheet to another 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 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

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

Related video tutorials

The following video tutorials give a quick guidance on how to use the three comparing modes of the Compare Two Sheets tool:

Responses

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

Reply

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.

Reply

Ask a question

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.