Identifying the genuine differences between spreadsheets is a time-consuming task and one that is susceptible to human error. A methodical visual inspection of a large, complex spreadsheet is impractical while automated tools often flag inconsequential differences which in turn need to be manually inspected.

In this article, we discuss why users typically need to uncover differences between spreadsheets and take a closer look at your spreadsheet comparison options, including the best way to quickly identify genuine differences between spreadsheets.

What’s the purpose of a spreadsheet compare?

For a variety of reasons, spreadsheets change over time, including changes in inputs and in the formulas that calculate the results. Users of Office 365 can track who changed a sheet, but there is no automated way to track what those changes are. Typical rationales for comparing spreadsheets include:
• Many contributors. Complex sheets are handled by multiple team members and handled frequently. Determining who changed what in the absence of an automated changelog can be tedious if no efficient procedures are in place to make comparisons between different version of a workbook.

• Constructing a workbook. Building a large, complex spreadsheet involves many, frequent changes which can easily lead to errors creeping in over time. An effective, time-efficient method of comparing workbooks can help identify glitches and speed up the process of building a version history if none was kept.

• Auditing procedures. When audits flag concerns, comparison tools can help identify which changes caused an error to creep in, or what it is that led to shifts in outputs over time. Effective spreadsheet comparison tools also assist where auditing complex models involve the parallel reconstruction of workbooks by finding the source of discrepancies in results.

• Linked workbooks. It is common for a spreadsheet to use cell references that link to a different file. If the layout of the linked file changes the link will be pointing to a completely different cell. break. The OAK alignment mechanism will prevent this from happening by inserting rows on both files to match the contents and therefore the corresponding links. It is then possible to change the linked file without causing any errors. beak. Comparison tools can help determine where these changes are so that formula links can be fixed while making it easier to align similar models so that a spreadsheet that links to one model can easily be adapted to link to the second.

Cleary, performing spreadsheet comparisons is anything but a niche requirement, yet the process for doing so is not exactly user-friendly, particularly in the absence of a third-party tool.

Finding differences in spreadsheets is not straightforward

Before we look at the available tools we’ll discuss why finding genuine differences in a spreadsheet is so difficult as this clarifies why some methods of comparing spreadsheets do not work as well as they appear to at first glance.

Spreadsheet compare and complexity

The complexity of some workbooks simply precludes unassisted methods of comparing spreadsheets. Where a methodical comparison may be practical the risks involved in solely relying on fallible human visual inspection are simply too high, especially where large, complex spreadsheets model financial transactions.

Yes, you may opt to have a process of double or triple checking, involving several analysts but the time spent comparing sheets can be truly prohibitive, often involving highly paid staff. For many spreadsheet comparisons an automated tool is the only realistic option.

Hitches with comparison tools

Tools that compare spreadsheets come in all shapes and forms, including the Microsoft Spreadsheet Compare tool and a range of third-party add-ins such as the Operis Analysis Kit, or OAK. There are two obvious benefits to comparison tools:
1. comparison tools can scan thousands of cells instantly, potentially saving vast amounts of time
2. comparison tools are not error-prone and should perform consistently if a tool finds no differences, chances are that there are none to be found
The drawback of many tools lies here: spreadsheet comparison tools will often flag inconsequential differences that are the result of simple changes in layout such as extra rows and columns. In fact, a minor change in layout can render a comparison tool useless.
A visual comparison can compensate for these changes, but ideally, your comparison tool should offer a way to compensate for changes in spreadsheet structure.
What options do you have when comparing sheets?

As always, finding the right tool for the task is the first step in performing spreadsheet comparisons. For simple workbooks, comparison tools may indeed be completely unnecessary. Here are your options.

Running a manual spreadsheet compare

Manual comparison can work, to a degree. Consider trying one or a combination of the following methods:
• tap Arrange All in the Excel ribbon, and select Vertical or Horizontal arrangement for a side-by side view
• turn on Synchronous Scrolling
• use formulas on an extra sheet to automatically report changed cells
• combine conditional formatting with formulas to make the visual inspection process easier

You will need to manually examine formulas, but this type of visual inspection could work for simple workbooks or where you simply want to get to grips with the scope of the challenge.
Nonetheless, the drawbacks of manual inspection remain, it is easy to make a mistake. Besides, if the second spreadsheet differs significantly in layout any formulas you use will report inconsequential differences.

Excel Spreadsheet Compare

If you don’t want to rely on manual, visual inspection consider Microsoft’s Spreadsheet Compare tool. Microsoft’s tool compares not only the differences in values in cells, it can also analyse a limited number of structural differences, including distinguishing between cells containing formulas and cells which contain text or constants.

That said, Spreadsheet Compare provides a relatively limited feature set without much flexibility. In particular, you will find it difficult to compare spreadsheets that have structural differences unless you first manually adjust the spreadsheets to match. Failing to do so mean that Spreadsheet Compare flags many changes that are not real changes. The Spreadsheet Compare is only available with Office Professional Plus 2013 or Office 365 ProPlus.

Third-party tools

This brings us to third-party tools. There are numerous tools available to you, ranging from the informal and more or less undocumented to paid tools from experienced developers. Some of these are a good fit for narrow use cases and go a bit beyond what Spreadsheet Compare can offer.

More advanced tools duplicate the abilities of Microsoft’s Spreadsheet Compare on a more intelligent level by, for example, grouping a repeated formula into one difference flag rather than flagging the formula as changed in every instance that it is used.

However, most tools are tripped up by changes in workbook structure and layout, again requiring that you manually align spreadsheets before you start making a comparison if you want to make sure only real differences are detected.

How OAK makes finding real differences simple

The Operis Analysis Toolkit (OAK) was developed by Operis out of an internal need to make it simple to find real differences in spreadsheets. The toolkit includes OAK Compare, a flexible tool that can swiftly compare ranges, worksheets in a workbook or entire workbooks.

OAK performs some of the same functions as other third-party tools and indeed Microsoft Spreadsheet Compare, but OAK arguably presents the results in a more usable format and offers more flexibility. One unique feature of OAK is its ability to easily find real differences where spreadsheets have undergone structural changes.
OAK Compare’s alignment feature

What sets OAK apart from other spreadsheet comparison tools is its ability to assist the user in aligning two workbooks so that a comparison can be made without flagging hundreds of cell discrepancies when it is in fact merely the placement of data or formulas that has shifted.

OAK’s Align Row and Align Column tools attempt to automatically align spreadsheets by inserting or deleting rows and columns before a comparison is started. Though OAK’s automatic alignment has restrictions it generally performs well where workbooks are tidily structured and where these are consistent in their use of designated columns for descriptive text.
It also offers users the ability to customise how OAK performs alignment, via the Advanced Alignment tool. Here a user can specify that rows and columns are aligned using data that differs from the data that is being compared. For example, you can set OAK to align by values even if OAK Compare is set to compare formulas.

Finally, OAK Compare also features a Remove Compare Modifications tool which reverses the changes made in the effort to create a sensible basis for comparison. With a single click you can remove the rows and columns OAK inserted to restore your workbook to its original condition.

Which is the best tool for spreadsheet comparison?

It really depends on your individual use case. If you typically work with spreadsheets consisting out of lists where the overall structure is simple and unlikely to change, Microsoft’s Spreadsheet Compare tool may suffice even if it is not the most elegant solution. More complex spreadsheets will require some assistance if you want to avoid hours of manually scrutinising either the spreadsheet itself or endless redundant difference flags.

If you have more complex requirements you may want to test out the free trial of the Operis Analysis Toolkit. For 30 days you can enjoy the full functionality of OAK, including its sophisticated comparison tool, OAK Compare.

Any questions? Just contact the OAK team.