How to compare two sheets or files in Ms-Excel?

Many times we are in need to compare data of two Ms-Excel sheets. This is to find out the differences or any discrepancies in data between two sheets. In this post, I will share some of the tools and methods available to compare two sheets in Ms-Excel.

For example purpose let us consider the following excel file with data into two sheets.

Solution 1: Online Tools

www.xlcomparator.net

Xlcomparator.net is a very nice free online tool to compare two sheets. This tool supports Excel 2003, 2007 and 2010 version. You can compare two files of less than 5MB size. Once you upload the file it presents a very neat interface about what you want to compare (which columns, what is source and what is destination, ignore uppercase/lowercase etc.). This tool gives you option to download a file which contains all the mismatch or discrepancies data.

There are other online tools available to compare data between two excel files. Some of them are free and for few you have to pay but you can download the evaluation version to see if it useful for your or not. Some examples are here:

• www.speadsheetdetective.com
  
• www.synkronizer.com
  
• Beyond Compare 3

Solution 2: VBA Solution

You can use the following VBA code shared by Graham Stent to compare two sheets. For example I have a excel file with data in two sheets available. The discrepancies data are also highlighted.

I pressed the ALT + F11 to switch to VBA and pasted the below code in Sheet1. I run the UseCompareWorksheets() function and it neatly pointed out the differences in data. You can use the same to compare data in two different excel files as well

Sub UseCompareWorksheets()

'[compare two different worksheets in the active workbook]
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")


'[compare two different worksheets in two different workbooks]
' CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
' Workbooks("WorkBookName.xls").Worksheets("Sheet2")

End Sub


Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)

Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long

Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False

While Worksheets.Count > 1
Worksheets(2).Delete
Wend

Application.DisplayAlerts = True

With ws1.UsedRange
    lr1 = .Rows.Count
    lc1 = .Columns.Count
End With

With ws2.UsedRange
    lr2 = .Rows.Count
    lc2 = .Columns.Count
End With

maxR = lr1
maxC = lc1

If maxR < lr2 Then maxR = lr2

If maxC < lc2 Then maxC = lc2

DiffCount = 0

For c = 1 To maxC
    Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %") & "..."
    For r = 1 To maxR
        cf1 = ""
        cf2 = ""
        On Error Resume Next
        cf1 = ws1.Cells(r, c).FormulaLocal
        cf2 = ws2.Cells(r, c).FormulaLocal
        On Error GoTo 0
        If cf1 <> cf2 Then
            DiffCount = DiffCount + 1
            Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
        End If
    Next r
Next c
'**************************************
' Formating Report for Good visibility
'**************************************

Application.StatusBar = "Formatting the report..."
With Range(Cells(1, 1), Cells(maxR, maxC))
            .Interior.ColorIndex = 19
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        On Error Resume Next
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        
        On Error GoTo 0
End With

Columns("A:IV").ColumnWidth = 20
rptWB.Saved = True

If DiffCount = 0 Then
    rptWB.Close False
End If

Set rptWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox DiffCount & " cells contain different formulas!", vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub

Differences were shown neatly in data.

Solution 3: Excel built-in Solution

There is a very good solution provided in MSDN Forum to compare data in two excel files. The solution is to use to copy data into one excel sheet and on a third sheet use the following formula. For the best result you can sort your data before apply the formula.

=SheetName1!ColumnRowNumber= SheetName2!ColumnRowNumber

For the excel file shown above I applied the below formula and it shows True and False for the values where data has matched and not matched respectively

=Sheet1!D1=Sheet2!D1

Solution 4: Excel 2013

If you are using Ms-Office 2013, there is an in-built option to compare two sheets.

Excel 2013 > Menu Inquire > Compare files
I do not have Excel 2013 so I have not tested it

There are other solutions also which uses VLookup, HLookup, IF, Count() function. But it depends on the data and complexity that you have to go with a particular solution.