Home » How to Compare Two Columns in Excel: 7 Methods

How to Compare Two Columns in Excel: 7 Methods

by News Times USA

When working with large Excel spreadsheets, comparing the data from two columns can be time-consuming. Instead of analyzing the columns and writing “Match” or “Mismatch” into a separate column, you can use Excel’s functions to streamline the process.

We’ll take a look at how to use different Excel functions to compare two columns and identify matching or mismatching data.

1. How to Highlight Duplicate Data

If you want to compare two columns in Excel, but don’t want to add a third column showing if the data exists in both columns, you can use the Conditional Formatting feature.

  1. Select the data cells you want to compare.
  2. Head to the Home tab.
  3. From the Styles group, open the Conditional Formatting menu.
  4. Click Highlight Cells Rules > Duplicate Values.
    Identifying matching data in Excel.
  5. In the Duplicate Values window, make sure the Format cells that contain option is set to Duplicate and choose the formatting option next to values with.
    Identifying matching data in Excel.
  6. Click OK.

Excel will now highlight the names that are present in both columns.

2. How to Highlight Unique Data

You can use the same function if you want to identify data that isn’t part of both columns.

  1. Select the data set.
  2. Once again, head to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. For Format cells that contain, choose Unique.
  4. Select how the mismatched data should be highlighted and click OK.

Looking for mismatched data in Excel.

Excel will now highlight the names that can be found only in one of the two columns.

While these methods are quite easy to use, they might not be efficient for larger spreadsheets. So we’ll take a look at more complex solutions that show you which rows have the same data or use an additional column to display values indicating if the data matches or not.

Related: How to Fit Your Excel Spreadsheet to Your Screen

3. Highlight Rows With Identical Data

If you need a better visual representation of identical data, you can make Excel find matching values in two columns and highlight the rows with matching data. As we did on the previous method, we’ll use the Conditional Formatting feature, but will add a few extra steps.

This way, you will have a visual indicator that will help you identify matching data faster than reading through a separate column.

Follow these steps to use Excel’s conditional formatting to compare two sets of data:

  1. Select the data you want to compare (don’t include the headers) and open the Home tab.
  2. Click Conditional Formatting and select New Rule.
  3. From Select a Rule Type, click Use a formula to determine which cells to format.
  4. Enter =$A2=$B2 into the field below Format values where this formula is true. Here, A and B correspond to the two columns we are comparing.
    Set a formatting rule in Excel.
  5. To customize how Excel will highlight the rows, click Format and in the Format cells window, select the Fill tab. You can choose the background color, pattern style, and pattern color. You will get a sample, so you can preview the design. Click OK once you’ve completed the customization process.
    Compare rows in Excel.
  6. Click OK in the New Formatting Rule window, so Excel will highlight the rows with matching data instantly.

Highlighted identical rows in Excel.

When comparing two columns in Excel using this method, you can also highlight the rows with different data. Go through the above steps and at step 5, enter the =$A2<>$B2 formula within the Format values where this formula is true field.

Related: How to Compare Documents in Google Docs

4. Identify Matches With TRUE or FALSE

You can add a new column when comparing two Excel columns. Using this method, you will add a third column that will display TRUE if the data matches and FALSE if the data doesn’t match.

For the third column, use the =A2=B2 formula to compare the first two columns. If you think your spreadsheet will look too crowded with the TRUE and FALSE rows, you can set a filter in Excel, so it will only show the TRUE values.

Comparing two columns in Excel.

5. Compare Two Columns With an IF Function

A different method to analyze Excel data from two columns is to use an IF function. This is similar to the above method, but it comes with the advantage that you can customize the displayed value.

Instead of having the TRUE or FALSE values, you can set the value for matching or different data. For this example, we’ll use the Data matches and Data doesn’t match values.

The formula we’ll use for the column showing the results is =IF(A2=B2,”Data Matches”,”Data Doesn’t Match”).

Using an IF function in Excel.

6. Compare Two Columns With a VLOOKUP Function and Find Matching Data

Another way to have Excel find duplicates in two columns is to use a VLOOKUP function. Excel will compare each cell in the second column against the cells in the first column.

Use the =VLOOKUP(B2,$A$2:$A$14,1,0) for the column displaying the results. Just make sure you adjust the data range.

Using a VLOOKUP function in Excel.

When using this formula, Excel will display the matching data or use a #N/A value. However, the #N/A value might be confusing, especially if you send the spreadsheet to someone else. If they are not experienced when it comes to Excel, they could believe there’s a mistake.

So to avoid any confusion, upgrade the VLOOKUP function to an IFERROR function. If you need to find data that is in column B and is also in column A, use the =IFERROR(VLOOKUP(B2,$A$2:$A$14,1,0),”Data Doesn’t Match”) formula.

Improved VLOOKUP function in Excel.

7. How to Compare Two Columns and Extract Data

Besides comparing two Excel columns for matches, you can also use the VLOOKUP function to extract matching data. This will save you time as you don’t have to manually go through the first column and search for relevant data.

Also, if data in the second column misses from the first column, Excel will display a #N/A value. For this, use the =VLOOKUP(D2,$A$2:$B$14,2,0) formula.

Compare two columns and extract data from first column.

Note: If you want to protect your results against spelling mistakes, use the =VLOOKUP(“*”&D2&”*”,$A$2:$B$14,2,0) formula. Here, the asterisk (*) has the role of a wild card character, replacing any number of characters.

Compare Data With Ease

As we’ve discussed, there are plenty of ways and tricks you can use to compare two columns in an Excel spreadsheet and get the best results. And Excel has a lot more tools that can help you avoid repetitive tasks and save you a lot of time.

15 Excel Formulas That Will Help You Solve Real Life Problems

Excel isn’t only for business. Here are several Microsoft Excel formulas that will help you solve complex daily problems.

Read Next

About The Author

Source link

Related Articles

Leave a Comment