Comparing tables in worksheets

The following assumes that the information in your Excel files is (at least partly) organized as tables. Selecting Rows or Columns (vertical=rows, horizontal=columns) indicates your file is indeed organized as tables. This is used to decide if rows (or columns) have shifted because of an added extra line. As a result the table properties group becomes visible. Supplying the correct values in this group will enable the comparison process to give better information. Instead of simply returning any cell with a different value, it can now also tell that some lines were added (or removed).

If we press now [Compare] we get much more useful reports (as compared to Comparing Excel files using ranges). The Analysis Report reduced the information to 4 lines. The Visualization Report shows the original data in the table and what the changes are. Added rows have a blue mark. Removed rows a red mark. Changed values show the previous value in yellow.

Analysis Report:

Missing row C:\projects\agora\VB6\code\ExcelCompare\TestFiles\FileNew.xls!SheetWithTable!A11:E11(11)
Missing row C:\projects\agora\VB6\code\ExcelCompare\TestFiles\FileNew.xls!SheetWithTable!A10:E10(10)
Different value SheetWithTable!D3 20 19
Missing row C:\projects\agora\VB6\code\ExcelCompare\TestFiles\FilePrevious.xls!SheetWithTable!A8:E8(7)

Visualization Report

Rows 1 - 77   (      - Row added         - Row removed)

A B C D E
1  Product ID   Product Name   Quantity Per Unit   Unit Price   Units In Stock 
2  1   Chai   10 boxes x 20 bags   18   39 
3  2   Chang   24 - 12 oz bottles   20 19   17 
4  3   Aniseed Syrup   12 - 550 ml bottles   10   13 
5  4   Chef Anton's Cajun Seasoning   48 - 6 oz jars   22   53 
6  5   Chef Anton's Gumbo Mix   36 boxes   21.35   0 
7  6   Grandma's Boysenberry Spread   12 - 8 oz jars   25   120 
8  7   Uncle Bob's Organic Dried Pears   12 - 1 lb pkgs.   30   15 
8  8   Northwoods Cranberry Sauce   12 - 12 oz jars   40   6 
9  9   Mishi Kobe Niku   18 - 500 g pkgs.   97   29 
10  10   Ikura   12 - 200 ml jars   31   31 
11  11   Queso Cabrales   1 kg pkg.   21   22 


Note: differences between rows / columns in the order in which they are placed are ignored (considered inessential).

Tutorials on comparing spreadsheets