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.- Using formula to compare two cells in Excel or
- 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.
Downloaded 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- Enter formula in cell C1: 'A1=B1' or '=IF(A1=B1,TRUE,FALSE)'
- Drag the formula to other cells down the column 'C'
- Choose 'Conditional Formatting' in 'Styles' Tab in 'Home' menu.
- Click 'New Rule'.
- Choose "Format only cells that contain"
- Select "Cell Value" "Equal to" "FALSE" & choose a color.
2.1 Macro to Compare Two Excel Sheets and Highlight Difference
To Compare 2 Excel spreadsheets use the below VBA code.
- Open Excel file that has the worksheets to be compared.
- Create a 3rd sheet to display the Results
- Press Alt + F11 to view VB Editor for Excel.
- Copy paste the below given macro.
- Press F5 to initiate comparison process and highlight the cells that have a mismatch.
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
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.Downloaded 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.