How to Compare Two Columns in Excel using Two Different Methods

There is often more than one solution to a problem. In the world of computers and programming, that is particularly true. Excel is a powerful spreadsheet that offers a multitude of functions that allows the user to manipulate data, and due to the sheer number of functions, there is often more than one way of finding a solution to a problem. For example, you can add columns of numbers by using the SUM function or you can add the numbers within a column individually using the PLUS function. There are also various ways of comparing data within Excel depending on the results you are looking for.

This tutorial will show you two different ways to compare columns of data within Excel. To apply the concepts in this tutorial you need a basic understanding of how to work in Excel so a course Excel 2013 made easy will give you the skills you need to put the principles of this tutorial into practice.

A simple visual comparison of two columns in Excel

Excel offers a way of formatting the data in columns to highlight data depending on your specific needs and requirements. This formatting is achieved through “conditional formatting.” Conditional formatting in Excel is a really simple way of visually highlighting values within your spreadsheet data. Conditional formatting allows you to highlight values that meet specific requirements you set. You can, for example, highlight all values that are greater than a certain value, or less than a certain value, as well as various other functions.

Conditional formatting also allows you to quickly and efficiently look for duplicate values within a set of data. For our example, we will use a spreadsheet that contains orders from our clients. Our initial data looks like this:

 Let us assume that we want to know if there are clients that only ordered goods in the first quarter or if there are clients who only ordered goods in the second quarter. In other words we are looking for unique values within the two tables of data.

Conditional formatting allows you to format data to highlight duplicate values. This function is in fact two functions in one, since it shows you which are duplicate values and at the same time, logically speaking, shows you which values are unique.

To use the conditional formatting rules on your data, highlight the data you want to format and then choose “conditional formatting” from the Home tab in Excel.

For our example, I have selected the client names in column B and then clicked the “conditional formatting” tab. The conditional formatting tab has a number of options to help you format data. For this example we will select the “Highlight Cell Rules” option and then choose “Duplicate Values”.

You can then select the Color Formats you would like to use to highlight the duplicate values. I chose the first option which formats all duplicate values in Red with a red fill for each cell, but you can choose whatever option suits you. The result of conditional formatting of our data is shown below:

It is clear if you look at the two lists above that Sarah is missing from the 2nd quarter sales and that Adam did not order in the first quarter.

As you can see, from the above, conditional formatting is a very efficient and quick way to compare two columns of data within excel. The problem with conditional formatting is that it is purely visual and does not help you to manipulate the data. It may also not be practical in long lists of data or large spreadsheets.

For an advanced course in Excel, why not sign up for the Advanced Excel Training course from udemy today and really learn to harness the power of Excel.

Using the Match function to compare two columns of data within Excel

Excel offers a number of different formulas that allow you to compare data in two columns. The LOOKUP function, the VLOOKUP function, and the MATCH function can all be used to compare values within columns of data depending on the results you need.

For the next example we will use the MATCH function to compare our two columns of data. The Match function searches for a value that matches the value you specify and then returns the row number of that value if the value is found. If the value is not found then the function returns an #N/A to indicate that there were no matches found.

To use the Match function in Excel you can either use the Formula tab to insert the formula into the cell or you can type “=MATCH” into the cell address to initiate the function. For the purposes of this tutorial we will add the match function via the formulas menu.

MATCH is part of the Lookup & Reference functions within Excel. To use the formula, click the Formula tab and then click the Lookup & Reference button and choose MATCH from the list. The arguments window will automatically open to help you complete the arguments you need for this function:

This is the table of data:

For our data we want to look up each client’s name to see if the client ordered in the second quarter. The lookup value is therefore C3. The lookup array refers to the range that Excel must search to see if there is a match for this value. Our range is C18:C28. You may have noticed that the range is specified as $C$18:$C$28 and not merely as C18:C28. The dollar signs make this range an absolute range so when we copy the formula to other cells, the range that is searched remains the same. Mastering MS Excel will teach you how to use advanced tips and techniques to save time in Excel.

The results of our MATCH formula to compare the columns in Excel is as follows:

You can see from the above results that Alan for example ordered in the second quarter and the order ID was 2. You can also immediately see that Sarah did not place an order in the second quarter.

As mentioned before, there are a number of different ways that Excel offers to help you compare two columns of data. To learn more advanced Excel techniques, try an advanced Excel course today.