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.
Download Trial Compare Two Excel Files Before Buying Downloaded 6,078 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.
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.
- 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.
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,078 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.