Efficiently check Excel files for differences

You are working with Excel files regularly - there will be a moment when you need to find a difference between files you are working with.

If your files are relatively small - you can sort the data, tile your worksheets one versus one on the screen and highlight the differences with Conditional Formatting feature. Easy work.

Excel table with 1000+ rows, that has inserted, and deleted rows and possible mixed order, can't be compared with cell-by-cell method described above. Comparison algorithm became much more complex.

1. Determine which type of worksheet you have. If you have a structured table, where columns represent fields - this is database, and you need to compare it with Excel Table algorithm in xlCompare. This is the most common type of the Excel files. Various lists are often stored or exported into Excel tables. For all of them we apply database comparison algorithm. For other files you can use General Worksheet algorithm. This is suitable for regular Excel files.

You can select right comparison algorithm in the Comparison Wizard.

2. In case of the Database table - define Primary Key columns and rows. Even if heading is missed, combination of some columns usually makes Primary Key. This is highly important. Primary Key identifies a record in the Database table. So, records must be compared only by the Key.

To define Primary Key, select columns you need and use command Set As Key in the right click menu.

3. Analyze if your Primary Key can be changed. Database theory requires primary key to be unique. However, in the real life we often encounter excel files that doesn't have ID column and have several rows with same key value. Open Database page in the Options and define possibility of the changed keys by changing option Allow Changed Primary Keys in the Database Tables.

Very often you need to allow primary keys to be flexible.

4. Hide the data you would like to ignore. By default, xlCompare ignores hidden rows and hidden columns (you can change this in the Options). Use this technique to ignore data on your worksheets. For example, if you would like to exclude block of data from comparison - filter your table and hide these rows. This technique is applicable any Excel worksheet, not only to database tables.

5. Ignore whitespace or specific characters. Extra space characters may give you a comparison noise. That's why xlCompare ignores space characters by default.

If you need to detect differences caused by space characters - enable this option on the General page in Options.

Excel file comparison is more complex than it looks. There are other options you can use - Ignore calculated values and cell styles, Ignore errors, ... But the points we've described above are base options you need to know to compare your Excel files effective and get the best results.

xlCompare makes complex work simple