Best diff tool to compare Excel files for differences
How to find differences in Excel files or sheets
xlCompare: fast and efficient excel diff tool
Using Excel File Comparison Tool helps you to reach your business goals faster
In the modern world - this is important competitive advantage
xlCompare: difference report
15+ years on the market
With a team consists of industry experts we developed a powerful and reliable best excel comparison tool to
bring your productivity to the highest level. Armed with xlCompare, you will compare Excel files for difference in seconds.
Actual value of this tool is a cost of your time, spent on the spreadsheet comparison tasks. xlCompare is more than a regular Excel file comparison tool, it is your partner!
I would like to see how it works
Contact us if you would like to see the demo. Our team will be happy to introduce xlCompare features and options for you on the real examples.
Compare Excel files using xlCompare
xlCompare finds changes in
- Cell Values
- Formulas
- Array Formulas
- Background Color
- Text Color
- Cell Font
- Borders
- Number Formatting
- Text Alignment
- Hyperlinks
- Conditional Formatting
- Data Validation Rules
xlCompare is a tool that performs detailed worksheet comparison and gives you perfect color coded comparison report
Compare cell values in two Excel worksheets
Highlighted changed cells on the Excel worksheet
xlCompare compares all corresponding cell values in the worksheet columns.
In xlCompare you can ignore specific characters, ignore case, use numeric tolerance.
This excel diff tool gives you as much flexibility in comparing cell values for differences as possible.
Compare Formulas on the Excel sheets
Different formulas with highlighted changes
Formulas are compared on the function level. xlCompare divides formula into the logical expression and compares corresponding parts.
This algorithm allows xlCompare to determine and reject changes caused by inserted and deleted rows.
Comprehensive Color-Coded Difference Report
One of the most important benefits you are getting from xlCompare is a native and clear representation of the comparison results.
Difference report for two compared Excel files (Left Panel)
Highlight updated cells
All cells that was updated in your Excel files are highlighted with orange color. Tendency Icon before numeric value indicates increase or decrease of the value.
Highlight new rows
All rows and columns that doesn't have matching item on the corresponding worksheet is highlighted with color. Left row receives green color, right row receives red color. This makes unique items are clearly separated from other data on the worksheet.
Align rows on the worksheet
xlCompare inserts space into the worksheets to synchronize new rows. This feature allows to display modified cells on the same places on both worksheets. You should not look over your file to find the corresponding cells. Just look into other panel - corresponding cell is on the same place!
Difference report for two compared Excel files (Right Panel)
Display corresponding value in same cell
xlCompare has an option that displays original and updated cell value in the same cell. You should not look into the other panel for the correspondent value. You are getting it immediately.
Display difference below cell value
In addition to the option described above xlCompare is able to display difference instead of corresponding value. If you need to know how your value was increased - this option will save your time!
How to identify changed cells in the difference report
Clear representation of the discrepancies found between two Excel files is a major part of the xlCompare. Look into the image below to see how we
introduce modifications found in your Excel files.
Updated cell gets orange background
Updated cells
New and Removed rows get green and red background
New Rows in diff report
All cells are aligned on the worksheets
Aligned Rows on two worksheets
Difference explorer with legend
Difference Explorer
Sheet Tab reflects number of differences on each worksheet
Sheets Tab with number of changes
Sheets without diffs are hidden
Sheet Tab in the Report Mode
Export Comparison Report into PDF or XLSX
After you compare two sheets in excel, you get Comparison Report for your worksheets and VBA projects. xlCompare allows you to save it in several formats.
Export difference report into PDF file
Save comparison report as PDF
How to save report you see on your screen, including tendency arrows, color formatting, modified cell values, etc? Excel worksheet doesn't allow to save data with this formatting options.
xlCompare offers you to save this color-coded comparison report in the PDF format, that will look identical to the comparison report you have on your screen. This method is ideal for presentations or to
explain difference to your colleagues.
Save comparison report as Excel file
xlCompare gives you outlined report with list of the differences found between processed Excel files. It contains summary worksheet and list of updated cells and added rows, that are grouped on the other worksheets.
This is perfect way to save comparison report on your disk.
Export comparison report as HTML
xlCompare uses all power of the HTML language to create presentation for you and save it on disk in the HTML format.
xlCompare in the action
Find and highlight duplicate rows on two Excel sheets
One of the common tasks of the Excel users - filter matching rows on two Excel worksheets and find updated cells in the matching rows.
xlCompare easily does this job. If you have your worksheets compared, it is very easy to introduce you matching records.
xlCompare give you ability to extract these records from the worksheet and to save them as another file.
Filter matching(duplicate) rows on two Excel worksheets
xlCompare gives you set of filtering commands. Use Matching rows command to get only rows that have corresponding row on the other worksheet.
Get unique rows in two Excel files
Use Filter | Unique Rows command on the Ribbon, and xlCompare will show you only rows that doesn't have the matching item on the other sheet.
Get completely equal rows on two Excel worksheets
Filter | Equal Rows command leaves only identical rows on your screen
Save filtered rows you need on disk
After you've filtered rows you need, use Save | Only Visible Rows command from the right click menu on this sheet. This is quick and easy way to save filtered data in the other file.
Synchronize two lists in Excel files using xlCompare
Imagine the following task. You have two Excel files. First one is a table exported from database into XLSX format, that contains list of users from your company. And another one that contains only few e-mail addresses, exported from other table. Second file is just a list of emails without any other information.
You need to quickly filter all the users in the first Excel file, that are present (not present) in the second list.
Let's see how xlCompare does this.
Image below shows these files opened in the xlCompare. So, you can see what data it contains.
You will have similar image after you drop your files into xlCompare.
Filter worksheet columns that should be compared
We need to compare column A in the first file with column A in the second one. All other data should not be compared.
Use Ignore Columns command on the Home tab of the Ribbon.
Uncheck the columns B and C to ignore them, and press Ignore Columns button again to exit from this mode. Also, you can exit by pressing ESC button on your keyboard.
On the image above columns B and C are marked as ignored.
Compare Excel files for differences
Now we are ready to process these files.
Use Compare Files button on the Ribbon. It opens Comparison Wizard. In this window you can review what worksheets are compared, and which algorithm is used for every pair of worksheets.
xlCompare creates list of pairs and assigns the most appropriate algorithm. So, usually you don't need to select anything in this window. Just close it by using Compare button.
Now you have comparison results on your screen.
Press Close button to get to the comparison report.
Every row that is not present in the file2 is marked with green color and added to the difference explorer into the group of Unique Rows.
Filter rows that are present in both Excel worksheets
xlCompare has set of filtering command on the Home tab of the ribbon. They become available after you get comparison report on your screen.
Apply the Matching Rows filter:
Now we have only rows that are present on both Excel worksheets.
Filter rows that are not present in the right Excel file
In case if you need to exclude rows in the list from the global table of your users - apply the Unique Rows filter.
Now xlCompare displays only green rows.
Export filtered rows into Excel file
How to save the data we've got on disk?
Use the right click menu on the worksheet you would like to save and apply Save | Only visible rows command.
It asks you for a file name and saves only rows you have on your screen.
Conclusion
There is a reasonable comment: Excel user can do the same using VLOOKUP worksheet function and he can automate this procedure using VBA macro. Also, he can adjust the procedure to his needs.
This is absolute truth. Everything that was described above can be done using VBA macro and worksheet functions.
So, what is the reason of using xlCompare in this case?
You need to spend you working time to configure VLOOKUP properly or to create a Visual Basic macro.
In xlCompare you need to do only a few clicks and get results on your screen.
There is a minimum risk of error and very quick and accurate results.
If data in your second list contains extra characters you can ignore them in the xlCompare Options. But without xlCompare you need to pre-process your Excel worksheet first.
The most important benefit xlCompare gives you: saved working time and greatly simplified process of data extraction. With xlCompare you don't need to learn VLOOKUP, UNIQUE and other worksheet functions at all. Just do a few clicks and get the results!
Filter worksheet data you want to compare with Excel diff tool
Reduce comparison noise by selecting cell and formatting options included into comparison.
Define comparison strategy which is most suitable for the data you have
Exclude Hidden Rows
If you don't need to compare some rows on your worksheet - just hide them. And they will be appear in the comparison report. Quick and easy way to exclude data from the comparison.
You don't need to compare some columns
Use Ignore Columns command on the Ribbon. Use this command to exclude data in the selected columns from the comparison report.
Compare only Formulas on the worksheets
Go to the Options | Comparison and uncheck calculated values and entered values. In this case xlCompare will process only cells with formulas.
Compare only constant cell and ignore calculations
Like in the previous item, go to the Options | Comparison and uncheck formulas and calculated values. This will point xlCompare to entered values (constant cells) only. Use this command it you want to compare input cells in your models.
Compare cell formatting (or ignore it)
By default xlCompare finds differences in the cell formatting on the Excel worksheets. Sometimes this could create a lot of comparison noise, if you are looking into the different values only. In this case you can ignore cell formatting options on this page and ignore fill color or borders from the comparison.
Find Unique and Matching rows between two Excel worksheets
Find matching rows in two Excel files is a very common task for Excel users.
xlCompare solves it in just a few clicks and gives you ability to export matching rows into another Excel file.
Drop your files into xlCompare and get the comprehensive report for two compared Excel files.
Apply Filter Matching Rows commands and get only equal rows on your screen.
If you need to export rows present in both Excel worksheets for future processing, do the following:
- Right click in one of the worksheets
- Use Save | Visible Rows command
As you see xlCompare is very easy to use Excel Diff Tool, that saves you a lot of working time.
Combine Several Excel Sheets into One
Compare cells on 2 or more Excel sheets and combine them into single sheet with Merge and Bulk Merge commands.
Merge Excel Files
Combine contents of the opened Excel files into one file. Contents of the worksheets with same names are joined into one sheet. Use this command to consolidate Excel files that have similar structure into one.
Merge Worksheets
Join records on two opened Excel worksheets. This is very simple command, that just copies data from one sheet into another one and preserves cell formatting.
Merge Files in Folder
Use this command to merge all Excel files inn specific folder into one. This command works flawlessly if your files have same structure. In this case after executing it you will get your files merges.
Combine Excel worksheets into one
This command combines contents of the selected Excel worksheets inside one Excel file into one sheet. Use this command if you've received an Excel file, that contains dataset splitted into several worksheets.
Update Files
This command is something like a "smart" merge command. It compares two Excel files for difference first and update right file with modifications found in the left one. It doesn't copy all records from the left file into right one.
Only modified cells and new rows are copied.
Update Worksheets
This is same command like Update Excel Files described above. The only difference, it is applied to worksheets that are opened in the left and right panels. This means that you can apply it to the worksheets in the same Excel file.
Merge multiple Excel files or sheets into one
One click mass operations are essential part of the software, designed to quickly merge contents of the Excel files in the various ways.
If you are working with a big datasets:
You needs in quick and powerful merge commands which allows to manipulate whole sheets.
xlCompare gives you Bulk Merge Operations - you can resolve all conflicts found by comparison in single click. This greately increases your productivity if you are working with large amount of data.
xlCompare gives you Bulk Merge Operations - you can resolve all conflicts found by comparison in single click. This greately increases your productivity if you are working with large amount of data.
By choosing xlCompare as your tool to merge excel files, you can solve following tasks:
- Merge Excel files into one
- Merge Excel sheets in one file
- Combine multiple Excel files into one file
- Merge Excel sheets from command line
- Merge Excel files in the batch mode
Compare two columns for differences on the Excel sheets
You can use xlCompare for a number of spreadsheet comparison tasks.
Look into the article that describes how to find differences in two columns and compare specific segments of your worksheet tables.
- Ignore specific columns when comparing Excel sheets
- How to exclude columns from the comparison
- How to mark column as Key in Excel file
- Change(clear) selection of the Primary Keys in Excel file
- Link two columns when comparing Excel files
- Clear linked columns in the Excel files
- Should I sort values before comparing columns on Excel sheets
- Delete columns in the difference report
REDDIT ARTICLES
- Xlcompare for version control
- What's the best way to compare two CSV files and instantly view the differences?
- Freeze key columns to simplify browsing the difference report
- Key, Standard and Ignored columns in xlCompare
- Compare XML Files and highlight differences in a form of tree
- New Spreadsheet Compare tool
QUORA ARTICLES
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!
External Diff Viewer for Excel and CSV files
Diff Viewer for Version Control Applications
2 and 3-Way Comparison options with set of command line options allows to integrate xlCompare into Source Control System as Diff Viewer and Merge Tool.
Compare Files with Common Base Version
Use 3-Way comparison mode to compare changes made by you and your co-worker in the same version of workbook.
Resolve Merge Conflicts
One of the most complex version control tasks with Excel Workbooks - is to resolve conflicts between different versions of the same workbook.
xlCompare compares 3 versions of Excel workbook - YOUR, BASE and THEIR and gives you comprehensive report of the changes made, with ability to
merge changes and resolve conflicts.
External Diff Viewer for SVN\GIT Tools
We offer rich set of the command line options, which cover requirements of the SVN\GIT Applications.
Compare three Excel files
You've got 2 Excel files from your collegues. How to see modifications, made in all of them to imagine the entire picture? xlCompare is a tool that compares 3 Excel files for difference.
Put your copy in the central panel and 2 other copies on the left and right sides of the application and get the difference report.
Resolve conflicts between different versions of same workbook
When working on the shared workbook with your colleagues, you often get conflicting changes in Excel cells. Few seconds and you resolve them with xlCompare.
Highlighted Conflicts
All conflicted cells are marked by colors exactly in the workbook. Conflicts has red color to be more highlighted then other diffs.
Synchronized Rows
Inserted\Deleted rows are virtually inserted into same place in the corresponding workbook. This gives native look for the modified sheets. Conflicted changes appear one versus one.
In-place Tooltip Report
Put cursor over the conflicted cell to get detailed report about the changes made.
Command line mode
xlCompare supports standard set of command line arguments (switches), that allows to perform all comparison and merging tasks in the batch mode. This feature is a basis of the integration of xlCompare into the any external application, like SVN or GIT.
Please, review this page to find detailed information on all command line parameters supported by xlCompare:
Compare Visual Basic forms and macros
Compare Visual Basic Modules, Forms and Controls
xlCompare performs detailed comparison of the contents in VB Projects in your Excel files. Compare changes in the Class and Code Modules and Form Controls.
Compare text of your Visual Basic macros.
xlCompare is a must have tool for VBA developers!
Compare Visual Basic macros
xlCompare compares all vba macros in your Excel files for difference. Every module is compared with correspondng one on the function level. What does this mean?
xlCompare compares text of the function only with text of the same function in the other file. This is the only method to get accurate comparison results.
Complete comparison of the Visual Basic projects
Our purpose is to point you to every difference in your Visual Basic projects. That's why xlCompare compares VBA macros and form controls in the Visual Basic projects in the Excel files.
Merge Visual Basic macros in the Excel files
In addition to comparison, xlCompare give you commands to merge differences in the VBA macros in your Excel workbooks. With xlCompare you will easily populate one of your files with changes made in the other Excel files.
xlCompare is an ultimate spreadsheet compare utility for a VBA developers and Excel programmers who work in the team environment.
From the first look VBA projects are text files, that should be compared like regular text documents. Yes, sure you can use this method.
xlCompare detects changes in the VBA code on the function level. This gives better and much accurate results in comparison to alternative methods.
xlCompare gives you a powerful tool that does all you need to compare and merge Visual Basic code in the Excel files.
Compare and Merge VBA Forms
Compare and Merge VB Forms and Controls in a visual, native form. Just in a few clicks you get all changes applied to specific form.
Compare VBA forms in the Excel files
xlCompare finds differences in the Visual Basic Forms in your Excel files and presents these changes to you.
Merge controls on the VBA Forms in the Excel files
One of the common problems for VBA developers - how to merge modifications on the VBA forms in the Excel files. xlCompare is tool that displays comparison results in the native form and allows to you merge controls in the clear and visual form.
If you've added new control to VBA form - xlCompare will present it to you and allow to merge it as simple as a click on the checkbox control.
Compare and merge control properties
Imagine that you've changed background color on the label control on the VBA form. xlCompare will point you to this change by highlighting this item in the list of control properties. One click on the
checkbox attached to this property in list, and this color is applied to the corresponding control.
Compare and display hidden controls
This gives ability to display contents of the Form in it's native way. You see form exactly as it looks in Excel application.
All form controls are combined into hierarchical tree structure - you can see it in the top-left pane of the editor.
Properties of the selected control (Name, Font, Colors, Tab Index, Size and Position, ...) are below tree control in the list.
Choosing Best Excel File Compare Tool
Are you looking for Best Excel Comparison Tool?
How to compare Excel file comparison tools on the market and choose the best one
If you search for Excel file comparison tool - Google gives you 5 or more tools you can choose to compare Excel files for difference. Which one is the best Excel file comparison tool?
You don't need to analyze spreadsheet comparators market yourself to find the top excel file compare products. Just investigate the list of xlCompare features. Excel diff tool xlCompare offers the most complete set of spreadsheet comparison and merging features to its Excel users.
If you are working with spreadsheets that have identical structure and relatively small size - Excel itself is the best in this business. You can compare such files using one of the instructions on this page, without using excel diff tool.
But for complex spreadsheets, that have unique rows and columns, or if you are doing this regularly - you need an excel diff tool. And this is the best place where to find robust and simple in use utility - Download xlCompare.
Key differences between xlCompare and other Excel file comparison tools?
All Excel diff tools solve the same task - compare Excel files for difference and display results for the comparison. Results are higlighted with colors. From this point of view all Excel diff tools are identical. But there are details, that helps you to choose one tool over others.
Using side-by-side view to display worksheets
This feature is used by all Excel diff tools. This is the best and most suitable layout to present results in the compared Excel files. xlCompare gives it to you by default when you start it. While other tools need to configure Excel application to tile worksheets vertically on when they start the comparison.
How we report changed cells in the compared Excel sheets
Just look into the image below to see how xlCompare displays you information about changed cell on the worksheet.
You see original and modified value, difference and tendency arrow that indicate increase or decrease of the cell value.
Align matching rows on the worksheets
Highly important feature. xlCompare inserts virtual rows into worksheet, where corresponding one contains unique rows. So, when you browse diff report matching rows are always one vs. one. This greately help you to analyze comparison results, when you've compared two Excel files for difference.
Commands to merge changed cells between Excel files
Every changed cells and new row(column) has merge button. Just press this button and call value(formula) or entire row is copied into the corresponding Excel file. You don't need to play with copy and paste commands to move the worksheet data frome file to another.
Ignore columns on the worksheet
Imagine, you don't need to compare data in some columns. What to do? xlCompare gives you easy way to uncheck the columns you don't need. Easy and quick!
Filter compared worksheets by specific segments of data
Compare only portions of your worksheets that belongs to specific segment of your data. xlCompare supports auto filters on the worksheets, like Excel application. So, you can filter rows on the worksheet by any criteria.
With this feature you can put your focus into selected data.
Compare VBA forms for difference
In this component xlCompare is unique tool. No other Excel files comparison tools compare forms and controls in the Visual Basic projects. You may say that most of the Excel files comes with data in the XLSX or CSV formats.
But there are a lot of Excel developers around the world and we can't ignore their needs.
Excel diff tool with command line mode
Integrate xlCompare tool into SVN or git software as an external Excel diff viewer. xlCompare has all you need to resolve merge conflicts, while most of the Excel based tools doesn't provide you command line mode.
Compare three Excel files
In some cases you need to compare three or more Excel files for diffs. What to do if your tool allows to compare only 2 Excel files or sheets? This is not a problem for xlCompare. It compares 3 Excel files and synchronizes changes.
You are getting report that points you to common and different records in your Excel files. Also it highlights conflicted cells and rows, if they were changed in all three Excel files.
Competitive advantages of the xlCompare tool
Our purpose is to give you best excel diff tool to save your working time and boost your productivity to the highest level.
Simplicity
Simplicity reveals the beauty of xlCompare. Advantage of xlCompare is not only to efficiently compare or merge Excel sheets or files, but to do this process as simple and convenient as possible.
xlCompare works in the following way:
- Drop your files into xlCompare
- Get the clear results
We've minimized number of settings and options you should use to get the results by using several AI algorithms.
You should not spend you time to select various comparison settings to get the results.
Fundamental tool
As a professional Excel/VBA developer, XLCompare has become a fundamental tool in my development process. It works on even the most code-heavy and complex of workbooks, and is something I just couldn't do without.
Peter Hamilton
Comprehensive Comparison
xlCompare is fast and best performing Excel file comparison tool. Excel file comparison tool detects all modifications in your Excel files. Formulas and macros are compared on the function level - this gives you best and accurate results when you compare Excel files.Compare three Excel files
xlCompare is an ultimate Excel Diff tool to compare and merge all kinds of Excel files (XLS\XLSX\XLSM\CSV\TXT\...). Compare multiple Excel files for difference. Integrate Excel file compare tool into SVN and GIT tools and source code control systems. Command line mode allows to integrate xlCompare into any third-party application that requires Excel diff checker. Use xlCompare to resolve merge conflicts between different versions of the same Excel file (3-way compare and merge for Excel files).Compare Excel files for diffs by key columns
xlCompare is the best Excel file comparison tool, that finds differences in two Excel sheets or files. xlCompare is an ultimate spreadsheet compare utility for Excel and CSV files. xlCompare solves a wide range of common tasks, that every Excel user has. This Excel file comparison tool gives you set of commands to define and edit Primary Key Columns on your sheets, to get the best comparison results for Excel tables. xlCompare (Excel file comparison tool) gives you all changes made in your Excel files, extracts unique rows from your Excel tables, filters updated and matching rows within two Excel worksheets, compares and merges CSV and XML documents, and more ...ExcelDiff tool with command line mode
Use xlCompare for SVN\GIT integration. If you are working in the multi-user environment and have SVN\GIT system installed - xlCompare is a perfect excel diff viewer and merge tool for XLS\XLSX\XLSM\XLA\XLAM files and CSV\TXT files. Easy integration allows to use it with most popular source code control systems.Merge cells and entire rows
xlCompare is more than just Excel file compare tool. It gives you commands to merge inserted rows and modified cells between sheets in one click. Easy to use merge commands copy all new rows and edited cells from one worksheet to another in just a second. Worksheet consolidation was never being so easy. Just in one click you can combine several Excel files into one.Color-Coded Diff Report
In the Extended View mode xlCompare shows original and modified value in one place. xlCompare shows you difference between two modified cells with tendency icon, that indicates increase or decrease of the value. In the modified formulas we highlight edited characters. xlCompare puts your focus directly to the changed data.Filter matching rows
Get identical rows on two Excel worksheets by using Filter commands on the Ribbon. To extract these rows and save them on disk use Save | Visible Rows in the right click menu. xlCompare performs various operations with Excel worksheets. This tool is very useful for Excel users. It will save a lot of time.Rich set of options
xlCompare has a lot of options you can use to do your work much faster. Comparison accuracy, ignore specific characters, exclude hidden rows and columns, exclude calculated values, ... Application has long list of features you can find here: xlCompare Features
Very nice software
Very nice your software, the 3 ways blowed my mind out, I did a 2h job in 10 minutes (I looked 5 sec at my screen without moving because I was so surprised).
Olivier Regout
xlCompare and Spreadsheet Inquire Add-in
When do you need xlCompare tool and when you can use free Spreadsheet Inquire Add-in
Some versions of Microsoft Excel include Spreadsheet Compare Add-in, that is called Spreadsheet Inquire in the list of Excel Add-ins.
What is Spreadsheet Compare Add-in
Spreadsheet Inquire is available in the Office 365 Professional. It is not available in the other versions of the Microsoft Excel 365. This is COM Add-in that compares Excel files for differences.
It is installed with Microsoft Excel, so it is free for you if you already have Microsoft Excel 365 Professional on your computer.
Main differences between the Spreadsheet Compare and xlCompare
Spreadsheet Compare Add-in is a perfect tool that finds different cells in the Excel files. If you need only to find changed cells in your Excel models, probably this is good and working solution. In this case you don't need a professional tool like xlCompare.
We've compared 2 small Excel files with Spreadsheet Inquire Add-in:
You are getting highlighted differences and summary report in the bottom part of the window.
Below screenshot of the same files, compared with xlCompare:
xlCompare compares these worksheets by primary keys and gives you more accurate results:
- Row with Orange was highlighted, even it is present on the other worksheet.
- xlCompare uses several types of colors to highlight the differences on the worksheets. This greatly increase readability of the report.
- xlCompare displays tendency (increase or decrease of the numerical values).
List of the differences
This one you are getting in Spreadsheet Inquire Add-in
This one is a summary of changes in the xlCompare
- xlCompare detects inserted and removed rows, while the Spreadsheet Inquire reported only changed cells.
- xlCompare allows to collapse branches in the report tree. This simplifies navigation if you have several worksheets compared.
Processing unsorted data
Spreadsheet Inquire works incorrectly with unsorted data:
Probably, you need to sort the data on the worksheet yourself before comparison.
Other benefits of xlCompare
- merge cells between the worksheets
- command line mode
- compare multiple Excel files at once
- filter comparison results
- evaluate and debug formula logic
Conclusion
If you need only to find a modified cells on your worksheets - Spreadsheet Inquire Add-in is a good solution that will help you.
In case if you need to merge changes made in the Excel files, extract rows that are present on the other Excel worksheet, configure your spreadsheet compare tool as diff viewer for Excel files - xlCompare is your reliable partner!
xlCompare vs. On-line tools to compare Excel files
What are the reasons to choose xlCompare if there are on-line services
There are several services on the web that offer free spreadsheet comparison. Just drop your Excel files into your browser and in a few clicks get your files compared.
This looks easy. If spreadsheet comparison is so simple, that can be done using online diffchecker tools, why several companies develop their desktop applications for comparing Excel files?
Key benefits of desktop spreadsheet compare tools over the on-line web services:
1. Load large Excel files faster
xlCompare uses own spreadsheet engine, that loads Excel files faster than Excel application.
2. More complex comparison algorithms
Desktop applications have more flexibility in memory management and data processing. Everything that is implemented in the desktop app can be done in the diffchecker created with javascript. But will the performance capabilities be the same?
3. Highlighting the Results
Web browser limits formatting options in online difference checker. On-line tool can use only html features to display Excel files and highlight the results. xlCompare is much more flexible in this:
Output produced by on-line difference checker tool
Comparison summary produced by xlCompare for the same Excel files
4. Filter changed cells
Online diffchecker tool doesn't allow you to filter rows in the compared worksheets.
5. Merge cells commands
xlCompare gives you several tools to merge changes in two Excel files. You can quickly transfer data between your Excel files. This is impossible in the web-based tools.
Conclusion
On-line diffchecker services for Excel files are good and robust tools, that you can use to find differences in Excel files. But their visual capabilities and functionality of significantly less than functionality of xlCompare and other desktop spreadsheet comparison tools.
If you are looking for a tool you can use as diffchecker for XLSX files in your SVN\GIT environment - on-line services are not an option at all.
Make a conclusion on choosing best ExcelDiff tool
The most important benefit you get from xlCompare is simplicity. This tool transforms complex and routine actions into simple steps and gives you results without any efforts!
Complete comparison
On the Features page we've published a summary of the features collected from excel comparison tool reviews. xlCompare covers all of them.
Biggest set of options
xlCompare has more options than every other tool on the market. This gives you maximum flexibility in checking your sheets for difference.
Best performance
xlCompare is based on our Spreadsheet Core engine, that opens Excel workbooks faster than Excel application. By working with core objects and state-of-the-art algorithms we give you best performing tool on the market.
Focus on the efficiency
Spreadsheet Tools is not a seller of the Excel file comparison tool. Our company is your reliable partner, that makes you business processes more efficient. Saved working time is a primary benefit of the xlCompare, that is usually hidden by the comprehensive Excel file comparison.
Continuous development = Reliability
Regular updates give you stability. This is important. We continuously improve the software and publish updates for xlCompare. Look into History of Changes to see the list of regular updates. We develop this software more than 15 years and will continue adding new features and options. Development of xlCompare will never stop!
No extra payments = Minimum expenses
We don't use Software as a Service model to distribute our software. xlCompare is not a service, for that you need to pay every month. You pay only once for the license and get permenent license and updates and support included. No extra payments! This is important in the modern world, where companies are working on optimization of their business processes.
Please review this article on our website. Here we are discussing Excel file comparison (ExcelDiff) tools:
Contact us if you would like to see the demo. Our team will be happy to introduce xlCompare features and options for you on the real examples.
BONUS: Free functions in xlCompare
Number of tools you are getting for free with xlCompare
xlCompare - is more that usual Excel diff tool, that compares Excel files and gives you report with highlighted changes.
Our Excel file comparison tool gives you set of extra commands for free. For example look into the article below to see how you can debug formulas in your Excel models.
All commands that trace precedents and analyze calculations are available in the Evaluation Version of Excel file comparison tool and also works in the expired version of the Excel diff tool (xlCompare).
Excel gives you commands to evaluate formulas and see how it is calculated. But, try using Excel diff tool (xlCompare) for this and you will never give it up!
All the commands, that are described in this section are AVAILABLE FOR FREE in xlCompare.
Even if you don't have xlCompare license you can install the software and use for FREE.
Debug and Explore Formula Logic
Complex Excel models has thousands of formulas. It is a real pain to find out how one cell depends from another.
xlCompare gives you a powerful Formula Evaluation tool, to view and explore internal structure of the formulas in your model.
xlCompare displays formulas, their location (address) on the worksheet. Returned value of the every function. And all parts of the logical structure of the formula.
How Output cells depend from Input cells in your model?
xlCompare offers you a Find command in the Evaluate Formula window.
Just open Evaluate Formula window on your output cell. Press Ctrl+F and enter Input Range into the search bar. It Enter and in a few seconds xlCompare will give you an answer!
xlCompare does complex work in a seconds and absolutely free!
More about formula evaluation
Additional information about evaluating formulas in xlCompare is present in this topic on the MrExcel forum:
Number of free features constantly grows and we plan to increase it constantly in the future!
FAQ: Find Answer to your question
FAQ: What are the options I need to know
If you open Options window in the xlCompare you may be a bit confused - this application has a lot of options. How to learn and use all of them.
xlCompare works with different kinds of worksheet data and gives you a number of options to tune up the comparison results. Most of the options changes some visual options of the software or allows to change the formatting or delimiter for text files.
All these options are rarely used and 90% of users normally don't change the default behavior. However, there are 2 pages you need to know about.
Comparison Rules
This is the most important page in the options. It defines set of rules used by the application to compare data on the Excel worksheets.
Here you can allow to ignore some minor typing mistakes in string values, ignore case in the text cells, ignore specific characters and specify the numeric accuracy.
Allow key values to be changed on your worksheets
Imaging that you are working with 2 worksheets where one file contains record about John Smith, and in the second file there is a typing error and name was imported as John Smitn.
xlCompare highlights you this difference as 2 unique rows - one colored with green and another one colored with red.
But this does not work for you, as you need to compare data for the John Smith from both files.
Use this option to allow difference in 1 character in the compared files. This will force xlCompare to think that both values are identical.
However, xlCompare will point you to the change in this cell. Both rows will be considered as matching and will be compared one versus one. This cell with username will be highlighted with orange color.
Rows that begin with N equal cells are identical
Imaging two rows where most of the cells are different. Should xlCompare consider them as unique and highlight with green and red fill color, or mark them as matching and compare one versus one?
This option is one of the rules that helps xlCompare to make the decision.
You can specify number of equal cells on the beginning of the row, that should advice xlCompare to that these rows are matching.
Rows having N or less different cells are identical
Next rule that solves same task - you manage the process of finding the matching rows.
This rule defines a limit, where xlCompare should think that rows are different or matching.
This is very simple criteria, and it can't be used in the general case, but you can use for the pair of specific worksheets to get the most accurate comparison results.
Case-insensitive comparison
You've got 2 worksheets where one contains record for John Smith starting his name from the capital letter and in the other file his record was imported as john smith.
But using this option you can ignore this difference or make it important and xlCompare will highlight it for you.
Ignore space characters
Again, this is a way to adjust the results of comparing text values.
Extra space in the username leads to thinking that this value is different. Trailing spaces in the values exported from the database or copied from the webpage produce a lot of unique values on the worksheets, instead of comparing them as matching.
So, this option is enabled by default.
But if you need to perform strict comparison to the find the exact compliances, you can turn it OFF.
Ignored Symbols
Here you can enter symbols that are not important.
Imagine that you've got two files, imported from the external database and one of them contains a lot of @ and # symbols on the character positions.
Should you edit all the file manually?
No. You need only to mark these symbols are ignored and that will not be considers when comparing string cells.
Comparison Options: what data is processed by xlCompare
On this page all the options speak for itself.
You can ignore formulas and compare only their values.
Ignore constant cell values (input cells) on the worksheets and compare only calculated values, or only text of the formulas.
Compare only cell values on the sheets by excluding text of the formulas.
Cell styles are often producing differences that are highlighted with yellow color. On the large worksheets you may got a million of the reported cells and all of them are not important for you.
Exclude styles from comparison and xlCompare will focus on the cell values and formulas. This is a great option to reduce the comparison noise.
Conclusion
As you see xlCompare has a lot of options.
We've configured them in the way that should give you best results out of the box, so you should not tune the software.
You should not learn all the options in the product. But these two pages are important, so we suggest you take a brief look at them. Possible sometimes this information will help you.
FAQ: Ignore Case when comparing String Cells
Does the xlCompare distinguish small and capital letters on the worksheets?
Yes, sure. xlCompare takes case of the letter into account. However, this is managed by the option.
In the typical case strings like Account and account should be marked as identical. When you are getting data from the different sources, the most likely you will have a text value with mismatching case.
But if this difference is important for you - turn this option OFF and xlCompare will bring you all these differences. You will get all such values highlighted with orange color as regular changed cells.
Where to find this option?
It is located on the Rules tab on the Application Options.
FAQ: Link columns on the worksheet
You've compared 2 workbooks for differences and some columns was not marked as matching.
For example, first file contains column Product while second one contains value Item Code. Both contains code of the product. Easy workaround is to rename column name in one file to fix the issue. But xlCompare offers you general way to solve this task.
This feature is called Link columns
What does it mean?
When we say link columns, we mean that you say to xlCompare that selected left column should be compared with the selected right column. No matter of what positions in file these columns are located and what is the heading of the columns. They are always compared one versus one.
How to mark two columns as linked
Select column you would like to link in the left file.
Right click on its heading.
Select Link command in the context menu.
The opened Link dialog shows you corresponding worksheet.
Select column on this sheet and click the Link button.
Now Link icon appears on both column headings. This means that columns are linked.
When you put your mouse over the column it shown you name of the corresponding one. So, you can check how you've linked the data.
After columns are linked, xlCompare compares their cell values in the corresponding rows.
Can I apply this option when comparison report is active?
Yes sure. You can link columns when you don't like how data was compared and xlCompare will automatically refresh the comparison report.
How to clear the links
The same context menu contains other command Clear Links ... it removes all links on the opened worksheets.
When do I need to use this option?
First case, when columns names doesn't match. In this case just link the columns and get the results you want.
Second one, data in the particular columns is very different, so xlCompare treats them as Unique and highlight with green and red colors. In this case there is no workaround with editing heading cells. Just mark two columns as linked and xlCompare will consider them as corresponding ones.
Is this option important?
Absolutely. Every equal or different cell in the row is important - it effects on the decision if two rows are matching or not.
For example option that marks rows equal if they have equal N cells on the beginning of the row, depends on the count of equal cells. So, every equal or different cell value in this row may change the decision.
FAQ: List of xlCompare hotkeys
xlCompare uses several hotkey combinations you need to know to performs all the operations quickly.
Hotkeys are saving your time and makes you more efficient.
F6 Start the comparison
After you press F6 xlCompare will compare opened Excel files. Use this key after you've made a modification in your files and need to restart the comparison.
The difference between F6 and Compare Files command on the Ribbon - F6 doesn't show the comparison wizard. It just restarts the comparison using the previous settings.
F12 Close All Files
Close both files you have opened in xlCompare. If you have a comparison report on your screen - xlCompare close it also.
Ctrl+1
Set Zoom on both files to 100%. This allows to quickly restore zoom if you have changed it.
Ctrl+2 2-File comparison
Turn OFF 3-File comparison option. Switch to 2 panels mode.
Ctrl+3 3-File comparison
Turn ON 3-File comparison option. xlCompare opens additional panel for 3-rd file.
Ctrl+H Recent Items
History of the comparisons. This window shows you all recent comparisons.
If you double-click on one of the items - xlCompare reopens this comparison again for you.
Ctrl+K Edit Primary Keys
This hotkey opens window where you can edit primary keys on your worksheets.
Ctrl+L
Show or hide Left Panel.
Ctrl+R
Show or hide Right Panel.
These 2 options allow to display only one workbook on your screen. Use this option if you need more space for your data.
Ctrl+T (Alt+O) Options
Call the options window. Edit comparison rules, select what data is processed by xlCompare, change visual options of the application.
Alt+Z Zoom
Brings the Zoom window where you can change zoom applied to your workbooks.
Alt+F11 VBA Editor
This hotkey is identical to Excels combination that opens Visual Basic editor. xlCompare shows you contents of the Visual Basic project by pressing this key.
FAQ: How to tune up the comparison results
xlCompare tries to give you best possible comparison results out of the box.
You should not select options and modify your data to change the output. However not all the cases are so simple. In some cases, you can change the criteria used by xlCompare to make the conclusion about equal or changed data.
Here we will describe some common situations and give our suggestions.
Two columns containing almost identical data are marked as unique
This could happen if column headings are different.
If field names are not identical, they should be considered as different. This the normal behavior for the database fields.
Use Link columns command to bind one column to another in the comparison report and to modify this.
After columns are linked, they will always be marked as matching without checking their names.
Two keys that should be identical are highlighted as unique
Possible they contain extra space character or extra space value.
Check contents of two cells to make sure that they are identical.
Some keys contain typing mistakes
Use option to allow N characters in cells to be different. According to this rule xlCompare ignores limited number of differences in values and marks values as matching.
This changes the comparison results, because matching key values leads to marking corresponding rows as matching on the worksheets.
There are extra characters in strings
Your file is a result of the exporting from database and some cells contain special characters like $ of #.
Put this symbol into list of ignored symbols and xlCompare will not take them into account. They will be excluded from the values.
Rows containing a lot of changed values are marked as unique
When 2 rows don't match exactly, xlCompare should make a decision - are these rows matching or not.
On the Rules tab in Options window, you can change default behavior.
There are 2 options you need to use:
- Rows that begin with N equal cells are matching
- Rows containing not more than N difference cells are matching
For example, you are comparing price list or the cash flow financials statements. Possible only 1 or two cells in the first column are equal, all other cells that represent numbers are different.
These options allow to mark rows that begins with 2 equal cells on the similar positions on the worksheets as matching.
In this case comparison report will be different from the report with this option turned off.
FAQ: Working with small display
We are extensively using laptops in our business. Laptops gives us perfect mobility and flexibility. Armed with laptop you can work from anywhere. But this type of computer has significant disadvantage: small size of the display. While you can use 32 inches display on your desktop and work very comfortable, most modern business laptops will give you 14 or 15 inches.
So, if you are using laptop, you are very limited in screen size. Scrollbar allows you to see all the content, but much better to see as much as possible information on the screen without scrolling.
How xlCompare helps laptop users.
Collapse the Ribbon
Starting from xlCompare 11 you can collapse the ribbon to get more space on your screen. Ribbon takes some space on the top of the window and in the collapsed state it will give you several additional rows.
xlCompare window with expanded ribbon control
xlCompare window with collapsed ribbon control
To expand or collapse the ribbon use button highlighted with red arrow on the image above.
Hide comparison results
Difference Explorer window that contains the complete list of the differences occupies space on the bottom of xlCompare window.
In version 11 it is hidden by default. So, at least 30% of your screen is available for the worksheet data.
Turn off Extended View
Extended View is a perfect option that displays modified value below the original, that it very handy. But it duplicates height of the row.
If this option is disabled, all the rows keep their original size. So, you get more data on your screen.
To get the corresponding value of the modified cell quickly, put your mouse over this cell. Tooltip window will show you detailed information on the modification, including value and formula of the corresponding cell.
Use Zoom
Changing zoom from 100% to 80% has small impact to readability but gives you 20% more data.
Use Ctrl+1 hotkey combination to open Zoom window and select preferable zoom on for worksheets.
FAQ: Important commands
What is Extended View?
This is an important feature, which displays cell value(formula) and the corresponding value in same cell. With this option turned ON (default selection), you won't need to search for the corresponding value on the other panel. It greatly saves your time and increases the usability of the Comparison Report.
I need to see the Difference between changed values.
The Option Display Difference activates the feature you need in the Extended View mode.
With this option enabled, you can see difference between value in the MINE cell and value in the THEIR cell. The Colored Arrow mark indicates an increase or decrease in the value.
Can I save the report I see in the Extended View?
Yes, sure. You can export this report in a PDF format for future use. For example, you can print it or send to your co-worker.
Does xlCompare find differences in formulas?
Yes. xlCompare finds differences in the text of formulas, and the text of an array formula.
However, if you don't need to compare formulas, you can exclude this in the options. xlCompare gives you a rich set of options to control what you are searching for.
You can compare:
- All worksheet data
- Only constant cells
- Only calculated cells
- Only text of formula
- Only value of formula
- Combination of the options above
How to identify differences, found between two Excel worksheets?
All differences are listed in the Difference Explorer report. When you select difference in this list - xlCompare will activate it in the Worksheet\VBA Panel.
All unique rows and changed cells are marked with color - background, text color and border. Formatting in the equal cells will not be changed.
Can I ignore hidden rows and columns?
Yes.xlCompare has an option to exclude hidden rows and columns from the comparison.
If your worksheet has data you want to exclude, hide it and run the comparison. This is one of the methods to reduce comparison noise.
How to save the Comparison Report to present it to colleagues?
There are two options. You can print the Comparison Report exactly as it looks with all formatting into the PDF format, or use the Export button, with the PDF icon in the Difference Explorer.
It opens Print Preview window, where you can preview your report, change printing options - put all columns on one page, align printout, etc. and save it on disk.
This report perfectly presents all differences, found between Excel Worksheets.
Other option - Summary Report button in the Difference Explorer.
This command generates an outlined report, which contains all items you have in the Difference Explorer. It doesn't display modifications in-place, like a previous PDF report, but it gives you a complete summary of the comparison done.
Choose one of the following formats to save this report:
- XLSX
- HTML
- Text
Or just copy it to clipboard and paste it into e-mail or another application.
I have a question about this Excel Diff Tool
Please, describe your question or inquery in the form below. We will be happy to respond you!
xlCompare: International Resources
Excelファイルを比較する
엑셀 파일 비교
excel对比工具
Excel-Dateien auf Unterschiede vergleichen
「Spreadsheet Tools社と素晴らしいパートナー契約ができたことをうれしく思います。xlCompareは当社のドキュメントソリューションに貢献してくれるでしょう」
株式会社ヒューリンクス 常務執行役員 若林 登
[プレスリリース] ウクライナ Spreadsheet Tools 社との販売バートナーシップ契約締結について
www.hulinks.co.jp/info/202208_pr_xlcompare
10/26/22 - 10/28/22
