Different cell values in Excel worksheets
xlCompare displays how one cell differs from another one
Different formulas with highlighted changes
Different formula values (calculated cells)
Highlighted cells with different font options
Difference Explorer: complete comparison summary
Compare Excel Files by Key Values
One of the biggest groups of Excel files can be named Tables with Key Columns. Excel spreadsheet is a suitable form to keep and edit database records.
You receive this type of worksheets very often. Every price list is a table, list of your clients is a table, list of transactions, and so on. Databases are deeply integrated into our business, and you need an efficient tool to work with your data.
xlCompare offers a big set of commands and options you can use to efficiently compare, filter and merge tables, stored in Excel files. Below we show you how to configure primary keys on your worksheets and get the comparison report, that is based on the key values.
Setup primary keys on the worksheet
Before you get the comparison report you need to point xlCompare to the primary key columns and rows in your file.
When xlCompare opens Excel file it analyzes data in the worksheet and assigns primary keys on the worksheet if possible. However, you can modify the default selection and assign another key columns, if initial assignment was wrong.
Before you select your own combination of the keys, you need to clear current selection. Use Primary Keys | Clear menu item in the Right Click Menu on the worksheet.
This command removes all primary keys columns and rows from the worksheet.
Add new keys
Now, add select the column(s) you would like to mark as key and right click on its heading. Apply command Primary Keys | Set as key.
This command assigns key icon to the heading of the selected column and highlights it with color.
Now you can easily identify key column among other columns.
Primary key with multiple columns
Can the primary key consist of several columns? Yes, to select a complex key - just repeat previous step for every column you need.
If primary keys go in the unsorted order, this is not a problem. You can select key columns in the any order on your worksheet.
Primary key row
Usually, database table contains heading. This is row that contains field names.
If your table has key row - select it and set it as key using the same menu command as for the columns.
xlCompare highlights key rows on the worksheet as well.
Key row or heading is an important part that greatly simplifies the comparison. So, if your worksheet has key rows, please make sure that xlCompare knows about it.
Apply same keys to all sheets
I'm working with Excel file that has 30 worksheets with similar data. Corresponding file has same number of sheets.
Do you need to configure key columns and key rows on every worksheet.
No. You need to configure them only on the first sheet and apply the Primary Keys | Set to all sheets menu command from the right click menu.
It assigns same keys to every worksheet in all opened files. In a second you will have your keys assigned to all sheets.
Remember keys for future use
In the files I receive key columns goes in the reverse order. Do I need to configure columns one by one, every time I open the new spreadsheet?
xlCompare has a command that greatly simplifies this process. It will save you a lot of time and make comparison much easy for you.
After you've configured key columns on your sheet first time - your menu command Primary Keys | Remember Key
This command says xlCompare that it should remember this key.
Next time you can assign this saved selection by using command Primary Keys | Saved | Your Key.
So, usually you can setup columns only a few times and after this just reuse the saved choice.
How to compare Excel tables by primary keys step by step
All you need to do is to point xlCompare to the keys it should use. All other work is done by the application. So, at this step we are ready to get the results.
Press the Compare Files button on the ribbon. This button starts the comparison and opens comparison wizard.
Comparison Wizard is a window where you see what will be compared and how.
Check order of the keys
Use Keys button to check order of the primary keys in your files. This button brings Manage Primary Keys window where you see primary key columns assigned to all worksheets.
Usually, this step is optional and can be skipped, but you can do it to make sure that files are configured properly.
Assign Excel table algorithm
xlCompare has several comparison strategies, that are called comparison algorithms. Comparison Wizard is the only place where you can select the comparison algorithm of every pair of worksheets.
The most used strategies are Excel Table for databases and General Worksheet for all other types of Excel files.
Select the worksheets, you need to change, and use Options button. It opens window where you can select the appropriate algorithm.
Every comparison strategy (algorithm) is marked with icon. This icon, that is shown in the list, gives you a quick information about the options uses for this pair of worksheets.
Get the results
After you finalize comparison wizard with Compare button, xlCompare gives you results that are based on the key columns and key rows (heading rows).
Now you can filter and merge data on the compared worksheets. Command you need to use to extract unique or modified rows are explained in the sections above.
Options you need to know to get the best results
Can the comparison be done in just a one click?
In the simple case - yes, but unfortunately data, our customers are working with, have complex structure and sometimes table is jut looks like a database, but it is not a classic database. For example, table may have duplicate values of primary keys and this is not an error, like in the database theory.
So, you need to know several options offered by xlCompare for the database data.
Compare Excel sheets with changed keys
If your tables contain pair of almost equal rows with minor changes in the key value - this could be a modified key value. And not the insertion of 2 different rows into both tables.
This option allows primary keys to be changed.
Sort tables and comparison results by key
By default, xlCompare doesn't change order the data on your worksheets. It sorts cells internally and this doesn't have a visual reflection.
However, you can get your data sorted, by selecting option to sort the records. Also, this options sort the comparison report by key values.
This is important if you are going to merge two tables and get the right order of the records.
Highlight primary keys on the worksheet
xlCompare highlights the key columns and rows on the worksheet with color. This is made to notify you about key columns that will be used.
This is perfect visual representation of the key columns and rows on the worksheet view.
Ignore columns to minimize comparison noise
I've got a lot of changed values in one of the compared fields. Can I remove them from the comparison report.
Yes, use the Ignore Columns menu for this. It displays a checkmark on the column heading. Once unchecked - column is marked as ignored from comparison.
Ignored columns are not taken into account and doesn't produce any output for the comparison report.
Merge two Excel tables with key columns
I need to combine two Excel sheets that have a key columns. Can I do this with xlCompare?
Yes, first that you need to do is to follow the instructions above to get your sheets compared properly.
After you have comparison results on your screen, go to the Merge tab on the ribbon.
Insert Rows from Left and Insert Rows from Right are the commands you should use to move all the rows from one panel into other.
Order of the inserted rows is determined from the comparison report. So, you can play with option that Sort the results to change order rows in the merged table.
Having a question on how to compare Excel worksheets by keys?
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.
