Find the differences between 2 Excel worksheets?

Easy way: Use a 3rd sheet to check.

Say you want to find differences between Sheet 1 and Sheet 2.

1. Go to Sheet 3, cell A1, enter =IF(Sheet2!A1<>Sheet1!A1,"difference","").
2. Then select all cells of sheet 3, fill down, fill right.
3. 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.

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.

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

Tried to find a tool that will help to extract only the different sheets with the cell difference highlighted. Could not find any, so ended up writing one for myself. I hope this helps someone who is looking for similar solution. It takes care of left/right unique sheets, identical/different size sheets.

import pandas as pd
import xlsxwriter
import numpy as np
from openpyxl import load_workbook

# Get a complete list of sheets from both WorkBook
BOOK1 = "Book_1.xlsx"
BOOK2 = "Book_2.xlsx"

xlBook1 = pd.ExcelFile(BOOK1)
sheetsBook1 = xlBook1.sheet_names
xlBook2 = pd.ExcelFile(BOOK2)
sheetsBook2 = xlBook2.sheet_names

sheets = list(set(sheetsBook1 + sheetsBook2))

with pd.ExcelWriter('Difference.xlsx', engine='xlsxwriter', mode='w') as writer:
    for sheet in sheets:
        print (sheet)

        book1 = None
        book2 = None
        book1Exists = True
        book2Exists = True

        try:
            book1 = pd.read_excel(BOOK1,sheet_name=sheet,header=None,index_col=False).fillna(' ')
        except ValueError as ve:
            book1Exists = False
        try:
            book2 = pd.read_excel(BOOK2,sheet_name=sheet,header=None,index_col=False).fillna(' ')
        except ValueError as ve:
            book2Exists = False

        # Case 1: Both sheet exist and they are identical size
        if ( (( (book1Exists == True) and (book2Exists == True) )) and 
            ( (len(book1) == len(book2)) and (len(book1.columns) == len(book2.columns)) )):


                comparevalues = book1.values == book2.values
                if False in comparevalues:
                    rows,cols = np.where(comparevalues==False)

                    for item in zip(rows,cols):
                        book1.iloc[item[0],item[1]] = ' {} --> {} '.format(book1.iloc[item[0], item[1]], book2.iloc[item[0],item[1]])

                    book1.to_excel(writer,sheet_name=sheet,index=False,header=False)
                    
                    # Get the xlsxwriter workbook and worksheet objects.
                    workbook  = writer.book
                    worksheet = writer.sheets[sheet]
                    
                    # Add a format. Light red fill with dark red text.
                    format1 = workbook.add_format({'bg_color': '#FFC7CE',
                                                   'font_color': '#9C0006'})

                    # Apply a conditional format to the cell range.
                    worksheet.conditional_format('A1:AZ100',{'type':     'text',
                                                  'criteria': 'containing',
                                                  'value':    '-->',
                                                  'format':   format1}) 

        # Case 2: Left unique case        
        elif (book1Exists == False):
            book2.to_excel(writer,sheet_name=sheet+" B2U",index=False,header=False)
        
        # Case 3: Right unique case        
        elif (book2Exists == False):
            book1.to_excel(writer,sheet_name=sheet+" B1U",index=False,header=False)
        
        # Case 4: Both exist but different size
        elif (( (book1Exists == True) and (book2Exists == True) ) and 
            ( (len(book1) != len(book2)) or (len(book1.columns) != len(book2.columns)) )):
            if (book1.size > book2.size):
                book1.to_excel(writer,sheet_name=sheet+" SD",index=False,header=False)
            elif (book2.size > book1.size):
                book2.to_excel(writer,sheet_name=sheet+" SD",index=False,header=False)