Do you need to detect changes or errors between two versions of mission-critical Excel workbooks?

The Excel Compare Tool (available on AppSource here) is a Power BI app that compares two Excel files of your choosing and detects differences and errors in cells across all the matching tabs. You can also track changes and automatically audit your spreadsheets. Store the revisions of an Excel workbook on a SharePoint site or OneDrive for Business folder, and the app can automatically import the latest two workbooks to track new changes and errors.

The app includes the following features:

• Interactive report pages to slice and dice value changes, errors and grid data
• Detection of changes in values
• Detection of Excel errors
• Detection of new or missing worksheets
• Cell comparison by row and column coordinates across matching worksheets
• Detection of blank columns and rows
• Blank rows and columns will not affect the comparison
• Set scheduled refresh to track changes and errors automatically

How to Set Up Excel Compare

Once you install the app from here, you can find it on the Apps page on Power BI. Click on the Excel Compare tile to open it.

Once the app is open, you will see a yellow pop-up on the top of your screen. Click on Connect your data to connect the app to your workbooks.

A window will pop up asking you to fill in the parameters. The app requires an initial two full path filenames of existing Excel workbooks on your SharePoint site or OneDrive for Business. The full path is represented by a special URL that you can find by following the simple instructions here.

Note: the app allows you to import either two specified files or to dynamically load the latest two files from a folder. Even if you prefer the dynamic method, you still need to explicitly define two full path filenames to refresh the data on the Power BI service. That means that for the dynamic option you can set two old revisions for the full path filenames, and when you have newer revisions of the workbook as new files in the same folder, these new files will be loaded instead of the first two. The dynamic load filters all subfolders according to the Folder Substring parameter and then filters all file names according to the Filename Substring parameter. Then all files are sorted according to their alphabetical order, and the last two files will be loaded and be compared.

Start by copying the file path names of the two Excel files. Paste the URL of workbook 1 under Filename 1, then the URL of the second workbook under Filename 2. (Don’t forget to get the URLs by following the instructions here).

Scroll down to fill in the rest of the parameters. Under SharePoint Site, copy the URL for the SharePoint site or OneDrive for Business where the workbooks are stored.

If you have the workbooks on a SharePoint site, here is the syntax of the URL you would need to use (Replace the brackets with your SharePoint site information):

https://[tenant].sharepoint.com/sites/[site name]

If you use OneDrive for Business, open the desired folder in your browser. Copy the prefix of the URL in the address bar before “/_layouts”, as demonstrated in this screenshot. This prefix is the value you would need to use as the parameter SharePoint Site.

Under Folder Substring, write a substring that can uniquely represent the folder where the two files can be found. Similarly, scroll down to find Filename Substring, and write down the substring of the workbooks’ filenames that you would want to load. All these parameters will allow the app to locate the two latest revisions of the Excel workbooks and load them if you set the final parameter Dynamic Load as TRUE.

The latest two workbooks will only load when Dynamic Load is set to TRUE. If you intend to load the latest two revisions of the file automatically, set it to TRUE. If you intend to statically load the specific two filenames (Filename 1 and Filename 2), set Dynamic Load to FALSE. Once you are done, click Next.

Set your SharePoint/OneDrive Credentials

Now, set the Authentication method to OAuth2 and Privacy level to Organizational. Then click Sign in and connect and enter your credentials to log in.

You will need to repeat this step three times — One for each of the data sources (Filename 1, Filename 2, and SharePoint site).

You will now see the Refresh is now in progress notification. Once the refresh completes, you will have an up-to-date comparison of the workbook revisions through the app pages.

You can explore the various pages on the left sidebar, including Main, Workbook 1, Workbook 2, and Search. The first page, Main, displays a summary of the comparison including all the changes and errors in the two workbooks.

In the top right corner, you can use the slicer to select between Changes Only, Workbook 1, or Workbook 2. The first option will show only the changes and errors in the main matrix visual (Excel-like grid). The second and third options will show all the data by the corresponding workbook, including the changes and errors. The filters above allow you to filter by worksheet, row, and column.

The cells highlighted in yellow represent changes in values (cell value in workbook 1 –> cell value in workbook 2), while those highlighted in red are errors.

While detecting changes in the two Excel files, the app ignores blank/empty rows or columns, and they will not impact the detection of changes. However, the app does detect offsets made by blank columns or blank rows, and you can find these offsets by finding the arrow (–>) sign in the main matrix by looking at the column names and the values of the Row column.

For example, the column “E –> D” in the screenshot below can tell you that in Workbook 1 you had a blank column that was removed in Workbook 2, and as a result column E of workbook 1 is the same as column D in workbook 2. The same logic is applied to blank rows. The value “6 –> 5” tells you that in Workbook 1 there is a blank row that was removed in Workbook 2, and as a result, row 6 in Workbook 1 is the same row as row 5 in Workbook 2. In the report pages Workbook 1 and Workbook 2, you can learn more about blank columns and rows.

The second page, Workbook 1, displays the errors and changes of the first workbook. You will be able to see which blank columns or rows are missing in the workbook, search a specific value in the cells of this workbook, or use filters to zoom in on specific changes and errors.

Similarly, the Workbook 2 page displays the errors and changes of the second workbook, displays missing blank columns and rows, and presents filters to zoom in on specific changes and errors.

The final page, Search, allows you to search for specific values in cells across both workbooks.

This solution can be used in conjunction with the DataChant data quality apps here and here to build a low-code data quality automation or for large-scale Excel auditing projects. Contact us if you have any questions about this report.