Datatific Review Learning Center

How To Compare Two Excel Spreadsheets

Oftentimes you may need to compare two versions of the same spreadsheet to see what has changed from one version to the next. The spreadsheets may be time related (e.g., data from different weeks or months). Or the spreadsheets could be different versions shared between groups (e.g., a supplier submitting a different version of data to an engineering company).

If the spreadsheets conform to a data table (with the first row the header), you can use  Datatific Review to make this comparison.   It will show the rows that were added, deleted and/or changed.  Changes are highlighted to show you what changed.  The basic steps for doing this are:

  1. Specify the first spreadsheet to compare
  2. Specify the second spreadsheet to compare
  3. Map the columns in each spreadsheet to compare
  4. Specify the unique keys in the spreadsheets
  5. Compare the spreadsheets
  6. View and analyze the results
  7. Save the compare settings
Buy now
Try for free
More Info
Data Navigator Tutorial
Buy now
Try for free
More Info
Demo (14 min.)

How-To Details

To compare two Excel spreadsheets, open Datatific Review, select the Compare tab and follow the steps below.

Specify the First Spreadsheet

Select Data Source 1 in the Compare setup. Select Excel as the data source type, click Browse to select the Excel file and then click the Connect button.

Specifiy Data Source 1

After connecting, use the Data Source 1 left sidebar to expand the Table folder and select the spreadsheet to compare.

Select spreadsheet

Filter the data as desired using the data tree navigator and/or the table column filtering.

Specify the Second Spreadsheet

Select Data Source 2 in the Compare setup. Connect to the second Excel file, select the spreadsheet and filter, if necessary, as was done for the first Excel spreadsheet.

Map the Columns to Compare

Select Map Data in the Compare setup. If both spreadsheets have the same column names, the easiest way to map the columns is to select the Match All command on the Compare ribbon.

Match All

This will automatically map all columns that have the same name.

Map Columns

Specify the Unique Keys

In Map Data, specify the columns that form unique keys - or values - for the rows. This is how new rows, deleted rows and changed rows are determined. The keys can be one or more columns. Specify the keys by clicking on the Map Setting value. This toggles between Compare, Key and Exclude. You can also exclude any columns you do not want to compare.

Specifiy Keys

Compare the Spreadsheets

Select Compare Data in the Compare setup. Before comparing, select from one of the various options available. Note that the default compare direction is from Data Source 1 to Data Source 2 which means Data Source 1 is considered "before" and Data Source 2 is considered "after". You can change the direction by clicking on the Compare Direction button.

When ready, click the Compare button.

Compare Data

View and Analyze Compare Results

After the compare is complete, the compare results are displayed with a graphical summary.

Compare Summay

Select an item to view in more detail.  For example, click on All Compare Data to view all compare results.  Note the results are color coded for easy viewing (you can change the colors in Options).

All Compare Data

Unequal results are displayed by stacking the two rows on top of each other. Changed values are color coded for easy viewing.

Unequal Resutls

Save Compare Settings

You can save the compare settings for reuse again later. Note this only saves the settings (i.e., the data source connection information, compare map and compare options) - not the data.  This makes it even easier to run the compare again.

Note: Data filters are not saved. If you filtered the data before comparing you will have to filter it again the next time.

To save the compare settings click the Save Compare button on the ribbon. If the compare has not been saved before, you will be prompted for a filename and location.

Save Compare

To save a previously saved compare to a different file, select File > Save > Save As from the ribbon. You will be prompted for a filename and location.

Compare Save As

To open a previously saved compare, select File > Open > Open Compare from the ribbon.  You will be prompted to specify the filename and location.

Open Compare

Refer to the Datatific Review Users Guide for more details on how to compare data.

Back to Learning Center Home