Columns Match in Excel

A tool to compare multiple data sets and calculate match percentage

When you have several comparable items in an Excel table, it is common to arrange data in columns with different attributes across rows. E.g. car models, phones, experimental and control groups, stores in a retail chain, etc. With large number of entries visual analysis will be inconclusive. VLOOKUP, INDEX and MATCH functions provide only cell-by-cell comparison and do not give a general picture. How can you find out whether the data sets are similar to each other? Are the columns identical?

The Columns Match feature helps compare columns and get a big picture:

  • Compare two or more columns with each other
  • Compare columns against benchmark values
  • Calculate the exact match percentage
  • Results in crosstab table are easy to interpret

Overview of XLTools Columns Match Functionality


Add XLTools Columns Match to Excel 2016-2007

Works with: Microsoft Excel 2016, 2013, 2010, 2007, and desktop version of Office 365.

Download XLTools Add-in


Learn your way around:


How to compare two or more columns with each other and calculate the match percentage

Suppose, you want to compare product prototypes with each other and find out how much similar, different or maybe even identical they are.

  1. Select the columns which you want to compare.
  2. Click ‘Columns Match’ on the XLTools tab > Select ‘Match the columns with each other’.
  3. Tick ‘My data has headers’ if it does.
  4. Tick ‘Show the percentage of compliance’ to display the match percentage.
    Otherwise, the match level will display as 1 (complete match) or 0 (no match).
  5. Choose where to place the results – in a new or an existing worksheet.
  6. Click OK > Done, results are presented in the crosstab table.

How to match columns with each other with XLTools

Tip: apply conditional formatting. It will be easier to interpret the results:
Select output table > Click on the Quick Analysis icon > Apply Color Scale.

Review results: Type 1 and Type 3 prototypes are almost identical with the 99% match. But Type 2 and Type 4 match least of all – only by 30%.

How to compare and calculate match percentage of columns


How to compare columns against benchmark values and calculate the degree of compliance

Suppose, you want to compare product prototypes against a set of standards, as well as measure their degree of compliance to these benchmark values.

  1. Select the columns which you want to compare:
    E.g. columns with prototypes data.
  2. Click on the ‘Columns Match’ button on the XLTools tab.
  3. Select ‘Match against the range of benchmark columns’ > Select the benchmark columns:
    E.g. columns with standards.
  4. Tick ‘My data has headers’ if it does.
  5. Tick ‘Show the percentage of compliance’ to display the match percentage.
    Otherwise, the match level will be displayed as 1 (complete match) or 0 (no match).
  6. Choose where to place the results – in a new or an existing worksheet.
  7. Click OK > Done, results are presented in the crosstab table.

How to match columns against benchmark with XLTools

Tip: apply conditional formatting. It will be easier to interpret the results:
Select output table > Click on the Quick Analysis icon > Apply Color Scale.

Review results: Type 2 prototype is a 100% match to Standard 2. While Type 5 is the closest match to Standard 3 at 96%. Now you can conclude, how far the prototypes deviate from the benchmarks.

How to compare with benchmark and calculate the percentage of compliance

When is XLTools Columns Match useful

The add-in scans cells row by row, and then calculates the percentage of identical values between columns. XLTools Columns Match is not an common cell-by-cell comparison tool. It does not search for duplicate/unique values.

The add-in has a different purpose. It aims to answer – how much the data sets (columns) are similar in general. It is helpful for large data sets, when you need a big ‘macro-level’ picture, e.g.:

  • How does the performance of experimental groups compare
  • How do the results of experimental and control groups compare
  • How similar/different are the product models
  • How do the employee KPIs compare to the target values
  • How does the performance of several retail stores compare, etc.

 

Any questions or suggestions? Contact us or leave your comment below.

 

Leave a Reply

Your email address will not be published. Required fields are marked *