Spreadsheet Compare Download Mac ##TOP##
Lynda Durnil
If it helps: I'm on a Dell Latitude running Windows 10 and using Office 365. I believe my IT attempted the (presumably outdated) solutions here: -US/07cfb1db-3596-4010-99df-274d330d99b8/excel-2016-spreadsheet-compare-crashes-on-startup?forum=Office2016ITPro
When I have used this function, the dialog box contains a list of differences with a basic description of their cell location and the differing values. Clicking an entry in the list highlights the cell in the spreadsheet where the difference occurred.
Spreadsheet Compare has been part of Office Professional Plus since version 2013 and is also shipped with Office 365 ProPlus. Spreadsheet Compare is a stand-alone desktop application that lets us compare two Excel workbooks and create a report of the differences.
Make sure the above path to Spreadsheet Compare is correct for your Office installation. Instead of specifying the full path, you could also replace it with spreadsheetcompare and configure your PATH environment variables so it points to the correct folder.
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.
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.
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.
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.
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.
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.
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.
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.
In this tutorial, you will learn a variety of methods to compare Excel files and identify differences between them. See how to open two Excel windows side by side, how to use Excel formulas to create a difference report, highlight differences with conditional formatting, and more.
When you have two similar Excel workbooks, or better say two versions of the same workbook, what's the first thing you usually want to do with them? Right, compare those files for differences, and then probably merge them into a single file. In addition, workbook comparison can help you spot potential problems like broken links, duplicate records, inconsistent formulas or wrong formatting.
If you have relatively small workbooks and a sharp eye for detail, this quick and easy way to compare Excel files might work well for you. I am talking about View Side by Side mode that lets you arrange two Excel windows side by side. You can use this method to visually compare two workbooks or two sheets in the same workbook.
If you want to scroll through both worksheets simultaneously to compare data row-by-row, make sure the Synchronous Scrolling option it turned on. This option resides on the View tab, in the Window group, right under the View Side by Side button, and is usually turned on automatically as soon as you activate View Side by Side mode.For more information about using this Excel feature, please see View Excel workbooks side by side.
Arrange multiple Excel windows side by sideTo view more than 2 Excel files at a time, open all the workbooks you want to compare, and click the View Side by Side button. The Compare Side by Side dialog box will appear, and you select the files to be displayed together with the active workbook.To view all open Excel files at a time, click the Arrange All button on the View tab, in the Window group, and choose your preferred arrangement: tiled, horizontal, vertical or cascade.
Compare two sheets in same workbookSometimes, 2 sheets that you want to compare reside in the same workbook. To view them side by side, perform the following steps.
Due to the we use of relative cell references, the formula will change based on a relative position of the column and row. As the result, the formula in A1 will compare cell A1 in Sheet1 and Sheet2, the formula in B1 will compare cell B1 in both sheets, and so on. The result will look similar to this:As you can see in the above screenshot, the formula compares 2 sheets, identifies cells with deferent values and displays the differences in corresponding cells. Please note that in the difference report, dates (cell C4) are presented by serial numbers as they are stored in the internal Excel system, which is not very convenient for analyzing differences between them.
Highlight differences between 2 sheets with conditional formattingTo highlight cells that have different values in two sheets with the color of your choosing, use the Excel conditional formatting feature:
As you see, it's very easy to compare two Excel sheets by using formulas or conditional formats. However, these methods are not well suited for all-round comparison because of the following limitations:
To point out the row and columns with differences, Excel highlights the column letters and row numbers in a dark red color. At the cell level, edits from different users are marked with different colors. To see who made a specific change, just hover over the cell.Note. If the Compare and Merge Workbooks command is greyed out in your Excel, most likely you are trying to combine different Excel files. Please remember, the Compare and Merge Workbooks feature allows merging only copies of the same shared workbook.Third-party tools to compare Excel filesAs you have just seen, 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 values, formulas or formatting.
So, if you need advanced and really 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, updating and merging Excel sheets and workbooks. Below you will find a quick overview of a few tools that, in my opinion, are best performers in this area.
Also, you can select worksheets manually or instruct the add-in to match sheets by other criteria, for example by worksheet type - all, protected, or hidden.Once you've selected the sheets, the Synkronizer add-in will open them side by side, arranged vertically or horizontally, like in Excel's View Side by Side mode.
- Select one of the following comparison options:
- Compare as normal worksheets - the default option that works in most cases.
- Compare with link options - if the selected sheets do not contain any new or deleted rows and columns, you can compare them "1 on 1".
- Compare as database - recommended for comparing sheets that have a database structure.
- Compare selected ranges - if you do not want to compare the entire sheets, define the ranges to be compared.
- Choose the content types to be compared (optional).On the Select tab, in the Compare group, you can choose the content type(s) relevant to your current task:
- Under Content, you can select comments and names (in addition to cell values, formulas and calculated values that are compared by default).
- Under Formats, you choose what cell formats such as alignment, fill, font, border etc.
- The Filters option lets you filter out differences that you don't want to display. For example, you can ignore case, leading or trailing spaces, all formulas or different formulas with the same result, hidden rows or column, and more.
- Finally, click the big red Start button on the ribbon and proceed to examining the results.