Excel Compare and Merge Add-in

Save hours comparing sheets, ranges, tables, and lists. Analyze cell-by-cell differences; find added & removed rows.

Comment

Inspiration

The primary inspiration was to provide an app that helps users quickly compare and analyze information, particularly data that changes over time and where items (rows) are changed, added, and/or removed. Integration with Excel and the (new) Add-in model would provide the user with a natural extension to their analysis toolbox, quickly accessible via the Excel Task Pane. Excel integration also provides access to the wide variety of data that resides or surfaces into Excel via external data connections, .csv files, copy/paste etc. The app would provide advanced capabilities for power users yet be simple enough for casual Excel users, those who may not be comfortable with complex Excel formulas and formatting.

What it does

The Add-in compares two Excel ranges, known as the Left and Right ranges, and places the compare results as a new table in the workbook with detailed cell-by-cell differences using colors, filters, and additional columns for analysis. The Match Rows compare type allows the user to indicate how to find similar or matching rows by selecting columns with unique values that identify a row such Employee Id or Product Id (key columns in database terms). This allows the compare process to first find matching rows and then compare the cells. It also allows the user to find added, removed, and/or duplicate rows. The user can optionally use the Task Pane to merge the differences found into a new "up-to-date" range. The user can also save the Compare to the workbook, making it available to run/edit at a later time.

The most powerful feature of the Add-in is Select Methods which provide innovative ways to get data into the Left and Right ranges, many of which will automatically find the data to compare. The user can simply select sheets, ranges, or tables to compare or they can use one of the advanced Select Methods such as Filtered Table or Current Date to Previous. The former allows comparing data in the same Excel table with different column filters applied to the Left and Right compare ranges. The latter will automatically find compare ranges based on sheet names containing a date or date part such as "May 2016" or "Sales Q1 2017", a common occurrence in Excel workbooks.

How I built it

The Office Add-in model is built around web technologies and is also well suited for client technologies and single page apps. Therefore, the Add-in was developed as a SPA using TypeScript, Angular 1.x, Bootstrap, Microsoft Office JS, and some Office UI Fabric styling. Various other third party libraries were used such as jQuery, MomentJs, and Font-Awesome. Tooling included Visual Studio 2015, Node.js, Gulp, and SASS. Custom font icons were created using a combination of Gimp, Inkscape, and IcoMoon.

The Add-in is part of a larger infrastructure containing a web site that serves multiple add-ins and is hosted in Azure. The solution started from the Visual Studio Office Add-in template provided by Microsoft but was heavily modified to support 1) Angular SPA add-in's and associated tooling for build and deployment, 2) multiple add-ins being served from a single web project, 3) a common infrastructure to implement the Office license framework to enable the Add-in to be deployed from the Office Store.

Challenges I ran into

Probably the biggest challenge was providing a good user experience in the small surface area of the Task Pane. Although I'm not sure this was fully achieved, I relied heavily on the Bootstrap grid system and some custom responsive breakpoints and CSS logic. In the Excel client, for example, the user can resize or even pop-out the Task Pane and the Add-in will respond by displaying a larger font and laying out UI controls more efficiently to utilize the large viewport. Other techniques were also implemented to minimize task pane scrolling such as allowing the user to hide/scroll the menu and providing paging and a single column view in the compare results in the Task Pane.

Another challenge was cross-client/platform support and implementing code to account for some differences among Windows client, Mac client, and Excel Online.

Accomplishments that I'm proud of

Generally, implementing the Add-in model as an Angular SPA using prevailing (at least then) web technologies. Efficient use of the Task Pane area.

What I learned

Increased depth in knowledge of Angular, Node.js and related technologies, the Office Add-in framework, and Office.js.

What's next for Excel Compare and Merge Add-in

Implement new features from the backlog and continually improve the user experience, including exploring more options for using Office UI Fabric with an Angular app.

Share this project:

Updates