To clearly see the difference between two Excel files, let the Compare Two Workbooks tool find worksheets with the same name there, analyze the ranges they contain, and show the content that varies using the colors of your choice.
Before you start
Consider the requirements and limitations the add-in has.
- Protected and unsaved workbooks cannot be processed.
- The layout and structure of the worksheets that are going to be compared should be identical.
- Data should be filtered, grouped, and sorted similarly.
- Only XLSX and XLSM files are processed.
- The colors you choose for marking differences should not coincide with your original formatting.
- The difference in the height of rows, the width of columns, the sheets options and embedded objects will not be shown.
How to work with Compare Two Workbooks
The Compare Two Workbooks icon is in the Compare drop-down menu in the Merge group on the Ablebits Data tab:
After you click it, the add-in will show the list of requirements and limitations it has:
If you do not mind them and want to proceed, click Agree.
Note. To stop the add-in from displaying the Requirements and Limitations dialog window each time you click the Compare Two Workbooks icon, opt for Do not show this next time:
Step 1: Check off the workbooks (and worksheets) to be compared
Let the add-in know which two of your open Excel files to compare by picking them in the list:
To add a file to the list, click the Open Workbook button.
To make it possible for the add-in to process shared files from OneDrive, you should sync
and open them on your local device.
On selecting the necessary workbooks (and worksheets), click Next.
Step 2: Decide what comparison options you prefer
- Show differences in formulas
Pick this option if you want the add-in to compare formulas that the workbooks of interest contain.
- Show differences in formatting
If you are interested in seeing formatting differences, opt for Show differences in formatting.
- Ignore hidden rows/columns
Check off this option to exclude the content of hidden rows and/or columns from the results.
- Ignore empty rows/columns
To prevent the tool from considering empty rows and/or columns, pick Ignore empty rows/columns.
- Ignore leading/trailing spaces
Select this option to let the add-in omit leading and/or trailing spaces just in case your cells contain them.
Decide how the tool should demonstrate the differences it will find:
- Background color
- Bottom border
- Font color
Pick the colors the tool should use for differentiating Rows that exist only in Book1, Rows that exist only in Book2, and Cells different in Book1 and Book2.
After selecting the options that suit your task, click Compare.