Comparing Excel workbooks in Microsoft Excel for Mac

Learning that lasts. Online courses from $14.99

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.

Excel differences sheet

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:

  1. How to compare two Excel files for differences
    By: Svetlana Cheusheva
    Updated: June 17, 2019
    Ablebits
  2. Spreadsheet Compare on Microsoft Excel for Mac
    By: A. User
    Date: July 9, 2017
    Ask the Microsoft Community