Compare Two Excel Sheets – Highlight Differences – Macro VBA Code

How to Compare Two Excel Sheets for Differences?

How do you compare Excel Spreadsheets? To assure quality of a software, compare Excel files as explained below.

1. Using formula to compare two cells in Excel or
2. VBA code to compare two Excel sheets and highlight differences
   • Compare Sheets from Same Excel Workbook
   • Compare Sheets from 2 different Excel Files

Or just download this file & test your Excel files for any differences.

Download Trial Compare Two Excel Files Before Buying Downloaded 6,166 Times

There are many instances, we have to choose between ‘quality’ and ‘effort’ for a task completion.

Comparing data in two Excel spreadsheets is one such task that Software professionals have to prefer quality even at cost of a huge effort.

1. Excel Compare Two Columns & Highlight Differences

To, compare 2 Cells in a Row or Column, we can just use the “=” operator.

You will get a TRUE if the compared values are identical and FALSE if there is mismatch in data. Then use conditional formatting to highlight the difference in below steps.

Example: Compare Columns A & B, then Highlight Differences Using Conditional Formatting

1. Enter formula in cell C1: ‘A1=B1’ or ‘=IF(A1=B1,TRUE,FALSE)’
2. Drag the formula to other cells down the column ‘C’
3. Choose ‘Conditional Formatting’ in ‘Styles’ Tab in ‘Home’ menu.
4. Click ‘New Rule’.
5. Choose “Format only cells that contain”
6. Select “Cell Value” “Equal to” “FALSE” & choose a color.

Similarly add a new rule for TRUE condition.

This way, we can compare two excel columns and highlight the difference with 2 colors.

If you have to compare data from 2 different spreadsheets then use cell references in the formula.

2.1 Macro to Compare Two Excel Sheets and Highlight Difference

To Compare 2 Excel spreadsheets use the below VBA code.

1. Open Excel file that has the worksheets to be compared.
2. Create a 3rd sheet to display the Results
3. Press Alt + F11 to view VB Editor for Excel.
4. Copy paste the below given macro.
5. Press F5 to initiate comparison process and highlight the cells that have a mismatch.

This macro to compare Excel worksheets. It will first loop through each row and then read every cell to verify the data.

Option Explicit
'For More Free Code & Ideas Visit http://OfficeTricks.com
Sub Compare_Two_Excel_Sheets_Highlight_Differences()
    'Define Fields
    Dim iRow As Double, iCol As Double, oRow As Double
    Dim iRow_Max As Double, iCol_Max As Double
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim shOut As Worksheet
    
    'Sheets to be compared
    Set sh1 = ThisWorkbook.Sheets(1)
    Set sh2 = ThisWorkbook.Sheets(2)
    Set shOut = ThisWorkbook.Sheets(3)
    
    'Max Rows
    iRow_Max = sh1.UsedRange.Rows.Count
    iCol_Max = sh1.UsedRange.Columns.Count
    
    'Read Data From Each Sheets of Both Excel Files & Compare Data
    For iRow = 1 To iRow_Max
    For iCol = 1 To iCol_Max
        sh1.Cells(iRow, iCol).Interior.Color = xlNone
        sh2.Cells(iRow, iCol).Interior.Color = xlNone
        
        'Compare Data From Excel Sheets & Highlight the Mismatches
        If sh1.Cells(iRow, iCol) <> sh2.Cells(iRow, iCol) Then
           sh1.Cells(iRow, iCol).Interior.Color = vbYellow
           sh2.Cells(iRow, iCol).Interior.Color = vbYellow
           
           'Write Differences to Output sheet
           oRow = oRow + 1
           shOut.Cells(oRow, 1) = sh1.Cells(iRow, iCol)
           shOut.Cells(oRow, 2) = sh2.Cells(iRow, iCol)
        End If
    Next iCol
    Next iRow
    
    'Process Completed
    MsgBox "Task Completed - Thanks for Visiting OfficeTricks.Com"
    
End Sub

Once executed this macro will compare two Excel sheets and highlight the differences.

2.2 Executing Macro to compare 2 Excel Files cell by cell

In the above code, we compared 2 Excel spreadsheets from one file. But , with this code, we can compare any number of sheets from 2 different Excel files.

Download Compare Two Excel Files & Report Differences Downloaded 6,166 Times

Option Explicit
'For More Free Code & Ideas Visit http://OfficeTricks.com
Sub Compare_Two_Excel_Files_Highlight_Differences()
    'Define Object for Excel Workbooks to Compare
    Dim sh As Integer, ShName As String
    Dim F1_Workbook As Workbook, F2_Workbook As Workbook
    Dim iRow As Double, iCol As Double, iRow_Max As Double, iCol_Max As Double
    Dim File1_Path As String, File2_Path As String, F1_Data As String, F2_Data As String
    
    'Assign the Workbook File Name along with its Path
    File1_Path = ThisWorkbook.Sheets(1).Cells(1, 2)
    File2_Path = ThisWorkbook.Sheets(1).Cells(2, 2)
    iRow_Max = ThisWorkbook.Sheets(1).Cells(3, 2)
    iCol_Max = ThisWorkbook.Sheets(1).Cells(4, 2)

    Set F2_Workbook = Workbooks.Open(File2_Path)
    Set F1_Workbook = Workbooks.Open(File1_Path)
    ThisWorkbook.Sheets(1).Cells(6, 2) = F1_Workbook.Sheets.Count
    
    'With F1_Workbook object, now it is possible to pull any data from it
    'Read Data From Each Sheets of Both Excel Files & Compare Data
    For sh = 1 To F1_Workbook.Sheets.Count
        ShName = F1_Workbook.Sheets(sh).Name
        ThisWorkbook.Sheets(1).Cells(7 + sh, 1) = ShName
        ThisWorkbook.Sheets(1).Cells(7 + sh, 2) = "Identical Sheets"
        ThisWorkbook.Sheets(1).Cells(7 + sh, 2).Interior.Color = vbGreen
        
        For iRow = 1 To iRow_Max
        For iCol = 1 To iCol_Max
            F1_Data = F1_Workbook.Sheets(ShName).Cells(iRow, iCol)
            F2_Data = F2_Workbook.Sheets(ShName).Cells(iRow, iCol)
            
            'Compare Data From Excel Sheets & Highlight the Mismatches
            If F1_Data <> F2_Data Then
                F1_Workbook.Sheets(ShName).Cells(iRow, iCol).Interior.Color = vbYellow
                ThisWorkbook.Sheets(1).Cells(7 + sh, 2) = "Mismatch Found"
                ThisWorkbook.Sheets(1).Cells(7 + sh, 2).Interior.Color = vbYellow
            End If
        Next iCol
        Next iRow
    Next sh
    
    'Process Completed
    ThisWorkbook.Sheets(1).Activate
    MsgBox "Task Completed - Thanks for Visiting OfficeTricks.Com"
    
End Sub

Before using this macro, assign the Excel File names to File1_Path and File2_Path. Also, assign the number of rows and Columns that you want to compare to iRow_Max and iCol_Max.

When you compare spreadsheets, you also have to consider the Data Type. Some Excel Files have date and time stamps added to any Column.

The above code is not case sensitive. i.e., a = A will return a TRUE. Few Times, a Blank space might be appended to data that will give incorrect results. Modify the above code to use TRIM function and truncate the unwanted spaces in data before comparison.

3. Compare Excel Files – Microsoft Spreadsheet Compare

With the latest version of Office 2013, Microsoft has included ‘Spreadsheet Compare’ a tool itself. To invoke this, from start menu, start typing ‘Spreadsheet’ and you can find this tool.

In this tool, provide the Excel files sheets or names that you want to compare and then set additional option on what you are trying to compare. The rest will be taken care by the tool itself. Find more explanation from Microsoft itself in this link.

If a comparison is done manually, there are chances for many errors or mismatched data escaping through human eyes. We tag those excuses as “Manual Errors”. To avoid this, automate data comparison process even though it takes more time to complete.