How to compare three Excel files(worksheets) for differences
What is three-way comparison?
If you are reading this page, probably you are looking for a method to compare 3 Excel files for differences. And probably you need to merge changes made in one Excel workbook into your copy.
Compare three Excel files is a basis for comparing multiple Excel files for differences. xlCompare is an ultimate tool that quickly compares three large Excel (CSV) files and produces comprehensive report. With xlCompare you can merge three Excel files and filter matching and inserted records in three Excel files.
For example, you are working on the Spreadsheet with your colleagues and need to synchronize changes made by you and co-worker in the same version of Excel file. You compare two different Excel files, that have same base version. This type of comparison is called three way comparison. This is typical case for the SVN and GIT systems, when you are merging two branches and have got a merge conflict. To resolve changes in the Excel file effectively without losing data you need to do 3-way comparison. You compare third version of Excel file with base version and move changes manually one by one into your workbook. Looks bad, right?
How to compare three Excel Files by viewing them side-by-side
The most common way to compare Excel files for differences is the following:
- Open two files in Excel
- Use View side by side command
- Arrange Windows vertically
- Highlight differences with conditional formatting rule
Let's add a bit of theory.
What is Conditional Formatting we are talking about?
Conditional Formatting is a feature, that allows to format range of cells on the worksheet, depending on some conditions.
This is powerful feature that allows you to highlight patterns and trends on your worksheets.
It allows you to easily identify cells by specific criteria.
In our case we are using it to highlight differences in cell values, between two worksheets.
Conditional Formatting has significant limitation that affects this method - formulas, used in the Conditional Formatting Rules can't refer to other workbook. So, all 3 compared worksheeets must be in the same Excel files.
But this method has one significant cons. It doesn't work with worksheets that have different layout (different number of rows and columns). And it doesn't detect inserted rows and columns.
If you have three or more Excel worksheets with similar structure - you can use this method to compare three Excel sheets for differences.
So, let's start comparing 3 Excel worksheets:
Open all 3 files in Excel
Unfortunately, this command is useless in case of 3 Excel worksheets. You can't use it to align 3 Excel sheets vertically, so we are using the New Window command 3 times, to be able to see three corresponding Excel sheets at the same time.
Align them vertically on your screen.Wide monitor is a big benefit in this case.
In the leftmost file add conditional formatting rule.That compares every cell value on the left sheet with corresponding value on the central sheet.
In our case formula is the following:
=A1<>'Transactions List 2'!A1
Formatting we are using - red background color.
After we've added this rule - all cells that were modified in the left worksheet are highlighted with red fill color.
Add same conditional formatting rule to rightmost worksheet.But in this case, we highlight values with green fill color. To make them different from the highlighted values on the left sheet.
Now you see differences in 3 Excel files.
- Method relatively easy to use the quick
- It works only on the spreadsheets with identical structure
- It doesn't detect changes in formulas
- It doesn't detect inserted rows and columns
So, we are very limited in the Excels capabilities to compare there or more worksheets.
Is there an alternative you can use? Our answer is - YES. xlCompare is the only excel file compare tool on the market that allows you to compare three Excel files, synchronize and highlight differences.
Compare three Excel worksheets in xlCompare
Some background about this tool.
xlCompare is a utility that compares and merges Excel files for differences (xl comparator). Primary goal of this tools is to effectively find discrepancies in Excel files and represent it to you in the most native way.
Enable 3-way comparison in Excel Compare
Use 3-Way button on the home tab on the Ribbon.
xlCompare switches into 3 worksheet mode and displays you three worksheet panels. One of them is marked as FILE1 another FILE2 and central worksheet - BASE. This terminology comes from the SVN\GIT environment, where we are talking about MINE, BASE and THEIR versions of the compared files.
Drop your workbooks into xlCompare
Don't forget to put them in the right order - common version of the worksheet must be in the center.
If your worksheets are three different excel files - order it not important and you can open them in the way you want.
After third file is opened xlCompare immediately starts the comparison and gives you highlighted results.
You don't need to enable any additional options, align windows etc. Everything is included into xlCompare, because comparison operations is a primary task of this application.
xlCompare synchronizes changes found in all three files and highlight them with background color.
You can scroll this window and go through all compared worksheets, in all files.
Bottom part of the Application Window contains Difference Report with all found discrepancies, grouped by worksheets.
What is Conflicted cell?
Some cells are marked with red color and has a conflict icon in the difference explorer. What is this. Imagine, that cell A10 was modified in all 3 files. This means we have editing conflict. There is no way to identify automatically which version is right.
Conflicts are always resolved manually.
What can I do after getting comparison report for 3 Excel sheets?
Filter matching and unique rows
Filter command on the Home tab on the ribbon allows to you to filter matching rows and changed rows in your 3 Excel worksheets.
Merge changed between 3 Excel files
With Merge commands you have in xlCompare you can effectively resolve any editing conflict in the Excel worksheets.
Extract matching and unique rows
xlCompare gives you set of commands that extracts matching or unique rows in your worksheets. Use them if you need to separate any kind of the worksheet data.
Create difference report
Create comparison summary report and send it to co-worker by e-mail or save it on disk for the presentation.
- Works with any types of Excel worksheets
- Detects changed cells and inserted rows
- Finds changes in cell value, formula and formatting options
- Quickly synchronizes changes in the large Excel worksheets
- This solution is not free. You can use it for free only during evaluation period, but after evaluation is complete - onetime payment is required.
xlCompare is sold as permanent license, so you pay only once and receive the software that works all the time.
How to compare three CSV files?
You can extend this method to compare CSV files also. CSV file or any other text file with delimiter is loaded by xlCompare as Excel workbook.
All commands applicable to Excel worksheet is applicable to CSV file also. So you can compare 3 CSV files just in the same way as usual Excel files.
xlCompare is unique solution that allows you can compare and merge three Excel files. It performs complex and time-consuming work into set of simple clicks and saves you a lot of time.
How to compare multiple Excel files
This method allows you to quickly analyze any number of Excel sheets for differences. You don't need to load all of them immediately. Your display probably doesn't have enough space to reflect all information. But you can easily change on of the files in 3-way mode and in the iterative way compare any number of Excel files for differences.