Comparing 2 Files On Excel Serial Numbers As They

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.

In addition, workbook comparison can help you spot potential problems like broken links, duplicate records, inconsistent formulas or wrong formatting.

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.

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.

The Compare Side by Side dialog box will appear, and you select the files to be displayed together with the active workbook.

As the result, you will have a difference report in a new worksheet.

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.

Comparing 2 Files On Excel Serial Numbers As They

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.

For this, click the upper left cell of the used range, usually A1, and press Ctrl Shift End to extend the selection to the last used cell.

However, these methods are not well suited for all-round comparison because of the following limitations.

As soon as you add or delete a row column in one sheet, all subsequent rows columns will be marked as differences.

It is especially useful when several users collaborate on the same Excel workbook because it lets you view the changes and comments of all users at a time.

To leverage this feature, be sure to do the following preparations.

Turning on the Track Changes feature shares the workbook automatically.

To add it to the Quick Access toolbar, perform the following steps.

To select several copies, hold the Shift key while clicking the file names, and then click OK.

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.

Please remember, the Compare and Merge Workbooks feature allows merging only copies of the same shared workbook.

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.

Below you will find a quick overview of a few tools that, in my opinion, are best performers in this area.

Also, you have a couple of managers in direct contact with participants and the database, and as a result, you have 2 versions of the same Excel file.

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.

Comparing 2 Files On Excel For Mac