How to Compare 2 Excel Spreadsheets using Macro?
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
- Using VBA code to compare two Excel sheets and highlight differences
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.
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. Compare 2 Excel Sheets Cell by Cell
Let’s start with few basics. Though it is termed as Excel Sheet or File, we first have to start everything with the building component of every Excel Sheet. So, let’s first compare 2 Cells in a Row or Column.
Excel Compare two Columns Cells for differences
We can just use the conditional Operators IF or just “=” formula to Compare data In 2 Cells. For example to compare the values in cells A1 and B1, just enter one of the below formula in cells C1.
You will get a TRUE if the compared values are identical and FALSE if there is mismatch in data. To check whole column values, drag the formula to other cells down the column and use conditional formatting to highlight the difference in below steps.
Using Conditional Formatting & highlight Differences
- From ‘Home’ menu, choose ‘Conditional Formatting’ in ‘Styles’ Tab.
- Click on ‘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 sheets 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. Macro to Compare Two Excel Sheets and Highlight Difference
To Compare 2 Excel spreadsheets or 2 different Excel Workbooks use the below VBA code. Create 2 Excel files with different Sheets of identical data. Then follow the listed instruction.
- Create a new Excel Workbook to run compare macro.
- In Sheet1, enter these details.
- Cell (1,2) – First Excel file name with complete path.
- Cell (2,2) – Second Excel file name with path to be compared with first Excel file.
- Cell (3,2) – Maximum number of rows to be compared.
- Cell (4,2) – Maximum number of columns to be compared.
- 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 files, will first open the Input files within code, loop through each sheet and then read every cell to verify the data.
Visual Basic1234567891011121314151617181920212223242526272829303132333435363738394041424344454647Option Explicit'For More Free Code & Ideas Visit http://OfficeTricks.comSub Compare_Excel_Files_WorkSheets()'Define Object for Excel Workbooks to CompareDim sh As Integer, ShName As StringDim F1_Workbook As Workbook, F2_Workbook As WorkbookDim iRow As Double, iCol As Double, iRow_Max As Double, iCol_Max As DoubleDim File1_Path As String, File2_Path As String, F1_Data As String, F2_Data As String'Assign the Workbook File Name along with its PathFile1_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 DataFor sh = 1 To F1_Workbook.Sheets.CountShName = F1_Workbook.Sheets(sh).NameThisWorkbook.Sheets(1).Cells(7 + sh, 1) = ShNameThisWorkbook.Sheets(1).Cells(7 + sh, 2) = "Identical Sheets"ThisWorkbook.Sheets(1).Cells(7 + sh, 2).Interior.Color = vbGreenFor iRow = 1 To iRow_MaxFor iCol = 1 To iCol_MaxF1_Data = F1_Workbook.Sheets(ShName).Cells(iRow, iCol)F2_Data = F2_Workbook.Sheets(ShName).Cells(iRow, iCol)'Compare Data From Excel Sheets & Highlight the MismatchesIf F1_Data <> F2_Data ThenF1_Workbook.Sheets(ShName).Cells(iRow, iCol).Interior.Color = vbYellowThisWorkbook.Sheets(1).Cells(7 + sh, 2) = "Mismatch Found"ThisWorkbook.Sheets(1).Cells(7 + sh, 2).Interior.Color = vbYellowEnd IfNext iColNext iRowNext sh'''''Process CompletedThisWorkbook.Sheets(1).ActivateMsgBox "Task Completed - Thanks for Visiting OfficeTricks.Com"End Sub
Once executed this macro will compare two Excel sheets and highlight the differences.
Executing Macro to compare 2 Excel sheets cell by cell
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.
Compare Excel Files – VBA Macro compare 2 Excel sheets & Highlight