How to Compare Excel Columns:

It can be tedious to compare data from two columns when working with large Excel spreadsheets. Excel functions make it easier to analyze the columns and write “Match” or mismatch into a separate row.

We will show you how to use Excel functions to compare two columns, and identify matching or incorrect data.

1. How to highlight duplicate data

The Conditional Formatting feature allows you to compare two columns in Excel. However, it doesn’t allow you to add another column that shows if the data exists.

1. Select the data cells to be compared.
2. Navigate to the Hometab.
3. Open the Conditional Formatting menu in the Styles section.
4. Click Highlight Cells Rules > Duplicate Values .
5. Check the Duplicate Values window to ensure that the You can format cells with the following: option is set Duplicate Select the Formatting option from the menu. With values.
6. \]Click OK.

Excel will now highlight names that are in both columns.

2. How to Highlight Unique Data

If you need to identify data that doesn’t belong in either column, you can use this function.

1. Select the data set.
2. You can also visit Home > Conditional Formatting > Highlight Cells Rules> Duplicate Values.
3. Unique will be used for -format cells that contain.
4. Select the mismatched data to be highlighted, then click OK.

Excel will now highlight names that cannot be found in either of the two columns.

Although these methods are simple to use, they may not be effective for larger spreadsheets. Let’s take a look at more advanced solutions. These will show you which rows have similar data and use an additional column that displays values to indicate whether the data matches.

3. Highlight Rows With the Same Data

Excel can find the matching values in two columns to highlight the rows that contain matching data. This will give you a more visual representation of identical data. The Conditional Formatting tool will be used, just as with the previous methods, but there will be a few more steps.

This visual indicator will allow you to identify matching data much faster than if you were to read each column separately.

Use Excel’s conditional formatting for comparing two sets of data.

1. Select the data to be compared (don’t include headers) then open the Hometab.
2. Click Conditional Formatting to select New Rule.
3. To determine the cells to be formatted, use a formula from
4. Enter =$A2=$B2 Inscribe in the box below Where this formula is true, format values . Here, A B The two columns that we are comparing correspond.
5. Click to change how Excel highlights rows. Format And in the Format cells Select the window Fill tab. You can choose your background color, the pattern style, or the pattern color. So you can view the design, you will receive a sample. Click OK After you’ve finished the customization,
6. In the New Formatting Rule window click OK to highlight rows with matching data.

You can highlight rows with different data by using this method when you compare two Excel columns. Follow the above steps. At step 5, enter the =$A2>$B2 formula in the format values.

4. Matches with TRUE and FALSE

If you are comparing two Excel columns, it is possible to add a new row. You can create a third column by using this method. It will display TRUE when the data match and FALSE if it doesn’t.

The =A2=B2 function can be used to compare the columns in the third column. Set a filter to restrict the TRUE and FALSE values in Excel if you feel your spreadsheet is too cluttered with TRUE and false rows.

5. Compare Two Columns with an IIF Function

An IF function can be used to analyze Excel data in two columns. The IF function is similar to the one above, but you can customize what the value will look like.

Instead of having TRUE and FALSE values, it is possible to set the value for matching/different data. This example will use Data matches, and Data doesn’t match.

6. Use a VLOOKUP Function for Comparing Two Columns and Finding Matching Data

Excel also has a VLOOKUP function that can be used to find duplicates in multiple columns. Excel will compare every cell in the second column with the cells of the first column.

Excel will display matching data when you use this formula. If it uses a #N/A value, Excel will show the matching data. It is possible to confuse the #N/A value, especially if you are sending the spreadsheet to someone else. Excel can be confusing for people who aren’t familiar with the program.

To avoid any confusion, you can upgrade the VLOOKUP function into an IFERROR operation. If you need to find data that is in column B and is also in column A, use the 

7. How to Compare Two Columns, and Extract Data

To find matches between two Excel columns, you can also use VLOOKUP to extract the matching data. This will save time since you won’t have to go through each column manually and search for the relevant data.