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,040 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,040 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.
11 thoughts on “Compare Two Excel Sheets – Highlight Differences – Macro VBA Code”
Vеrу hеlрful.. Dіd ѕоmе сhаngеѕ аѕ реr mу соdе.. іt wоrkеd…
Thаnkѕ a lоt. But I muѕt аѕk, whаt іf I juѕt wаnt thе rеѕultѕ іn a nеw ѕhееt
аnd nоt a whоlе nеw wоrkbооk?
Shоuld mу dеfаult рrоgrаm bе a nеw wоrkbооk whеn thе
соmрutеr wоuld bе lеѕѕ сluttеrеd whеn thе lеѕѕ fіlеѕ/wоrkbооkѕ аrе ореn?
Sometimes in the office we share excel files and make small changes to them based on our metrics/performance. This tool is super useful so that when we all come back we can actually remember everything that changed, and then we all know the specifics.
I cannot run the code. It keeps giving me a compilation error for the sub 🙁
Thanks for contacting. I would be happy to help with this the issue. Please send a screenshot of the error along with the line number the error is reported.
Nice Post on Compare two Excel Sheets. if anyone unable to write VBA Script Then you can use synkronizer Excel Tool. It is easy and Fast.
Hi I am getting subscript out of range error when i tried to compare two workbooks but in the store, i dont see that as a product. Any ideas?
Pls check the 5th product in this page.
Thanks, can you briefly describe the difference between full version vs the free version? Like what is added in the full version? Will I be able to see/modify the codes you have embedded?
Pls contact me: email@example.com to get a feasible quote for your requirement along with source code.
kumarapush yes but
Thanks for contacting. But I guess your message is truncated. pls send the details of the issue.