Find differences in Excel files using Conditional Formatting, VLOOKUP and VBA macro

If you are new to Excel, understanding how to effectively compare values in Excel sheets will save you a huge amount of efforts and time.

In this article we discuss ALL methods you can use to compare two excel workbooks and highlights the modifications you have made.

Unfortunately, Excel allows to open only one workbook in a window, so comparison operation is not easy task in Excel application. Excel itself is not fully oriented for comparison operations, so this gap is closed by the software, that was designed only to compare Excel files.

Let's try to analyze every solution and find its pros and cons and determine when you can use it.

Please, note. If you are looking for a way to compare two Excel file in a batch mode - there is no way to do this, using methods that are described below. You need an excel file compare tool for this. Only xl comparator application allows integration into third party SVN or GIT environment.

Note: all of the described methods are FREE solutions.

We will enumerate methods in by increasing their complexity and explain each step.

1. Use xlCompare.
   Reasonable question – why should I use this Excel diff tool?
   It is not free and I need only to see the difference between two Excel files.
   You need to know that xlCompare has free evaluation period and you can use almost all functions of the software.
   So, if you need to compare your Excel workbooks for differences for FREE – xlCompare is an ideal solution.
   You can use all power of the xlCompare to get your job done quickly and efficiently. And you should not pay for this!
2. Compare your Excel files using free web service.
   This is also good and robust solution to compare Excel file for changes, that will work on the most of spreadsheets.
3. Compare Excel worksheets using Conditional Formatting rules.
   This is good method, but it loses in all aspects in comparison to the methods described above. It doesn’t give you enough features and enough productivity. Also, there is a risk of errors.

Even if your worksheets are simple – try using xlCompare in the evaluation mode. You are not obliged to pay for it and this Excel diff tool solves your task in seconds.

1. No registration required
2. Easy to install and use
3. Just drop your files into xlCompare and get the results!

Hope, you will enjoy the tool!

Free alternative to paid Excel diff tools

Not all Excel users need to pay for Excel diff tool. Someone can't install the software on his office computer, other users have very simple spreadsheets and there is no reason to pay for professional tool.

That's why we would like to provide you a set of completely free Visual Basic macros use can use to compare your Excel files without using Excel diff tool.

This topic will be continuously extended with examples of VBA code, that performs Excel worksheet comparison and extracts unique rows.

Visual Basic for application is a powerful and feature rich development environment. If you are VBA programmer, probably, you can easily create a macro that compares Excel worksheets for differences. But, if you have only basic knowledge in VBA, we will fill this gap and give you an example of the VBA macros you can use to compare your Excel files for differences.

The topics below contain Visual Basic macros that you can use without any limitations on your computer to compare Excel worksheets and highlight differences.

What I need to do to use this code?

1. Open your worksheet
2. Use Alt+F11 to go to Visual Basic Editor
3. Insert new module into your workbook
4. Paste macro into this module

Our macros use worksheet names Sheet1 and Sheet2. However your file may have different names. In this case you need locate this line and enter actual worksheet names, that you are going to compare:

              ' Here you need to enter appropriate sheet names
  Sheet1 = "Sheet1"
  Sheet2 = "Sheet2"

If none of the published macros solve your task - just let us know the details. Our developers will quickly create a VBA code and share it with community.

List of the VBA macros:

1. Compare Excel worksheets cell-by-cell
2. Use Conditional Formatting to compare Excel worksheets
3. Compare Excel worksheets by Key Column

What are the benefits of using this method?

From our point of vew Conditional Formatting is better. When you change any cell - comparison results are updated automatically and highlighting appears. Or it is automatically disappear, if cells become equal.

Previous method is a static one-time comparison. After you change anything on the worksheet, you need to compare it once again.

This time we have more complex example and more "real". We will compare two Excel tables by the key column.

What is key? This is unique value, that identifies row on the worksheet. If keys are equal - they represent same record, otherwise they represent different records. Usually Primary Key is a number or string value, like SKU, Item No., User Name, ...

Do I need to sort the data? You don't need to sort your tables. We will do this for you using macro! Just copy it into your workbook and get the results.

The code below has following requirements:

• Key column is first in your table
• First row is a header in both tables
• Columns have identical structure

If your data doesn't meet these requirements - the algorithm below will not work correctly.

Option Explicit

Public Sub CompareSheets()

    Dim Sheet1 As String
    Dim Sheet2 As String
    
              ' modify these lines if you need to change worksheet names
    Sheet1 = "Sheet1"
    Sheet2 = "Sheet2"
    
    Application.ScreenUpdating = False
    
              ' Sort data on the worksheets
    SortSheetByKey (Sheet1)
    SortSheetByKey (Sheet2)
    
    Dim Range1 As Range
    Dim Range2 As Range
    
    Set Range1 = Worksheets(Sheet1).UsedRange
    Set Range2 = Worksheets(Sheet2).UsedRange
    
    Dim nRowCount1 As Long
    Dim nRowCount2 As Long
    
    nRowCount1 = Range1.Rows.Count
    nRowCount2 = Range2.Rows.Count
    
    Dim nColCount As Long
    
    nColCount = WorksheetFunction.Min(Range1.Columns.Count, Range2.Columns.Count)
    
    Dim nCol As Long
    Dim nRow1 As Long
    Dim nRow2 As Long
    
    nRow1 = 1
    nRow2 = 1

    While nRow1 <= nRowCount1 And nRow2 <= nRowCount2
        If Range1.Cells(nRow1, 1) < Range2.Cells(nRow2, 1) Then
              ' row from sheet1 is not present on the sheet2
            Range1.Rows(nRow1).Style = "Accent1"
            nRow1 = nRow1 + 1
        ElseIf Range1.Cells(nRow1, 1) > Range2.Cells(nRow2, 1) Then
              ' row from sheet2 is not present on the sheet1
            Range2.Rows(nRow2).Style = "Accent6"
            nRow2 = nRow2 + 1
        Else
            For nCol = 2 To nColCount
                If Range1.Cells(nRow1, nCol) <> Range2.Cells(nRow2, nCol) Then
                    Range1.Cells(nRow1, nCol).Style = "Accent2"
                    Range2.Cells(nRow2, nCol).Style = "Accent2"
                End If
            Next
            
            nRow1 = nRow1 + 1
            nRow2 = nRow2 + 1
        End If
    Wend
    
    While nRow1 <= nRowCount1
              ' row from sheet1 is not present on the sheet2
        Range1.Rows(nRow1).Style = "Accent1"
        nRow1 = nRow1 + 1
    Wend
    
    While nRow2 <= nRowCount2
              ' row from sheet2 is not present on the sheet1
        Range2.Rows(nRow2).Style = "Accent6"
        nRow2 = nRow2 + 1
    Wend

              ' arrange windows, so you can see both worksheets
    Application.ScreenUpdating = True
    
    ActiveWindow.NewWindow
    Sheets("Sheet2").Select
    Windows.Arrange ArrangeStyle:=xlVertical

End Sub

Private Sub SortSheetByKey(ByVal sSheetName As String)
    Dim oRange As Range
    
    Set oRange = Worksheets(sSheetName).UsedRange
    
    With Worksheets(sSheetName).Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=oRange.Resize(, 1) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortTextAsNumbers
        
        .SetRange oRange.Offset(1)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This is easy method, that is suitable for everyone. Even novice in Excel can compare Excel workbooks in this way.

1. Open files you would like to compare in Excel application
2. Select View tab on the Ribbon and select View Side by Side command
   
3. Arrange your Excel windows vertically using Arrange All command
   
   Now you have your files located one versus one. This is perfect layout to compare and contrast changed cells in both tables.
4. Select Synchronous Scrolling option on the View tab
   
   This step is important, as without this feature you will spend you time by scrolling both worksheets separately.
5. Now you can scroll down your worksheets and compare cell values one by one.
   
   Probably here we should stop. This method is based on the manual reviewing of the Excel files. All other actions to find the discrepancies will require additional Excel featured and commands.

This is just a few cons, we can continue.

This method is too weak and if it works for you - this is a good and working solution. In this case you should order a professional Excel files comparison tool. But we suggest you to add Contitional Formatting to this as described below to get better results.

Conditional Formatting is a powerful feature in Excel, that allows to change cell style options depending on the various conditions. So, applying it to compared Excel sheets is the only solution to highlight differences in Excel.

After placing your worksheets side by side, like in the previous example, select compared range on the worksheet and use Conditional Formatting | New Rule command on the Home tab.

It opens a window, where you need to enter formula that compares two corresponding cell values and select Formatting Options for the changed cell.

Rule Type: use a formula to determine which cell to format

Formula: =A4<>'Transactions (2)'!A4

Format ... button opens a Format Cells dialog, where you can select desired cell formatting. In our example we've applyed orange fill color to the changed cells.

This method is an upgrade of the previous one. We've added Conditional Formatting and got perfect difference report.

So, our conclusion - this is a very good way to compare small worksheets with exactly same layout. It is not suitable for most of the real cases, as worksheet layout can be different in the Excel files.

This method is also often used, because it requies only a few manual actions and can be used by novice also. First you need to understand what VLOOKUP worksheet function does. Lets refer to Excel Help Library:

Perfect. So, you can use this method for not cell by cell comparison, but to compare two columns for different values. In other words, this topic gives you answer to other question:

This method works perfectly if you have unordered tables.

Remember, VLOOKUP worksheet function returns #N/A error value if lookup value is not found. We would like to find and highlight missed cell values in two Excel columns, so better to avoid errors on the sheet. For this purpose we'll use worksheet function ISNA:

Now we are ready to compare two Excel columns for different values.

1. To compare two Excel files for differences, open Excel file and apply New Window command on the View tab.

This is necessary to display two worksheets within same Excel workbook simultaneously. If you skip this step you can't see corresponding values.

2. Apply Side by side view and Synchronous scrolling

These commands put both Excel windows one versus one on your screen.

Now you have MISSED value in the extra column that indicates new (inserted) value.

Again we need to select Ryle type as Use a formula to determine which cell to format and enter following formula:

For this case we've selected other formatting for unique rows: Red Fill Color in a combination with white Font Color.

After we've learned Conditional Formatting and some worksheet functions, we can make a variation of the methods described above and get the worksheets compared with perfectly formatted differences.

In this method we will simulate Extended View option, that is present in xlCompare. It displays original and changed value in same cell, that makes report much more useful and native. To understand our goal, look into the image below, we've highlighted cell with changed value.

A little of theory. What we are going to use:

CONCAT function:

Syntax

CHAR function

You don't need to know what these functions do and how to use them. We are giving you ready to use solution, that you can use out of the box.

Let's open files you are going to compare in Excel

In our example files are named Sales Transactions 1.xlsx and Sales Transactions 2.xlsx

Both worksheets that we are going to compare have same name - Transactions

Select first file

Insert new worksheet and give it name Summary

We will compare ranges A1:D19. Remember range of cells you are going to compare.

Open notepad on your computer and copy following text:

=IF(SHEET1!A1='[BOOK2]SHEET2'!A1, SHEET1!A1, CONCAT(SHEET1!A1,CHAR(10),'[BOOK2]SHEET2'!A1))

This is a template of the formula we are going to use to compare cell values and create difference report.

• Replace SHEET1 with name of the first sheet. If your worksheet name contains spaces - type it in single quotes.
• Replace SHEET2 with name of the corresponding sheet
• Replace BOOK2 with name of the second Excel file, including extension

In our example we've got following formula:

• Paste it into cell A1 on the Summary worksheet and hit ENTER
• Fill cells with this formula to the right - in our example this is column D
• Fill cells down to cover entire range of the table. In our example this is first 19 rows

Use Conditional Formatting to Highlight Changes

Now you have report on your screen, but it doesn't have formatting. Let's do final step to get the best comparison report, that is perfectly formatted.

• Keep all your cells selected and add new conditional formatting rule
• Select type of rule - Use a formula to determine which cells to format
• And paste this formula=NOT(ISERROR(FIND(CHAR(10),A1)))
• Select formatting for the modified values that you like. We've selected orange background and bold red font

Wrap cell text to simulate Extended View

• Right click on the selected area
• Select Format Cells menu item

• Enable Word Wrap on the selected range
• Click OK and get the final diff report

All cell values that are different on these worksheets are highlighted by color. In this report you have source value and value of changed cell in one place. Very similar to Extended View mode that you can find only in xlCompare.

With this method you don't need to select Side by Side layout - you have all of the differences on one sheet.

If you don't do extra clicks or scrolling - in several mouse clicks you get your worksheets compared for changed cell values and formatted with selected color options.

What you can do with this report:

• Customize the formatting (adjust column width, font and colors)
• Save it on disk for future use
• Send by email to colleague
• Make additional formatting and Print as PDF
• Present to your co-workers

Proc

• This report has good presentation part

Cons

• Method requires worksheets with identical structure
• Only changed cells are detected (it doesn't check worksheets for inserted rows)

This is another good and robust method to compare xlsx and CSV files, that perfectly works on the small workbooks with similar structure. More information on this you find in the article below:

Compare Excel Files for differences online

All the methods to compare Excel worksheet for differences, described above, are not so effective as desktop application that compares Excel files - xlCompare.

• xlCompare points you directly to what is different on the worksheets.
  It gives you a complete list of differences and you can browse and analyze all of them.
  
• Ideal way to contrast discrepancies between two Excel files.
  xlCompare shows original and modified value in same cell and gives you full information on the modification including difference of the values.
• Compare formulas and cell formatting.
  If you are working in the team environment, modified worksheets may look identical, so it is almost impossible to get an answer - what is changed in these worksheets. xlCompare loads all cells and compares cell values, formulas and cell formatting, names and tables. It gives you comprehensive report on the changes made by you or your colleague.
• Compare Visual Basic Projects for differences.
  For VBA developers this is a real pain to compare and merge visual basic modules in the XLSM files and forms or controls. xlCompare is the only solution that compares visual basic forms and controls and gives you an easy-to-use commands to merge changes.
• Create Comparison Report.
  The methods above give you only visual information, but you can't copy this report or save it to disk. xlCompare gives you a number of reports including saving color coded report into PDF format or outlined comparison report that contains information about all discrepancies found in two Excel files (worksheets). Report is structure by type of the difference and by worksheets.
• 3-Way comparison for XLSX and CSV files.
  What is 3-way comparison? Imagine you and your colleague modified same version of your common workbook. You need to analyze his changes and create common merged version of file. In other words, you need to compare two Excel worksheets that have same base file. In the SVN(GIT) environment this is called merge conflict. Usually, it takes a lot of time to resolve it, but with xlCompare you will do in a few seconds.
• Command line mode.
  xlCompare is tool designed to by used in the command line mode also. If you are working with SVN or GIT, you need a way to compare and merge XLSM\XLSX\XLS\XLA\XLAM\CSV files by external diff checker. xlCompare fills this gap and gives you a diff viewer (xl comparator) for the file types listed above.

There are a number of operations you need to do to compare Excel files and get the result.

Armed with this tool you will save a lot of time and efforts.

Let's talk about comparing database tables stored in Excel worksheets. How database table looks in Excel. Usually, database is represented exactly like table in database management application:

• First row is a header, that contains list of fields
• All other rows contain database data in each cell

Excel is often used to store this type of data. Application logs, price lists, financial and accounting statements, ... - all of them are examples of the database tables.

Why is database table compared in the different way relatively to other types of worksheets?

Every table has a primary key column. Primary key is a unique identifier, assigned to record. Only this identifier defines - is this a new row or edited row.

How xlCompare works with database tables

If you know which column in your worksheet is a primary key column - select it and do the right click. There is a menu item - Set as Primary Key. This marks column as a key.

You can use several columns are key. Normally this is wrong, if database table doesn't have a numeric key field. But in the Excel tables we allow to use combination of columns to create primary key.

For example: you don't have ID column in the list of employees, but there is First Name and Last Name field. Select both of them and mark as Primary Key.

Which type of data should be in the key column?

xlCompare allows you to use ANY column as Primary Key. This could be numeric, strings, data or Boolean values.

Should you always define Primary Keys?

No. When you open your workbook in xlCompare is analyzes data on your worksheets and suggests you, key columns and key rows. Just look over your worksheets to make sure that selection is correct. If needed you can change it with Right Click menu.

How to make sure that database algorithm is used for my worksheets?

In the Comparison Wizard every pair of compared worksheets has icon that indicates algorithm used for this pair. Database icon means, that this pair of sheets is compared by Primary Key.

Use Options button to view and change the algorithm.

After you've got comparison results - algorithm icon is reflected in the Difference Explorer.

Conclusion

Review your worksheet data and if it has signs of the database - apply database algorithm to get accurate results.

Compare Excel Files with Changed Primary Keys

Get more information about comparison process. How we compare excel files for differences, what types of excel files are processed and what operations xlCompare performs to deliver you best comparison results. How to consolidate and update excel files and what do you need for this. Read about this process in detail. Compare 2 excel columns for differences and get the outlined report with explanation for the every found difference.

Spreadsheets are widely involved into all kinds of business processes. Excel File (workbook) is very suitable form to store and interchange datasets.

Some of the third-party applications give you table data in a form of the text file, where values are delimited with comma, space, tab or any other characters. This is various types of application logs, exported transactions, financial statements and other data. It can't be procced by a usual text comparator. We offer you xlCompare as a text file comparison tool for table type data. On our website below describes options you can use to open and compare text (CSV) files, delimited with specific character, in the xlCompare.

If you are working with XLSX files, xlCompare is an Excel file comparison tool you need to compare and merge XLSX files into one. This is fast and feature rich xlcomparator. Spreadsheets a widely used by Excel users, so spreadsheet comparison and merging work is a part of regular office work. xlCompare will greately help you with this.