Spreadsheet Compare Tool
What is xlCompare and how it works. How this tool can be useful for you.
What is xlCompare?
xlCompare is a Windows application, designed to compare Microsoft Excel files for differences. xlCompare is completely independent from Microsoft Excel and works as standalone application. The software is based on the Spreadsheet Core engine, developed by Spreadsheet Tools. It allows to load and calculate Excel workbooks without involving Microsoft Excel components.
Tasks solved by xlCompare
xlCompare solves following tasks:
- Compare Excel worksheets for differences
- Compare Visual Basic macros for differences
- Compare Visual Basic forms and controls for differences
- Find and extract matching (duplicate) and unique records in Excel sheets
- Merge changes found between Excel files
- Merge several Excel files into one
- Combine worksheets into one sheet
- External diff viewer for Excel files in the SVN and GIT repositories
- Compare and merge Excel files from command line
- Analyze calculations in the Excel worksheets
The list above covers primary tasks, solved by xlCompare application. As you see, xlCompare performs complete comparison of the Excel files.
What are the benefits of choosing xlCompare?
- Simplicity. xlCompare transforms operations you need to do in Excel application into set of simple actions.
- Effectiveness. In just a few clicks you are getting the results. xlCompare saves your working time and efforts.
- Error free solution. xlCompare minimizes risk of error. If you are doing spreadsheet comparison manually, you have higher risk of error. Remember - companies lose millions of dollars every year due to errors in their spreadsheets.
- Incredible performance on the large files. xlCompare compares Excel workbooks with millions of rows in a few seconds and gives you comprehensive difference report.
Compare Excel files for differences
To give you best representation of the comparison results, xlCompare is divided into 2 panels, that displays workbooks:
To compare 2 Excel files for differences, drop your files into xlCompare window from the Windows Explorer.
xlCompare immediately starts the comparison and shows you a wizard where you can choose pairs of the compared worksheets, modules and forms and choose comparison options for every part of the worksheets:
After you complete this Wizard by pressing the Compare button, xlCompare gives you comparison report:
Bottom part of the window contains structured list of differences, that is grouped by worksheets and by type of the difference:
- Updated Cells
- Unique Rows
- Unique Columns
Worksheet Panels display color coded difference report. All differences are highlighted with background color:
- Updated cells with orange color
- Unique rows and columns in the left file - green color
- Unique rows and columns in the right file - red color
Formatting options (background color, font settings, border color) can be changed in the Application Options.
For every changed cell xlCompare displays Original and Modified value and tendency icon.
Tendency icon indicates increase or decrease of the numeric value. Red Down arrow icon means that value was decreased. Green Up arrow icon means that value was increased.
You can select option to display difference between cell values instead of modified value in the Application Options.
Filter Unique and Modified Rows
Compare tab on the ribbon contains set of commands, that filters data on the worksheet:
- All cells
- Only matching rows
- Only rows with updated cells
- Only unique rows
When filter is active, other rows are hidden. To get read of hidden rows on your worksheet, use Remove Hidden Rows on the Compare tab on ribbon.
How to merge changes on the Excel worksheets
Every changed cell and unique row(column) have a Merge button, that is displayed as arrow icon.
Press this icon to copy cell or entire row into the corresponding worksheet.
To merge range of cells at once, select it on the worksheet and press one of the merge buttons.
These simple commands allow you to quickly merge differences between Excel files.
Compare Excel worksheets by Primary Keys
Primary Keys are often used in the database tables, and should be used in the spreadsheets, that contains tables, exported from the databases.
xlCompare allows to define primary key columns and rows on the worksheet and select database specific algorithm in the Comparison Wizard.
To mark column or row as a key, select it and apply right click menu command: Set as Primary Key
To view and edit all keys in your Excel files use Edit Primary Keys command on the Table tab on ribbon:
How to compare only 2 selected sheets
To compare only two selected worksheets for differences, open them in xlCompare and use Compare Sheets command on the Compare tab on ribbon:
Compare Visual Basic Projects
xlCompare compares contents of the Visual Basic projects in the Excel files. It compares:
- Code in the modules
- Code in the forms
- Controls on forms
xlCompare has own viewer for visual basic project, that displays contents of all modules and form controls organized in a hierarchical tree form.
Compare three Excel files
One of the common cases is a three-way comparison. This means that you need to compare two Excel files that have the same base version. This situation often occurs in the SVN and GIT systems, where you are working in the multiuser environment.
This also could be three different Excel files, without marking one of them as base version.
Activate 3-file mode by using the 3-File command on the Compare tab on ribbon:
xlCompare displays you additional third panel and gives you a way to select third file:
Now you can select 3 files and compare them in the same way as you compare 2 Excel workbooks.
In the comparison report, created for three Excel files you can filter unique\matching rows and merge changes.
Merge Excel Files
xlCompare gives you following set of commands to merge contents of the Excel files. All of them are located on the Merge tab on the ribbon.
- Merge Files
- Merge Sheets
- Merge Folders
- Combine All Sheets Into One
- Update Files
- Update Sheets
What are the differences between these commands?
All merge operations work in the left to right direction. Data is moved from left sheet to the right one.
Merge Sheets. This command adds contents of the left worksheet into the right one. It works like a copy-paste operation.
Merge Files. The same command as Merge Sheets, but it is applied to all worksheets in two opened Excel files. Data from the left file is appended to the right file.
Merge Folders. Merge contents of all Excel files in a specific folder.
Combine Sheets. Use this command to combine all or several worksheets in the same file into one. xlCompare displays you a wizard where you can select which sheets you are going to merge and select worksheet that receives data.
The difference between merge and update commands is the following:
- Merge commands just appends all data from the source sheet into the target
- Update commands copy only changed values and unique rows. So, there will be no duplicate records on your worksheets after this operation.
Update Sheets. Compare two worksheets and copy all changed values and unique rows from the left file into right one.
Update Files. Compare all sheets in the opened files and copy only changed cells and unique rows into the right file.
In addition to the commands described above you can use merge commands on the worksheets, that appear in changed cells and unique rows\columns.
Create Comparison Report
After you get comparison results on your screen, you can save them into file on disk or create a PDF report.
Create Report command on the Compare tab on ribbon creates outlined report for the compared files\sheets and displays it in the separate window.
Report contains description of all changes found between Excel files.
You can export this report into XLSX and PDF format.
Integrate xlCompare into SVN and GIT applications
If you are using version control application to store Excel workbooks, you need a diff viewer application for this type of files.
All commands, that are available in the xlCompare are duplicated in the command line mode. So, you can configure your SVN to use xlCompare as a third-party external diff and merge tool for XLS, XLSX, XLSM, XLA, XLAM and CSV files.
To compare two Excel files from command line you need to use following syntax:
"C:\Program Files\Spreadsheet Tools\xlCompare\xlCompare.exe" %file1 %file2 /quit_on_close
File1 and file2 are paths to the files you are going to compare.
Quit_on_close key says to xlCompare to exit the application after you complete the comparison.
Complete list of the command like parameters is available on the xlCompare website:
Spreadsheet Core engine, that is a basis of xlCompare allows us to give you commands that simplify your work with spreadsheets.
Evaluate Formula command, that is present on the Tools tab on the ribbon and in the right click context menu on the worksheet, displays selected formula, divided into the sub-expressions in the hierarchical tree form.
You see value of every subexpression and understand where the value of the formula came from.
You can expand the subexpressions and precedents to go in-depth until the input cell appears.
This command displays return value of some functions like OFFSET, that produce indirect dependencies on the worksheets. Usually it is hard to determine which range OFFSET function returns. But xlCompare exactly points you to this range.
As you see, xlCompare is a feature rich Excel file comparison tool. Armed with this tool you will be able to solve all spreadsheet comparison and merging tasks you experience on the daily basis.