How to compare two Excel sheets for differences 2 Feb 2022
Compare two Excel files quickly and easily
Identifying the genuine differences between spreadsheets is a time-consuming task and one that is susceptible to human error. In our previous blog we laid out the manual process of how to compare two excel sheets for differences using alignment, however, this is an arduous task and often inconvenient for those who are time-poor.
Why comparing spreadsheets is necessary?
Even for the most organized book and record keepers, the sheer volume of information you store in Excel can soon become overwhelming. Alternatively, When working on a project with colleagues You create an Excel spreadsheet that you share with your co-worker. They edit the document, send it back to you, and you are left scanning through a long list of entries to try to determine what changes they made.
Microsoft Excel provides a handful of features to compare data in two or more workbooks. But none of the built-in options is sufficient to comprehensively compare Excel sheets, let alone entire workbooks, spotting all the differences in structure (rows and columns) values , formulas, or formatting.
If you need an advanced and efficient means to compare two Excel files, then most likely you would have to use one of the third-party tools specially designed for comparing, Excel sheets and workbooks. This is where Excel add-in OAK comes in! OAK has tools for comparing spreadsheet data, which can come in very handy if you’re trying to find a cell typo, comparing documents to prepare for an audit, or relinking a revised model to an existing workbook.
Compare Worksheets/Workbooks tool
The Compare Workbooks/ Worksheets tool can determine the differences between two worksheets or two workbooks. This comparison is typically completed within seconds, saving a significant amount of time and effort. The same comparison can also be repeated by somebody else. The Compare Workbooks/ Worksheets command links together several different functions. At the simplest level, it allows you to align two worksheets that are similar in overall vertical/horizontal layout.
At its most exhaustive, it performs a worksheet-by-worksheet comparison of the contents (formulas or values) of all the cells in two workbooks. It finds the appropriate pair of worksheets to compare, aligns them, compares them cell by cell, highlights their differences as well as the differences in the text, and produces an output report detailing these differences.
Three of the options making up this command (Highlight Differences On Worksheet; Align Rows; and Align Columns) will alter your worksheets. Any of the changes made by the OAK Compare tool can be reverted at any time (even after saving the files) by using the Compare / Unmodify tool.
Why would you want to use it?
This command is useful whenever you need to find the differences between two workbooks or two worksheets. For financial modelling and auditing this tool enables you to receive new versions of a model with your iterations still being valid as you can use the align tool to realign your work. Saving you from having to reword the model.
By running the OAK compare report in the first instance you can see exactly how many changes have been made to the workbook, allowing you a better understanding of how much time it will take you to address those updates. This quick report also allows you to easily see if there have been any changes made in error, allowing you to quickly correct them.
In each of these cases, Compare Workbooks/ Worksheets provides a quick and powerful method for performing the comparison between the two relevant worksheets or workbooks.
