Compare Two Columns in Excel

Often, you’ll have multiple columns of data in a spreadsheet that you want to compare. You might want to explore different and identical values between the columns. Excel offers multiple ways of doing exactly that. Let’s learn how to compare two columns in Microsoft Excel.

How to Compare Two Columns in Excel

Imagine that you have two columns of data, and you want to compare them to each other. Perhaps you want to find values that appear in both columns. Or, you may want to look at unique values that appear in only one column.

Fortunately, this is easy to do in Excel, and it’s a very visual process that makes analysis a breeze. Let’s first look at comparing columns to find values that appear in both of them.

Consider a table with two columns. As you can see, there are some values that repeat within both columns. Some, however, do not. For a lengthy list, it helps to visually filter these.

Begin by clicking and dragging your cursor to select both columns. Then, go to the Home tab on Excel’s ribbon. Open it up, and then click on the Conditional Formatting dropdown just right of center.

You’ll see a dropdown appear, with several options to choose from. Here, you’ll want to open the one at the top, titled Highlight Cells Rules. Hover over it, and then click on Duplicate Values.

Excel will launch a new window called New Formatting Rule. This is a menu that gives you several choices about how to illustrate your data. On the dropdown, choose from duplicate or unique values, depending on the way you want to filter your data.

Click OK, and you’ll see cells in your worksheet highlighted in red. These are the unique values (appearing in only one column) or duplicate values (appearing in both), depending on the condition that you set.

As you can see, it’s easy to compare columns in Excel and visually see repeating or unique values.