Excel CompareXLS Add-In

CompareXLS is an Excel Add-in that allows comparing workbook properties, worksheet properties and content, and datasets (columns, groups of cells, etc) in Excel. This Excel Add-in prompts three dialogs or user forms to choose two workbooks, two worksheets, or two datasets, respectively. The two worksheets or datasets can both belong to the same workbook or separate workbooks. Other options allow showing properties of the current workbook, worksheet, or selected range, as well as listing all worksheets in the workbook, sorting sheets alphabetically, or un-hiding all worksheets.

Workbooks

Comparing workbooks is all about comparing properties of the file or workbook (e.g. name, size), structure and content at a high level. Comparing two workbooks often refers (probably mistakenly) to comparing two worksheets in two different workbooks (see next Worksheets).

Workbook Summary shows the main properties of the current Excel file or workbook. These include the following:

• Name of the file (with extension)
• Local drive path where the file is stored
• Author of the workbook
• Date when it was created
• Date when it was last updated
• Name of user who last modified the file
• File size (in KB)
• Workbook structure protection, encryption with password, and read only status are indicated when positive
• Number of sheets
• Number of cells with data (showing number of formulas, errors, validations, and conditional formatting, if any) in the entire workbook
• Number of defined names in the workbook
• Number of comments across the entire workbook
• Number of charts (both Chart sheets and embedded charts)
• Number of pivot tables in the entire workbook
• Number of hyperlinks in the entire workbook
• Number of autoshapes, text boxes, and form controls

Compare Workbooks prompts a user form to choose the two workbooks we want to compare. The drop-down shows all open workbooks to choose from. Alternatively, we can trigger a dialog to pick and open another workbook. We can show or export details for each of the workbooks individually, and show or export the comparison details of the two workbooks side-by-side. Additionally, we can highlight sheets with different names in both workbooks.

 

Worksheets

Comparing two worksheets refers to comparing certain properties of the worksheets (e.g. name, visibility, protection) as well as cell-to-cell comparison between the two worksheets. The two worksheets may be in two different workbooks altogether, thus often being referred to (probably incorrectly) as comparing two workbooks.

Worksheet Summary shows properties and other information of the current Excel worksheet including the following:

• Name of the parent workbook
• Name of the worksheet
• Visibility of the worksheet (visible or hidden, including very hidden)
• Protection status (protected or not)
• Used range in that worksheet
• Number of cells with data (showing number of formulas, errors, validations, and conditional formatting, if any)
• Number of comments in that worksheet
• Number of embedded charts in that worksheet
• Number of pivot tables in that worksheet
• Number of hyperlinks in that worksheet
• Number of autoshapes, text boxes, and form controls

Compare Worksheets prompts a dialog form to choose the two worksheets we want to compare. We can show or export details for each of the worksheets individually, and show or export the comparison of the two worksheets side-by-side. Further options allow comparing cell-to-cell values, formulas, and formatting between the two selected worksheets. In this case, we can choose to select the differences within the worksheet, highlight the differences changing the background color (to yellow), and exporting the differences to a new workbook.

Additionally, we can also get the list of all worksheets, sort the worksheets alphabetically, and un-hide all the worksheets in that workbook.

Datasets

A dataset is a range or group of cells, or a column or row, containing the data.

Dataset Summary shows certain information about the selected range or dataset including the following:

• Name of the parent workbook
• Name of the parent worksheet
• Range address of the dataset
• Number of cells with data (showing number of formulas, errors, validations, and conditional formatting, if any)
• Sum of the numerical values (formulas not included and errors prompt N/A)
• Average of the numerical values (errors prompt N/A)

Compare Datasets prompts a dialog form to choose the two datasets we want to compare. The form shows some basic information upon selecting the dataset. The output shows the different values between one dataset (Dataset1) vs the other (Dataset2), regardless of the position in the range/worksheet. We can choose to select, highlight (yellow background), and export those differences.

How to use the Add-in

As any Excel Add-in, we just need to download the add-in (follow the link below), save it to your preferred folder, and then go to the developer tab -> Excel Add-Ins -> Browse (and locate the file) -> add the file and tick the box. If you don’t have the developer tab, you can easily add it going to File -> Options -> Customize Ribbon -> and tick the box for Developer on the right hand side of the dialog window. If you do not want to add the developer tab, you can still add the add-in through File -> Options -> Add-Ins -> Manage Excel Add-Ins at the bottom -> Browse (and locate the file) -> add the file and tick the box.

Once added, you will see a new tab in the ribbon of all Excel files. The new tab has all the options described above and a link to this article and Excel add-ins in this blog.

Please feel free to share any feedback in the comments below. I would really appreciate to get the heads up if it works well and you find it useful.

Click Here to download Excel CompareXLS Add-In.