Find the differences between 2 Excel worksheets

Question

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

User · Answer

I found this command line utility that doesn't show the GUI output but gave me what I needed: https://github.com/na-ka-na/ExcelCompare

Sample output (taken from the project's readme file):

> excel_cmp xxx.xlsx yyy.xlsx
DIFF  Cell at     Sheet1!A1 => 'a' v/s 'aa'
EXTRA Cell in WB1 Sheet1!B1 => 'cc'
DIFF  Cell at     Sheet1!D4 => '4.0' v/s '14.0'
EXTRA Cell in WB2 Sheet1!J10 => 'j'
EXTRA Cell in WB1 Sheet1!K11 => 'k'
EXTRA Cell in WB1 Sheet2!A1 => 'abc'
EXTRA Cell in WB2 Sheet3!A1 => 'haha'
----------------- DIFF -------------------
Sheets: [Sheet1]
Rows: [1, 4]
Cols: [A, D]
----------------- EXTRA WB1 -------------------
Sheets: [Sheet1, Sheet2]
Rows: [1, 11]
Cols: [B, K, A]
----------------- EXTRA WB2 -------------------
Sheets: [Sheet1, Sheet3]
Rows: [10, 1]
Cols: [J, A]
-----------------------------------------
Excel files xxx.xlsx and yyy.xlsx differ

User · Answer

Excel has this built in if you have an excel version with the Inquire add-in    This link from office webpage describes the process of enabling the add-in  if it isn t activated  and how to compare two compare two workbooks - among other things   The comparison shows both structural differances as well as editorial and a lot of other changes if  http   office microsoft com en-us excel-help what-you-can-do-with-spreadsheet-inquire-HA102835926 aspx

User · Answer

you should try this free online tool - www cloudyexcel com compare-excel   works good for most of the time  sometimes the results are a little off   plus it also gives a good visual output     You can also download the results in excel format   you need to signup for that

User · Answer

LibreOffice provides a Workbook Compare feature  Edit -  Compare Document

User · Answer

I used Excel Compare  It is payware  but they do have a 15 day trial  It will report amended rows  added rows  and deleted rows  It will match based on the worksheet name  as an option    http   www formulasoft com excel-compare html

User · Answer

SO in fact that you are using excel means that you can use the  SpreadSheet Compare from Microsoft  It is available from Office 2013  Yes i know this question is older then 6 years  But who knows maybe someone need this information today

User · Answer

COUNTIF works well for quick difference-checking  And it s easier to remember and simpler to work with than VLOOKUP    COUNTIF  Book1 Sheet1  A  A  A1     will give you a column showing 1 if there s match and zero if there s no match  with the bonus of showing  1 for duplicates within the list itself

User · Answer

The Notepad   compare plugin works perfectly for this   Just save your sheets as  csv files and compare them in Notepad     Notepad   gives you a nice visual diff

User · Answer

May be this replay is too late  But hope will help some one looking for a solution  What i did was  I saved both excel file as CSV file and did compare with Windiff

User · Answer

excel overlay will put both spreadsheets on top of each other  overlay them  and highlight the differences   http   download cnet com Excel-Overlay 3000-2077 4-10963782 html tag mncol

User · Answer

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

User · Answer

If you have Microsoft Office Professional Plus 2013  you can use Microsoft Spreadsheet Compare to run a report on the differences between two workbooks   Launch Spreadsheet Compare   In Windows 7  On the Windows Start menu  under Office 2013 Tools  click Spreadsheet Compare   In Windows 8  On the Start screen  click Spreadsheet Compare  If you do not see a Spreadsheet Compare tile  begin typing the words Spreadsheet Compare  and then select its tile   Compare two Excel workbooks    Click Home   Compare Files  a  Click the blue folder icon next to the Compare box to browse to the location of the earlier version of your workbook   In addition to files saved on your computer or on a network  you can enter a web address to a site where your workbooks are saved    b  Click the green folder icon next to the To box to browse to the location of the workbook that you want to compare to the earlier version  and then click OK   TIP You can compare two files with the same name if they re saved in different folders   In the left pane  choose the options you want to see in the results of the workbook comparison by checking or unchecking the options  such as Formulas  Macros  or Cell Format  Or  just Select All    Reference   https   support office com en-us article Basic-tasks-in-Spreadsheet-Compare-f2b20af8-a6d3-4780-8011-f15b3229f5d8

User · Answer

Easy way  Use a 3rd sheet to check   Say you want to find differences between Sheet 1 and Sheet 2      Go to Sheet 3  cell A1  enter  IF Sheet2 A1 lt  gt Sheet1 A1  difference          Then select all cells of sheet 3  fill down  fill right     The cells that are different between Sheet 1 and Sheet 2 will now say  difference  in Sheet 3    You could adjust the formula to show the actual values that were different

User · Answer

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

User · Answer

ExcelDiff exports a HTML report in a Divided  Side-by-side  or Merged  Overlay  view highlighting the differences as well as the row and column

User · Answer

Use the vlookup function   Put both sets of data in the same excel file  on different sheets  Then  in the column next to the 805 row set  which I assume is on sheet2   enter   if isna vlookup A1  Sheet1  A 1  A 800  1  false    0  1    The column will contain 0 for values that are not found in the other sheet  and 1 for values that are  You can sort the sheet to find all the missing values

User · Answer

With just one column of data in each to compare a PivotTable may provide much more information  In the image below ColumnA is in Sheet1  with a copy in Sheet2 for the sake of the image  and ColumnC in Sheet2  In each sheet a source flag has been added  Columns B and D in the image   The PT has been created with multiple consolidation ranges  Sheet1  A 1  B 15 and Sheet2  C 1  D 10      The left hand numeric column shows what is present in Sheet1  including q twice  and the right what in Sheet2  again with duplicates     of c and d   d-l are in Sheet1 but not Sheet2 and w and z are in Sheet2  excluding those there just for the image  but not Sheet1  Add display Show grand totals for columns and control totals would appear

User · Answer

I think your best option is a freeware app called Compare IT        absolutely brilliant utility and dead easy to use   http   www grigsoft com wincmp3 htm

User · Answer

Use conditional formatting to highlight the differences in excel     Here s an example

[excel] Find the differences between 2 Excel worksheets?

Examples related to excel