Find the differences between 2 Excel worksheets?

I have two excel files with the same structure: they both have 1 column with data. One has 800 records and the other has 805 records, but I am not sure which of the 5 in the 805 set are not in the 800 set. Can I find this out using Excel?

Solution 1:

vlookup is your friend!

Position your column, one value per row, in column A of each spreadsheet. in column B of the larger sheet, type

=VLOOKUP(A1,'[Book2.xlsb]SheetName'!$A:$A,1,FALSE)

Then copy the formula down as far as your column of data runs.

Where the result of the formula is FALSE, that data is not in the other worksheet.

Solution 2:

It might seem like a hack, but I personally prefer copying the cells as text (or exporting as a CSV) into Winmerge or any other diff tool. Assuming the two sheets contain mostly identical data, Winmerge will show the differences immediately.

Solution 3:

LibreOffice provides a Workbook Compare feature: Edit -> Compare Document