Sometimes you may need to compare two Excel spreadsheets for analyzing the differences and similarities in data. Here are four different ways to compare & analyze two excel documents.
1. By using the View Side by Side option
- Open both files.
- Go to View ribbon.
- Turn on “View side by side” mode.
2. By using Spreadsheet Compare (Excel compare freeware) add-in for Microsoft Excel.
Spreadsheet Compare performs a cell-by-cell comparison of Worksheets within the same or different Workbooks and then highlights the changes on the sheet, or prepares a new sheet report showing all the changes the program has found. The add-in is written in VBA and is compatible with Excel 2000 and through every version till Excel 2010.
After installation, the Spreadsheet Compare function becomes available under the Add-in tab in MS Excel’s ribbon. You will notice two buttons here – Compare and Sync Scroll. Click the Compare button.
A step-by-step wizard will guide you through the process which is, briefly, as follows: you select the two spreadsheets to be compared, select the start and end row, set sheets to ignore (if any), specify data ranges to ignore (if any) select report type, choose highlight colors and if necessary, stop the program once the first mismatch is detected. If you don’t have any changes to make in the configuration, you can skip all the steps and jump directly to ‘compare’.
After the comparison is done, Spreadsheet Compare highlights the differences found on both sheets and adds a column to show you which columns have been changed. It also generates a detailed comparison report and a merged difference report.
Spreadsheet Compare should be very useful when working with large Excel sheets, and can potentially save you enormous amount of time.
3. By Creating a macro.
For creating a macro, click on Tools in menu bar, select Macro and then Macros in sub menu and Copy below code and paste it in the editor.
' Macro1 Macro
' compare two different worksheets in the active workbook
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim diffB As Boolean
Dim r As Long, c As Integer, m 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..."
Application.DisplayAlerts = True
lr1 = .Rows.Count
lc1 = .Columns.Count
lr2 = .Rows.Count
lc2 = .Columns.Count
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2 If maxC < lc2 Then maxC = lc2 DiffCount = 0 For c = 1 To maxC For i = 2 To lr1 diffB = True Application.StatusBar = "Comparing cells " & Format(i / maxR, "0 %") & "..." For r = 2 To lr2 cf1 = "" cf2 = "" On Error Resume Next cf1 = ws1.Cells(i, c).FormulaLocal cf2 = ws2.Cells(r, c).FormulaLocal On Error GoTo 0 If cf1 = cf2 Then diffB = False ws1.Cells(i, c).Interior.ColorIndex = 19 ws1.Cells(i, c).Select Selection.Font.Bold = True Exit For End If Next r If diffB Then DiffCount = DiffCount + 1 ws1.Cells(i, c).Interior.ColorIndex = 0 ws1.Cells(i, c).Select Selection.Font.Bold = False End If Next i Next c Application.StatusBar = "Formatting the report..." 'Columns("A:IV").ColumnWidth = 10 m = maxR - DiffCount - 1 Application.StatusBar = False Application.ScreenUpdating = True MsgBox m & " cells contain same values!", vbInformation, _ "Compare " & ws1.Name & " with " & ws2.Name End Sub
Save this Macro by closing the editor.
Running the macro
Select Sheet1 and run the Macro by clicking on Tools->Macro->Macros. Select the Macro “Compare” and click on “Run” button. Duplicate entries will be highlighted in Sheet1.
4. By using XL Comparator
XL Comparator is a free website that enables users to compare two spreadsheets. With XL Comparator, users can compare without doing any programming or deploying any software in their machine. Easy to use, users select two worksheets to compare, choose the column from the workbooks to compare and finally download the comparison spreadsheets. XL Comparator supports Microsoft Excel 95, 98, 2001, XP and 2003 formats. However, it doesn't offer support to Microsoft Excel 2007 yet.