Excel Compare and Merge Help


Compare and Merge is an Excel Add-In that can save you hours of time comparing, analyzing, and reconciling information. With a few clicks you can compare sheets, ranges, tables, and other lists and review the differences. Then you can optionally merge the results into a new sheet/range.

Compare and Merge runs in an Excel Task Pane and interacts with the current Excel workbook allowing you to select ranges, tables, and lists and instantly compare them and analyze the results. The results can be merged using the Task Pane where the Add-in provides an intuitive way to view and merge the differences cell-by-cell. Compare and Merge can also find rows that are added, removed, and/or if there a duplicates rows in either of the compare ranges and merges these into the results.

 Compare And Merge - Getting Started

 Compare And Merge - New Customer List, Hours to Minutes



Installing Compare and Merge

To install Compare and Merge see the Excel section on the Installing Office Add-Ins page. After installation Compare and Merge may be available from the Excel ribbon depending on the version of Excel 2016. If you have the correct version of Excel 2016 or are using Excel Online you can start Compare and Merge from the ribbon on the Data Menu/Tab as depicted below.



Getting Started

Get started with these simple steps

  1. On the Run / Edit menu use "New Standard Compare" or the "New Match Rows Compare" from the toolbar.
  2. Select a range of cells in the workbook and use the Set Left button to set the first compare range. Repeat to set the second compare range using the Set Right button .
  3. Click the Run button from the toolbar.
  4. A compare result is created which you can view and analyze in the workbook as an Excel table or in the Task Pane.
  5. Optionally, you can merge the differences found between the ranges using the Task Pane.

    To resolve the differences for a cell, click the "M"erge link which will display the "Left" and "Right” values where you can select which value should be in the results. For added, removed, and duplicate rows click the "Accept" button to keep the row in the results and the "Reject" button to remove the row from the results.

  6. Optionally, you can save your Compare to run at another time using the Save button .

Another way to get started

Click the button on the welcome screen or Settings screen. This will create two example Compares - Standard Compare and Match Rows Compare - which you can review and modify to learn more about all the options Compare and Merge provides.

Standard Compare - compares cell-by-cell from top-left to bottom-right using absolute cell location. If the ranges are different size the compare stops at the smallest intersecting range.

Match Rows Compare - compares the ranges by first identifying matching rows in the Left and Right ranges and then compares the cells of matching rows. It will also find rows that are added, removed, and/or if there a duplicates rows in either the Left or Right ranges and merges these into the results.

Compare Results

The result of running a compare is a new range created in an Excel Sheet or the Task Pane. You specify this on the Results option tab. The sheet result range is actually an Excel table which allows filtering and analysis of the results. It is also an effective way to integrate the results with the rest of the workbook and to share results with others. The Task Pane is a good option for quick compare and analysis, generally of smaller ranges. For more details, see the Options section.

If you select Sheet results and also select Manual merge the Task Pane will also show the results along with options for merging the differences. See the Merge section below for more details.

Navigating Compare and Merge

Chevron Shrinks the top navigation to maximize the viewing area of Compare and Merge in the Task Pane.
Pin Unpins top navigation which will allow it to scroll off the screen and provide full view of the Compare and Merge in the Task Pane.
Settings Takes you to the Settings screen where you can set personal options and preferences.
Help Takes you to a quick reference to Compare and Merge common features.
Takes you the Run / Edit screen where you can create, edit, run, and save Compares.
My Compares Takes you the My Compares screen to view a list of Compares that you have saved.
Tools Takes you to the Tools screen which provides additional features of Compare and Merge including Excel Compare and Merge (beta) Integration screen where you can enable integration with Excel Compare and Merge 365 (beta).
Settings Run the current Compare.
Help Create a new Standard Compare.
Create a new Match Rows Compare.
Save the current Compare.
The name of the compare links to the location of the Compare results in the workbook. If the Result Type is set to Task Pane it will not be a link and the name will appear as white instead of yellow.
The address of the Left and Right compare ranges link to the location of the respective range in the workbook. If the Left and Right ranges are Excel tables the table name will be displayed instead.
New Compare Results Options Compare Results Narrow


Compare

The compare process works by retrieving the cell data for the Left and Right compare ranges that you have set and running them through the compare calculation. The result of the comparison depends on the options that you have set for the compare. The most significant option that impacts the results is Compare Type -- Standard or Match Rows -- since the latter first finds matching rows in the Left and Right ranges and then does the comparison.

After the compare process runs the results are provided either as a table in a worksheet, the Task Pane, or both. If your Merge Type is set to "Manual" the Task Pane provides a view of the differences and helpful controls for easily merging the results. See the Merge section below for more information.

Left and Right Compare Ranges

Why Left and Right? Using Left and Right provides the perception of comparing things "side-by-side" which is generally what Compare and Merge is doing. However, in the compare process the Right range has greater significance as outlined by the following:

  • The Auto-Merge feature is implemented by "merging right". This means you are automatically accepting the Right range values.
  • If the Left and Right compare cells have a different format, the Right cell format will be used when showing differences and for added, removed, and duplicates rows in the results for a Match Rows compare. For example, if the Left cell is a number with format "General" and the Right cell is number with format "Accounting" the difference value will be displayed in the Accounting format such as ($450.33)
  • The Right range is used as the source for column headers in the compare results and to retrieve the list of columns when using the "Get Columns" feature in order to select specific columns to be compared.

Left and Right can also be more intuitive with time-based compare ranges. For example, if you are comparing current quarter data with previous quarter data then you would set the Right range to be the current quarter on the basis that it contains more recent data and you would accept current data over older data when merging results -- i.e. "merge right".

Column Position

Generally, the compare process expects that your columns are in the same position in both the Left and Right ranges. In other words, the compare process needs the columns to "line up" in both ranges. This is not as important in a Standard Compare where the compare process is just comparing cell-by-cell. It's up to you if having the columns in the same position is important but in most cases it will be. When using the Match Rows compare type the columns must be in the same in order for the compare process to find matching rows.

Columns to use to Match Rows

When creating a Match Rows Compare you must tell Compare and Merge how to match a row in the Left range with a row in the Right range. You do this by specifying which column in the Left and Right range will have the same data value on both sides. This is usually a column that uniquely identifies the information contained in the row such as Social Security number of an employee list or Product ID for a list of products.

The terminology can be confusing so a picture may help.



In the above Match Rows Compare, the column Rep ID is specified as the column that the compare process should use to find matching rows in the Left and Right ranges. As the compare process moves through the rows in each range it will try find the same value for Rep ID on both sides. The example depicts that the compare process has found a match on "Rep2" in both the Left and Right ranges so it then continues and compares the other columns for Rep2 to check for differences. A new Last Name is found for Rep2 along with a new Sales number and Last Sale date. Rep1, Rep4, and Rep5 will similarly be matched by the compare process.

Using Rep ID the compare process can also find out if there are rows in the Left Range but not in the Right range (removed rows), rows in the Right range that are not in the Left range (added rows), or if there are duplicate rows in either the Left or Right ranges.

If necessary, you can also specify two columns to match rows which indicates that the value in the 1st column combined with the value in the 2nd column should be used by the compare process to find matching rows, for example User Name and Email Address. You can also specify column number instead of name. See the Options section for more details.

When to use Standard vs. Match Rows Compare

The following table provides some guidance on which Compare Type to use based on the information you are trying to compare.

Standard Compare Match Rows Compare
Left and Right ranges have the same number of columns and rows. Left and Right ranges have a variable number of rows.
There is no way to uniquely identify a row in the Left or Right range. Rows can be identified as having the same value in one or more columns (i.e. "match") but different values for other columns.
You want to find the differences in first 100 items of a list and don't care about other items. You want to find out what was added or removed from a list since the last time or find duplicate items in the compare ranges.
You want to compare financial or time-related such as budgets, time sheets, and X/Y Chart Axis data. You want to compare two lists that have items that can be uniquely identified such as employee lists (Social Security #), product lists (Product ID), or database records (Primary Key).

Analyzing the Results

The compare process examines the Left and Right ranges and compares cell-by-cell by looking at both the type and the value of the data in the cell. For a Match Rows compare, this happens after the matching rows are identified. If the type of data in the two cells is the same then the cells can be compared. The following table shows how differences are displayed in the results.

Compared Data Results
Number type in both cells The difference value between the cells, formatted with the number format of the Right range. Ex.   

The "Diff Details in Sheet" Number option for can be used to also show the Left and Right values in the compare result cell.
Date type in both cells The difference value between the cells, formatted as "# yrs, # days" or "# hours" or the actual number difference (days) depending on the "Date Labels" option. Exs.   

The "Diff Details in Sheet" Date option can be used to also show the Left and Right values in the compare result cell.
Note: when merging the Task Pane will always show the formatted difference even if the results table shows the actual number.
Text type in both cells Text differences are indicated by Text Diff

The "Diff Details in Sheet" Text option can be used to also show the Left and Right values in the compare result cell.
Note: the difference in the text can also be seen in the Task Pane when merging.
Data types are different Cell type differences are indicated by Type Diff
Note: you can still manually merge this difference in the Task Pane and select the preferred Left or Right cell value.

The "Diff Details in Sheet" Text option can be used to also show the Left and Right values with the type difference displayed in the compare result cell.
Error in one or both cells Error in one or both cells are indicated by Error Diff
Note: you can still manually merge this difference in the Task Pane and select the appropriate Left or Right cell value.

The "Diff Details in Sheet" Text option can be used to also show the Left and Right values with the error(s) displayed in the compare result cell.
Added Row Added rows, those in the Right range but not in the Left range, are colored in green and indicated in the Diff column with "A".
Removed Row Removed rows, those in the Left range but not in the Right range, are colored in red and indicated in the Diff column with "R".
Duplicate Row in Left Range
(Match Rows Compare Only)
Duplicates in the Left range are colored in light blue and indicated in the Diff column with "DL".
Duplicate Row in Right Range
(Match Rows Compare Only)
Duplicates in the Right range are colored in dark blue and indicated in the Diff column with "DR".

Results

Sheet Results

Left Range

Right Range



Merge

The merge process allows you to specify what to do with the differences found between the Left and Right ranges, if anything. On the Compare option tab, you specify the Merge Type which determines how merge will work according to the following.

Merge Type Results
None No merge. Just compare the Left and Right ranges and show the differences in the results range.
Manual Evaluate the differences between the Left and Right ranges and use the Task Pane to keep or "accept" which value should be added to the results on a cell-by-cell basis. For cells where there is no difference the cell value will be displayed in the compare results. For added, removed, or duplicate rows you either accept or remove the entire row in the results.
Auto Automatically accept the values in the Right range and use these values in the result.
Note: exceptions to this are the options to "Ignore text, use left Range" and "Ignore dates, use left Range" which will override and use Left range values instead. See Options below for more information.

Manual Merge

Manually resolving differences between two ranges or lists can be a cumbersome process. Compare and Merge makes this easier for you by providing efficient ways to view and navigate the differences and accept the correct values into the results range. When you accept the correct value in the Task Pane it will also update the sheet so that once you finish with the merge process you have the final results in the sheet.

NOTE:   You can bypass merging in the Task Pane and modify the sheet results directly but the Task Pane will not update based on your direct changes in the sheet. In addition, adding or removing rows directly in the sheet will cause the Task Pane to be out of sync with the sheet results and subsequently switching back to the Task Pane will result in errors. Therefore, it is recommended that you merge results using either the Task Pane or directly in the sheet but not both at the same time.

The table below details the controls and options available for merging.

"M"erge button opens a panel which displays the Left and Right values and allows you to select or "accept" which value to include in the merged results range. The values are links so simply click the value to accept it. You can also select the Diff value to cancel the merge for this cell.

Accept button used to accept a row in the results range.
Reject button used to remove a row from the results range.
Display the merge pane as a Grid view. This is ideal for larger screen displays where you can increase the size, or even undock, the Task Pane.
Display the merge pane in a Column view and merge row-by-row. This is ideal for smaller displays where the Task Pane is narrow or when the Task Pane size cannot be adjusted such as Excel Online.
Filters the Task Pane to show only rows that have differences.
Show the Left and Right values for all the cells with differences. Basically this auto-clicks all the "M"'s. This provides a great way to view all the differences and to quickly merge multiple differences.
Set how many rows to display per page. For larger screens, setting this at a higher value allows you to view several differences at once. When the Task Pane is smaller a good option is to set the page size to 1 and use the Column view for merging.

Navigate through the Task Pane -- First, Next, Previous, Last.

Compare and Merge Results Merge Row by Row


Select Methods

Select Methods specify how you want to select/set the Left and Right compare ranges. The most intuitive are "Select Ranges" and "Select Tables" where you set the Left and Right ranges by selecting contiguous cell ranges in the sheet or selecting from a list of table names, respectively.

Other Select Methods, though, provide creative ways to set the Left and Right compare ranges for various data scenarios where you may not even think the ranges are comparable. For example, the Filtered Table Select Method allows you to compare data in the same table with different column filters applied to the Left and Right compare ranges and the Same Range, Before After Select Method allows you compare the same range or table before and after refreshing or changing the data.

In addition, many Select Methods provide a way to automatically find the Left and Right compare ranges so you do not have to manually select the compare ranges in the workbook. For example, the Current Sheet to Adjacent Select Method will automatically find the first compare range in the active sheet and then find the other compare range in adjacent sheets to the left or right.

Generally, the automated Select Methods work by first finding the sheet and then finding the contiguous range of cells to use for the compare range. This is done for both the Left and Right ranges and is based on the settings you provide. We are continuously evaluating the design of new Select Methods to make Compare and Merge more efficient to use. If you have a suggestion for a Select Method please send us your Select Method Request.

The next section provides details on one of the automated Select Methods. See the Options section for more information on all the Select Methods.

Current Date to Previous

The Current Date to Previous Select Method makes it easy to automatically find compare ranges based on sheet names containing a date or date part, a common occurrence in Excel workbooks. For example, if your sheets were named such as below and the current date is in the 1st quarter of 2016, Compare and Merge would automatically find the Right compare range in sheet "Sales Q1 2016" and the Left compare range in sheet "Sales Q4 2015" and then run the compare process.



This Select Method is very effective for workbooks that keep historical data of a certain frequency -- daily, weekly, monthly, etc. -- where you are interested in the changes from period to period. You can simply add data to a sheet from the most recent period, name the sheet appropriately, and run the Compare.

There are several date format options for the sheet name and you can further refine the search for sheets by "sheet name containing" certain text. The first contiguous range will be found in each sheet (Left and Right) or you can specify a "Match First Cell" value which will find the first contiguous range containing this value in the upper-left cell.

Select Method Current Date To Previous


My Compares

The My Compares screen contains a list of Compares you have saved where you can view and manage your Compares. The table below summarizes the My Compares screen features.

Show the last Compare that was run.
Show all compares.
Find a Compare based on Compare name.
Create a new Compare of the last type created -- Standard or Match Rows.
Delete all your saved Compares.
Run this Compare.
Show the summary of the options for this compare, including Last Run date.
Edit this Compare.
Delete this Compare.
My Compares


Compare and Merge 365 (beta) Integration

Excel Compare and Merge 365 is a new app under development which is a web version of Compare and Merge that works with Office 365 OneDrive for Business and allows you to can compare ranges from different workbooks (in addition to the same workbook). Excel Compare and Merge 365 in currently in beta and is free to try.

Use the "CM 365" menu/tab to enable integration with Excel Compare and Merge 365. Currently, Excel Compare and Merge 365 (beta) cannot apply the proper formats to compare results in the workbook. Using the "Enable CM 365 Integration" option in both Compare and Merge 365 and Compare and Merge Add-In allow them to communicate to apply the correct formatting and keep the merge process in sync.

The option is available only when using Compare and Merge Add-In in Excel Online. The integration is designed to be used in "real time" in a live session between Excel Compare and Merge 365 running in a browser and the compare results workbook open in another browser with the Compare and Merge Add-In running in the Task Pane. As mentioned above both need the "Enable CM 365 Integration" set to On.

Compare and Merge Add-In will poll for requests from Compare and Merge 365 and if there are no updates it will turn off automatically after 30 seconds to save resources in Excel Online. You can turn it back on manually to continue the integration. The Add-In will reach back 5 minutes and process events it missed while it was not enabled but updates before that will not be processed and you will have to set the formatting manually.

Note that even without the CM 365 / CM Add-In integration, the compare and merge process still functions correctly in Compare and Merge 365 -- cells values will get updated, rows will be deleted etc. -- it's just the formatting of results and the merge will not happen.

As it is still in beta, the process is not always perfect and sometimes the proper communication doesn't happen. In these cases, it is best to reset the "Enable CM 365 Integration" option in the Add-In and occasionally you will need to reload the Add-In or sheet.

CM 365 Integration
Compare and Merge 365


Options Reference

 

Name
The name of the Compare up to 31 characters
Select Method
Select Ranges Manually select the Left and Right ranges in the workbook by selecting a contiguous range of cells.
Select Tables Compare tables in the workbook by selecting the Left and Right tables from a list.
Filtered Table Compare data in the same table with different column filters applied to the Left and Right compare ranges.
Same Range, Find Duplicates Easily find and extract duplicates rows from a range, sheet, or table. You can specify up to 3 columns to find duplicates in and you can also use "OR" instead of "AND" for the columns to use to find duplicates. The duplicates are highlighted and counted for easy analysis. You can also just extract the unique rows without the duplicates. Watch how easy it is.
Same Range, Compare Columns Compare adjacent columns in the same range or table and calculate the differences either as values or percentages. For example, you can use this Select Method to calculate the change in sales from period to period.
Same Range, Before/After Compare the same range or table before and after refreshing or changing the data.
Same Sheet, Find Ranges Compare ranges that are automatically found on the same sheet.
Select Sheets, Find Ranges Compare ranges that are automatically found on the sheets you select.
Current Sheet to Adjacent Compare a range on the current sheet to a range on an adjacent sheet to the left or the right based on the order of sheet tabs. The option "Auto-find next left/right on Run" will automatically find the next sheet and allows you to "walk" through the workbook and compare sheet-to-sheet. You can also lock the Left or Right sheet as a baseline for subsequent comparisons of adjacent sheets.
Current Date to Previous Automatically find compare ranges based on sheet names ending with a date. The current sheet (most recent date) and previous sheet (second most recent date) are compared using the first contiguous range found as the Right and Left ranges, respectively. Sheet Name Examples: "May 2016" compared to "Apr 2016", "Budget Q1-2015" compared to "Budget Q4-2016".

Compare Type New Compare
Standard Compares cell-by-cell from top-left to bottom-right using absolute cell location. If the ranges are different size the compare stops at the smallest intersecting range.
Match Rows Compares the ranges by first identifying matching rows in the Left and Right ranges and then compares the cells of matching rows. It will also find rows that are added, removed, and/or if there a duplicates rows in either the Left or Right ranges and merges these into the results.
Merge Type
None Do not merge the results and just show the differences in the results range. Use this all you want to do is compare the Left and Right data.
Manual Evaluate the differences between the Left and Right ranges and use the Task Pane to keep or "accept" which value should be added to the results on a cell-by-cell basis. For added, removed, or duplicate rows you either accept or remove the entire row in the results.
Auto Automatically accept the values in the Right range and use these values in the result.
Note: exceptions to this are the options to "Ignore text, use left Range" and "Ignore dates, use left Range" which will override and use Left range values instead.
Calculation Direction
Left to Right For number cells, the Left range will be subtracted from the Right. If the "Show percent change for numbers" is selected, the Left value is the divisor.
Right to Left For number cells, the Right range will be subtracted from the Left. If the "Show percent change for numbers" is selected, the Right value is the divisor.
Compare Columns
All Columns All columns will be compared
Select Columns Use the Get Columns button to select specific columns to be compared. The columns are retrieved from the Right compare range so it must be selected first. For Select Methods that find the compare ranges automatically, the Compare must be run at least once using All Columns before selecting individual columns.
Columns to use to Match Rows
How

Specify one or two columns that the compare process should use to identify a row. This is generally data that uniquely identifies a row such as Employee ID, Product ID, or email address. The compare process will use the data in this column to find matching rows between the Left and Right ranges. If a matching row is found it will then compare the row cell-by-cell and add it to the results. For example, "Rep2" was matched in the Left and Right range in the Match Rows example above using the Rep ID column.


Enter Column Number - enter the number of the column(s) to use to find matching rows in the Left and Right ranges. Column numbers start at 1 and end at the number of columns in the Right range.

Enter/Set Column Name - enter or set the name of the column(s) to use to find matching rows in the Left and Right ranges. Select a column name in the sheet and click the Set button .

Select Column Name - use the Get Columns button to select column(s) to use to find matching rows in the Left and Right ranges.

1st The 1st column number or name for matching rows. The data in this column identifies a row in both the Left and Right range.
2nd Optional. The 1st column number or name for matching rows. The data in the 1st column combined with the data in the 2nd column identifies a row in both the Left and Right range.
Include in compare calculation and results
Added Find and show added rows in the results range. An added row is one that is found in the Right range but not in the Left range. For example, "Rep6", "Rep7", and "Rep8, were found only in the Right range in the Compare example above using the Rep ID column.
Removed Find and show removed rows in the results range. A removed row is one that is found in the Left range but not in the Right range. For example, "Rep3" was found only in the Left range in the Compare example above using the Rep ID column.
Dup Left Find and show duplicate rows from the Left range in the results range. This applies only to the Match Rows compare type and indicates that multiple rows with the same matching row data were found in the Left range. For example, "Rep4" was found twice in the Left range in the Compare example above using the Rep ID column.
Dup Right Find and show duplicate rows from the Right range in the results range. This applies only to the Match Rows compare type and indicates that multiple rows with the same matching row data were found in the Right range. For example, "Rep5" was found twice in the Right range in the Compare example above using the Rep ID column.
Group Left / Right rows before compare
None For Match Rows compare type, do not group rows in the Left and Right compare ranges.
Group and Aggregate Rows For Match Rows compare type, group and aggregate rows in the Left and Right ranges into one row before comparing. This uses the Columns to use to Match Rows to match and aggregate the rows in the Left and Right ranges, respectively. The compare process then matches the Left and Right rows and compares the aggregated values for each cell in the row.
Numbers Choose from None (first row found), SUM, AVERAGE, MAX, MIN.
Dates Choose from None (first row found), MAX, MIN.
Text Choose from None (first row found), Combine with a comma, Combine with a space, Combine with new line.
Show Group Counts Additional columns Left Count and Right Count will be added to the results indicating how many rows are in the group for the Left and Right ranges, respectively. This is the number of duplicates found in the Left and Right ranges.
Headers
Row

None - the compare ranges do not contain header information. No headers will be included in the sheet results and the first row of data will be included in the compare process.

Use First Row as Headers - the first row of data is the Header information and will be set as the table header in sheet results to provided filtering and analysis. The headers will not be included in the compare process.

Use First Row as Data - the first row contains data that should be compared but column headers "Column 1", "Column 2", ... will be create the table header in sheet results to provided filtering and analysis.

Col

None - the first column of data does not represent headers and should be included in the compare process.

Use First Column as Headers - the first column of data contains headers and will not be included in the compare process. The first column will have bold text in the results.

Show compare name in header Place the Compare name in the top-left cell of the header in the results instead of the first column name.

Options New Compare
Decimals Select the number of decimal places to use in the Compare process. This impacts the rounding of difference values. If your data has several decimal points, such as longitude and latitude coordinates, be sure to set this to the appropriate value of decimal places to ensure accurate results.
Text

Compare Text - cells that contain text will be included in the compare process.

Ignore text, use Right Range - cells that contain text will not be compared and the results will display the text from the Right range for those cells.

Ignore text, use Left Range - cells that contain text will not be compared and the results will display the text from the Left range for those cells.

Dates

Compare Dates - cells that contain dates or times will be included in the compare process.

Ignore dates, use Right Range - cells that contain dates or times will not be compared and the results will display the dates/times from the Right range for those cells.

Ignore dates, use Left Range - cells that contain dates or times will not be compared and the results will display the dates/times from the Left range for those cells.

Show percent change for numbers

Calculate and display percent changes for numbers instead of the difference values.

Show zero for non-diffs

If no difference is found when comparing numbers, then show a zero in the results instead of blank.

Diff Details in Sheet
Numbers Show details of number differences in the sheet results, including the Left and Right values.
Text Show details of text differences in the sheet results, including the Left and Right values. This option includes showing the details of errors and type differences in the compared cells and for type differences it will display the type of the left and right cell in brackets, such as {Number} or {Text}.
Dates Show details of date differences in the sheet results, including the Left and Right values.
Single Line Show detail differences on a single line within the cell separated by the "|" character.
Multi-Line Show detail differences on a multiple lines within the cell with "L:", "D:", and "R" labels for Left value, Diff value, and Right value.
Analysis and Formatting
Sort Column Display the original sort order of the rows in the Left and Right ranges in the compare results. For Match Rows compares, this allows sorting the Compare results by the original order of the rows thereby moving added, removed, and duplicate rows back to their approximate original position. Note that there may be duplicate sort orders in the compare results since the row order of the Left and Right ranges are collected independently.
Diff Column Append a "Diff" column to the end of the results with difference counts for analysis.
AutoFit For sheet results, auto-fit the columns in the sheet after the Compare runs. Task Pane results are fitted according to the size of the Task Pane.
Diff Colors Highlight the differences with colors in the results.
Filter Button For sheet results, enable filtering on the Excel table containing the results.
AutoFilter Diffs Automatically filters out (hides) rows that do not have any differences in the compare results.
Date Labels For sheet results, display date/time differences formatted as "# yrs, # days" or "# hours" in the sheet. If not set the actual number difference (days) will be displayed in the sheet which can be used for analysis. Note the Task Pane will always show the formatted value.

Result Type
Task Pane Show the compare results in the Task Pane only. This is a good option for quick compare and analysis and generally of smaller ranges. However, it can be used for larger ranges and the results will be presented in pages in the Task Pane.
Sheet Add the compare results to a sheet as an Excel table. The Excel table allows filtering and analysis of the results. This is an effective way to integrate the results with the rest of the workbook and to share results with others.
Sheet Options
Method

New Random Sheet - create a new randomly named sheet each time you run the Compare. This is the safest way to ensure that you never overwrite any other sheet data. The random name will start with some part of the Compare name.

Use Compare Name - use the Compare name as the sheet name. If the sheet does not exist, it will be created. If the sheet does exist, the results of the previous Compare run will be deleted and replaced with the results of the current Compare run.

Enter Sheet Name - enter the name of the sheet to use for results. If the sheet does not exist, it will be created. If the sheet does exist, the results of the previous Compare run will be deleted and replaced with the results of the current Compare run.

Sheet If the "Enter Sheet Name" method is selected, enter the name of the sheet here.
Start Cell The upper left cell in the sheet where the results should be added. Select a cell in the current sheet and click the Set button . Note you don't have to be in the actual results sheet to use the set button, instead you can just the current/active sheet since the button is only capturing a cell address.
Next Run Options to control where where to place the compare results of subsequent compare runs "in the sheet.

Replace Last Results - use the same results range as the previous run, if any, and overwrite/replace the previous results.

Set New Start Cell - set a new location in the sheet for where to place the results and keep the previous results in the sheet.

Below Last Results - automatically place the results below the results of the previous compare run and keep the previous results in the sheet.

TIP  Use Below Last Results in combination with the Compare Option Show compare name in header to keep a history of named compare results in the sheet.




Productivity Tips

Here are a few tips on how to increase your productivity in Excel using Compare and Merge:

  •   Undo is not available after a Compare runs so it is a good idea to back-up your workbook or sheet before running a Compare if you have changes that you might need to undo.
  •   In the Excel desktop application, you can resize the task pane to increase the viewing space of Compare and Merge. Even better, if you have multiple monitors, you can undock the entire Task Pane and move (drag) it to a separate monitor:





Settings

Specify your preferences for Compare and Merge features using the Settings view. For more information on settings click on the toolbar which will provide a detailed explanation of the setting.

NOTE:   Your settings apply only to the current workbook and do not carry over to other workbooks.



Excel Version Support

Compare and Merge is supported in Excel 2016 and later and Excel Online. In Excel Online and Excel 2016 Windows Desktop version 16.0.6769.0000 or later, Compare and Merge can be started from the ribbon on the Data Menu/Tab.

Excel 2016 client supports compare range sizes up to 25,000 cells. Excel online supports smaller compare range sizes, up to 10,000 cells.

Note that certain features of Compare and Merge may only be available in recent versions of the Excel 2016 client. For example the Filtered Table Select Method is accessible only for certain Excel versions made available on or after September 2016 or in Excel online.



Compare and Merge Release History

Version / Date Description
2.3.0.0
    5/17/2017
  • New Select Method - Same Range, Find Duplicates
    • Same Range, Find Duplicates: with this new Select Method you easily can find and extract duplicates from a range, sheet, or table. You can specify up to 3 columns to find duplicates in and you can also use "OR" instead of "AND" for the columns to use to find duplicates. The duplicates are highlighted and counted for easy analysis.
2.2.1.0
    5/17/2017
  • New Feature for Match Rows Compare
    • Columns to use to Match Rows: you can now specify up to 3 columns (keys) to match rows instead of 2 when using the Match Rows compare type. For example, you can use Name, Address, and Region instead of just Name and Address to find matching, added, removed, and duplicate rows.
2.2.0.0
    12/15/2016
  • New Compare Option
    • Show compare name in header: place the Compare name in the top-left cell of the header in the results.
  • New Results Option
    • Next Run: additional options for where to place compare results in the sheet including setting a new Start Cell and placing the next compare run results below the previous compare run results.
  • Renamed Menu/Tab "CM 365" to "Tools"
2.1.0.0
    12/4/2016
  • New Select Methods
    • Filtered Table: compare data in the same table with different column filters applied to the Left and Right compare ranges; the filters are saved with the Compare for one-click access to filtering and subsequent compares
    • Same Range, Compare Columns: compare adjacent columns in the same range or table
    • Same Range, Before/After: compare the same range or table before and after refreshing or changing the data
  • Renamed Select Method Current Sheet to Previous to Current Sheet to Adjacent and added enhancements to allow selecting the direction of the adjacent sheet and automatically finding the next sheet. Also, allow locking the Left or Right sheet as a baseline for subsequent comparisons of adjacent sheets.
  • New Compare Options
    • Compare Columns: choose between comparing all columns or select specific columns to be compared; for Match Rows compares the columns to match rows can also be selected.
    • Calculation Direction: choose whether the Left range should be subtracted from the Right or vice versa for numbers and dates
    • Group Left / Right rows before compare: for Match Rows compares, group and aggregate matched rows into one row before comparing, with different aggregating options for Numbers, Dates, and Text
    • Show percent change for numbers: calculate and display percent changes for numbers instead of the difference values
    • Show zero for non-diffs: if no difference is found when comparing numbers then show a zero in the results instead of blank
    • Diff Details in Sheet: display the Left and Right values directly in the sheet results
    • Sort Column: display the original sort order of the rows in the Left and Right ranges in the compare results; for Match Rows compares, this allows sorting the Compare results by the original order of the rows thereby moving added, removed, and duplicate rows back to their approximate original position.
    • AutoFilter Diffs: automatically filters out (hides) rows that do not have any differences in the compare results
  • New user preference setting Theme to allow selecting Blue or Gray color for the compare options and results panel
2.0.0.0
    11/15/2016
  • New menu/tab "CM 365" which enables integration with Excel Compare and Merge 365 (beta), a web version of Compare and Merge that works with Office 365 OneDrive for Business and allows you to can compare ranges from different workbooks.This integration enables the Excel Compare and Merge 365 (beta) compare results to be formatted correctly and keeps the merge updates in sync with the web app merge panel. See Excel Compare and Merge 365 (beta) Integration
    IMPORTANT:   this is a Beta feature and is subject to change or removal in the future.
1.2.0.0
    7/30/2016
  • New Select Methods
    • Same Sheets, Find Ranges: compare ranges that are automatically found on the same sheet
    • Select Sheets, Find Ranges: compare ranges that are automatically found on the sheets you select.
    • Options to Ignore Text and Dates: allows you to exclude Text and Dates from the compare calculation and choose whether to use the values from the Left or Right compare range in the results.
1.0.0.0
    7/1/2016
  • Initial release of Compare and Merge.
  • Select Methods
    • Select Ranges: manually select the Left and Right ranges in the workbook by selecting a contiguous range of cells
    • Select Tables: compare tables in the workbook by selecting the Left and Right tables from a list
    • Current Sheet to Previous: automatically find compare ranges using the current sheet and previous sheet based on the order of the sheet tabs
    • Current Date to Previous: automatically find compare ranges based on sheet names ending with a date
  • Several Options are available to configure the compare process and results.