I needed to compare two Excel workbooks produced with Microsoft Excel for
Mac (version 16.29) on my
MacBook
Pro laptop. Unfortunately, the MAC version of Excel doesn't include a
capability to directly compare two workbooks. Since both workbooks only had one
worksheet in them, I created a new workbook and then copied the
contents of the worksheet in the first workbook to Sheet1 in the
new workbook and the contents of the worksheet in the second workbook
to Sheet2 in the new workbook. I copied the contents of the
worksheets by selecting Edit and then Select All
in a worksheet and then pasting the contents into a sheet in the
new workbook. I then created a third worksheet, Sheet3 in the new
workbook. In cell A1 in that workbook, I put the formula
=IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs
Sheet2:"&Sheet2!A1, ""). I clicked in that cell and then
clicked on Edit and then Copy. Since the columns
in both of the worksheets I wanted to compare extended to AE with
804 rows, I then selected all of the columns from A to AE and all
rows from 1 to 804 and then clicked on Edit and then
Paste Special with All selected. I then clicked
on OK to copy the formula throughout the new worksheet.
Excel automatically updates the references so that B2, for instance,
gets the formula =IF(Sheet1!B2 <> Sheet2!B2,
"Sheet1:"&Sheet1!B2&" vs Sheet2:"&Sheet2!B2, "").
Excel then showed the differences between Sheet1 and Sheet2 in Sheet3
where I had used a formula to compare cells in the two other sheets. As
shown below, if the contents of a cell differed, Excel showed the differences.
E.g. for cell A71, I saw Sheet1:I13-0003 vs Sheet2:I97-0033, since
Sheet1 had I13-0003 in that cell whereas Sheet2 had I97-0033
. If the cells matched, the corresponding cell in Sheet3 was empty.
So, even though the Mac version of Excel doesn't include the workbook comparison feature found in Windows versions of the program described at How to compare two Excel files for differences, you still may be able to compare sheets in two Excel files by copying relevant sheets into a new sheet where you can see the differences displayed. In the exmple above, the contents of E71 in Sheet3 showed the values for the other sheets as numeric values, though there were dates in the corresponding cells in Sheet1 and Sheet2.
Note: the Ablebits site where the above referenced article was posted also provides an Excel tool at Compare Excel spreadsheets for differences.
References: