How to Compare Two Columns in Microsoft Excel

We’ll show you 5 different methods

When you want to compare values in different columns in Microsoft Excel, you can use more than just your eyeballs. You can highlight unique or duplicate values, display True or False for matches, or see which exact values appear in both columns.

We’ll show you how to compare two columns in Excel using five different methods. This lets you choose the one that best fits your needs and the data in your Excel worksheet.

Highlight Unique or Duplicate Values With Conditional Formatting

If you want to spot the duplicates or the unique values in your columns, you can set up a conditional formatting rule. Once you see the values highlighted, you can take whatever action you need.

Using this method, the rule compares the values in the columns overall, not per row.

When you see the values highlighted, you can take action on them as you please. In this example, we’ve filled the cells with duplicate values yellow.

Compare Columns Using Go To Special

If you want to see the differences in your columns by row, you can use the Go To Special feature. This temporarily highlights the unique values so that you can do what you need.

Remember, using this method, the feature compares the values per row, not overall.

You can take action immediately if you only have a few differences. If you have many, you can keep the cells selected and choose aFill Coloron theHometab to permanentlyhighlight the cells. This gives you more time to do what you need.

Compare Columns Using True or False

Maybe you prefer to find matches and differences in your dataset without font or cell formatting. You can use a simple formula without a function to display True for values that are the same or False for those that are not.

Using this method, the formula compares the values per row, not overall.

=A1=B1

You’ll then have a True or False in that column for each row of values.

Compare Columns Using the IF Function

If you like the above method for showing a simple True or False for your values, but prefer to display something different, you canuse the IF function. With it, you can enter the text you want to show for duplicate and unique values.

Like the above example, the formula compares the values per row, not overall.

The syntax for the formula is IF(test, if_true, if_false).

Go to the row containing the first two values you want to compare and select the cell to the right as shown earlier.

Then, enter the IF function and its formula. Here, we’ll compare cells A1 and B1. If they are the same, we’ll display “Same” and if they’re not, we’ll display “Different.”

=IF(A1=B1,”Same”,”Different”)

Once you receive the result, you can use the fill handle as described earlier to fill the remaining cells in the column to see the rest of the results.

Compare Columns Using the VLOOKUP Function

One more way to compare columns in Excel is using the VLOOKUP function. With its formula, you can see which values are the same in both columns.

The syntax for the formula is VLOOKUP(lookup_value, array, col_num, match).

Go to the row containing the first two values you want to compare and select the cell to the right as shown earlier.

Then, enter the VLOOKUP function and its formula. Here, we’ll start with cell A1 in the first column for an exact match.

=VLOOKUP(A1,$B$1:$B$5,1,FALSE)

Notice that we use absolute references ($B$1:$B$5) rather than relative references (B1:B5). This is so we can copy the formula down to the remaining cells while keeping the same range in thearrayargument.

Select the fill handle and drag to the remaining cells or double-click to fill them.

You can see that the formula returns results for those values in column B that also appear in column A. For those values that do not, you’ll see the #N/A error.

If you prefer todisplay something other than #N/Afor non-matching data, you can add the IFNA function to the formula.

The syntax is IFNA(value, if_na) where thevalueis where you’re checking for the #N/A andif_nais what to display if it’s found.

Here, we’ll display an asterisk instead of #N/A using this formula:

=IFNA(VLOOKUP(A1,$B$1:$B$5,1,FALSE),”*”)

As you can see, we simply insert the VLOOKUP formula as the first argument for the IFNA formula. Then we add the second argument, which is the asterisk in quotes at the end. You can also insert a space or other character within quotes if you prefer.

Using built-in features or Excel formulas, you can compare spreadsheet data in a variety of ways. Whether for data analysis or simply spotting matching values, you can use one or all of these methods in Excel.

Sandy Writtenhouse is a freelance technology writer and former Project, Department, and Program Manager. She turned her education, job experience, and love of technology into a full-time writing career. With all sorts of gadgets in her home and her hands, she seeks to help others make life easier through technology.Read Sandy’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved