Top 3 methods to compare Excel files for differences

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!

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 (Compare Excel Files with Excel Diff Tool), 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 diff 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.