Compare three(multiple) Excel files and get the difference
xlCompare is the best Spreadsheet Compare tool to compare three or multiple Excel files for difference and to merge Excel files. Highlight differences, create comparison report, reconcile differences made in the Excel files - xlCompare is able to solve a number of tasks, that are based on spreadsheet comparison.
Find differences between three or multiple Excel files
What is three-way comparison?
3-way comparison is a method of comparing 2 files vs. their common base version. You will get a list of changes made to every modified copy of the document.
It is also called compare 2 Excel files with common ancestor.
This feature is often requested by developers, who keep their Excel files under version control.
Game Development companies are primary users of this feature. xlCompare is a part of this business. It is used as a diff and merge tool for Excel files stored in GIT.
xlCompare is a solution to resolve merge conflicts with Excel files.
Compare three Excel files or sheets in xlCompare
Some background about this Excel diff tool.
xlCompare is a utility that compares and merges Excel files for differences (xl comparator). Primary goal of this ExcelDiff tool is to efficiently 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 efficiently 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.
Proc
- 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
Cons
- 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.
Integrate xlCompare with GIT, SVN, Perforce, ...
You can use xlCompare as a tool to view differences in Excel files and for automatic merge operations you are doing in version control applications.
This article describes how to integrate xlCompare as an external diff and merge tool for Excel files in GIT:
Compare three CSV files for difference
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.
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.
Proc
- Method relatively easy to use the quick
Cons
- It works only on the spreadsheets with identical structure
- It doesn't detect changes in formulas
- It doesn't detect inserted rows and columns
Conclusion:
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.
Conclusion:
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.
EXCEL COMPARE TOOL ON REDDIT
EXCEL COMPARE TOOL ON QUORA
Join xlCompare space on Quora!
In this space we are sharing information about xlCompare and discussing various use cases. Post your question in this space to share it with community.
We will be happy to see you in the list of our followers and contributors!
I have a question about xlCompare
Please, describe your question or inquiry in the form below. We will be happy to respond you!
DOWNLOAD