Do you need to detect changes or errors between two revisions of mission-critical Excel workbooks? Would it be awesome if you could do it without the need to keep opening the files? Would it be a game-changer if you would have a Power BI report online that will allow you to monitor the changes and errors and compare differences? Even better – would you want to run such a report on multiple Excel files, and customize it based on your auditing needs?

Compare data in Excel is a Power BI App that is available on Microsoft AppSource for free here. The app can answer all the needs above. It compares two Excel files of your choosing and detects differences and errors in cells across all the matching sheets. You can 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 detect the latest two files and track changes and errors.

The app includes the following features:

• Dynamically compares two Excel files in OneDrive for Business or SharePoint online.
• Interactive report pages to slice and dice changes in cells, errors, and missing or new sheets, rows, and columns.
• Cell comparison by row and column coordinates across the matching worksheets in the two Excel workbooks.
• Efficient detection of blank columns and rows.
• Blank rows and columns will not affect the comparison.
• Scheduled refreshes in Power BI service to track changes and errors automatically.

The app is free, but you get more in the paid version:

• The free version is limited to one-sheet comparisons. It will load the first sheet of the first file, and all the sheets of the second file. As a result, any additional sheets in the second file will be detected as new sheets that are missing in the first file. To lift the limits, you can subscribe to the full version. More information is below.
• You can get an annual subscription and get the Power BI report file (PBIX) to customize the app, and run it from Power BI desktop.

Instructions

Install the app from Microsoft AppSource here. A Power BI account is needed.

Once you install the app, you can find it on the Apps page on Power BI. Click on the Compare data in Excel 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.

First parameter: SharePoint Site or OneDriveBusiness

To run the app, you need to place two Excel files on your SharePoint site or OneDrive for Business. After you stored the Excel files, you will need to enter the root URL as the first parameter. You can follow the instructions here to get the URL. If you use 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.

Enter the URL you obtained above in the SharePoint Site or OneDriveBusiness parameter.

The next two parameters will help the app to narrow down all the files by folder and filename substrings. Then the remaining files will be sorted according to their alphabetical order, and the last two files will be loaded and compared by the app. This way, every time you refresh the app you can dynamically read the last two alphabetically sorted files.

Folder Substring and Filename Substring:

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.

Example:

Set your SharePoint/OneDrive Credentials

Set the Authentication method to Anonymous and Privacy level to Organizational. Click Sign in and continue.

In the second dialog box, set the Authentication method to OAuth2 and Privacy level to Organizational. Then click Sign in and connect. You will be asked to log in using your Microsoft account.

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 in 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 cell values (e.g. A–>B means that the cell value in Workbook 1 was A and it changed to B in Workbook 2). Cells that are highlighted in red are errors in formulas.

While detecting changes in the two Excel files, the app is smart enough to ignore blank/empty rows and columns, and 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 headers and 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 for 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.

To change one of the parameters (Email Subscription, SharePoint Site or OneDriveBusiness, Folder Substring, Filename Substring), go to the app’s workspace (You can find the workspace’s app in the Workspaces view). Click the ellipsis of the Compare Data in Excel dataset, and select Settings.

In the settings page, select the parameters you want to change. Then, refresh the dataset and the new parameters will be in effect.

How to lift the free version limits

To lift the limits, you can subscribe to the full version. Then, go to the workspace of the app and enter the subscription email as the Subscription Email parameter. Then, refresh the dataset.

Need more?

To gain more from the app, make sure you learn all about Power BI Template Apps features here. You can share the app with colleagues, make edits to its workspace and report online, and take benefit of the broader ecosystems of features offered by the Power BI service.

You can purchase an annual license to use the Power BI report file (pbix) of this app. Subscribe here.

For generic support and subscription FAQs, go here.