Compare three(multiple) Excel files for differences
Contents
- Compare two Excel files
- Benefits of xlCompare
- What is 3-way Excel file comparison?
- Compare three Excel files
- Compare 3 CSV files
- Compare 3 Excel sheets by viewing them side-by-side
- Conclusion: How to compare multiple Excel files
- Contact Spreadsheet Tools
Available in Microsoft Store©
Join Reddit Community
Find differences between three or multiple Excel files
Compare two Excel files(sheets) for matches and differences
Comparing Excel files for differences is one of the tasks that could cost you hours of time. Imagine a large report of thousands of rows, that should be compared with a report from the past quoter. If you are an Excel expert, you can probably do it yourself using VLOOKUP worksheet function or Visual Basic macro. Excel is very good in this business. But how to accomplish this task efficiently if you a one of the millions of usual Excel users?
xlCompare is a fast and efficient solution for comparing Excels and highlighting differences.
Professional tools are always more efficient than other methods – you should not enter formulas, create formatting rules, etc. Just give your files to the xlCompare and get the easy to understand and perfectly formatted comparison report.
xlCompare: difference report
xlCompare makes complex things simple!
Benefits you are getting by choosing xlCompare
xlCompare is an efficient solution that compares columns in Excel files and present you a color coded comparison report. xlCompare is a robust and powerful utility that can be important part of your business process if you are using Excel in your daily work.
Let's talk about benefits you are getting and look how xlCompare works when you need to compare cell in two columns in Excel files.
Simplicity
xlCompare is very simple to use. We've made a lot of efforts to make the application as easy and convenient as possible.
xlCompare is divided into 2 panels to give you native representation of the comparison results. Comparison operations is our specialty.
Our team communicate with our customers on the regular basis. We are improving interface using your feedbacks to make it more user friendly. If regular Excel user says that there are no confusing commands in the application - this is perfect result for our team.
Efficiency is a key benefit
Our primary goal is to make you efficient. We don't just give you a tool that compares columns for differences in Excel files. We save your working time and prevent from errors. This is important difference. Our tool makes you efficient. Actual value of xlCompare is amount of the time spent to spreadsheet comparison tasks multiplied by your hourly rate. And this doesn't include possible loss of profit due to spreadsheet errors.
Works with unsorted and unordered data
xlCompare perfectly works with unsorted and unordered worksheets. When you have a very simple spreadsheet, you don't need a tool to do your work. But in the real business process Excel file you are working with may have thousands of rows and millions of cells. In this case you need a professional solution that will greatly increase your efficiency.
You don't need to learn Excel commands
With xlCompare you need to do only a few clicks to get the results. This is our goal - solve your tasks with minimum efforts from your side.
Off course you need to learn several simple commands in the tool. This a minimum you need to do to get your job done.
Compare only the data you need
xlCompare allows you to hide the data you don't need to be compared. Also you can ignore some columns and have them visible in the comparison report, but they doesn't produce any differences.
Manage Columns window is a place where you can hide and unhide columns. Here you can mark your columns as ignored and link one column with other.
This is easy and convenient interface that gives you a control over processed data.
Highlight matching and different rows
Comparison report highlights differences with background color and with text color. This highlighting is virtual, it is not present in the worksheet. xlCompare places it over the actual data to present differences to you.
Highlight Changed Cells command applies these highlighting options to the actual cells.
All colored cells you see in the report will be permanently highlighted in the Excel file. Color formatting makes unique and updated rows easily identifiable in the saved workbook.
Comparison Accuracy and Ignored Characters
xlCompare is very flexible. You can setup accuracy to ignore minor changes in the numeric values. Or ignore some characters if data in one column doesn't match exactly.
Save changed rows on the other sheet
Very often you need to extract duplicate rows between two worksheets. Or you've filtered a subset of your data and it should be saved for future processing.
xlCompare has very handy commands in the right click menu:
Save | All Rows
This command saves all rows on this worksheet into separate Excel file.
Save | Visible Rows
This is a perfect way to extract data from the worksheet. After you've filtered the data you need, just use command to save this subset of rows in disk.
Just in one click you have your data exported into external file, that can be emailed, uploaded into cloud, etc.
Populate another sheet with missing values
Do you need to add missing values from another column into your sheet?
xlCompare has set of merging commands that populates one worksheet with values from another sheet.
Merge | Insert Rows From Left
This command inserts all unique rows from the left file into appropriate positions in the right file.
Merge | Insert Rows From Right
Similar command that populates left worksheet with rows marked as new in the right file.
Filter your worksheets to analyze specific segments of data
xlCompare supports table and filters, that you setup in the Excel files. Filter is a perfect way to manage your table by showing only the rows you need.
In the comparison report mode xlCompare synchronizes filters and gives you a way to quickly get a specific segment of data on your screen.
For example if you need to find updates in the Segment named Midmarket - just filter comparison report by this segment.
Next step - filter Midmarket segment by Countries and so on.
According to the previous topics, just in one click you can export filtered data into other file including highlighting.
More Information
Get more informaton on the Excel file comparison tool (xlCompare).
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 two(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 presentation 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 efficiently 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?
Compare three Excel files(sheets) 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 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.
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.
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!
XLCOMPARE.COM ARTICLES
I have a question about xlCompare
Please, describe your question or inquery in the form below. We will be happy to respond you!
Contents
- Compare two Excel files
- Benefits of xlCompare
- What is 3-way Excel file comparison?
- Efficiently compare three Excel files
- Compare 3 CSV files
- Compare 3 Excel sheets by viewing them side-by-side
- Conclusion: How to compare multiple Excel files
- Contact Spreadsheet Tools
Available in Microsoft Store©
Join Reddit Community