7 Ways to Compare Two Columns in Excel 2023 May

Rate this post

Comparing data from two columns in a big Excel spreadsheet may be time-consuming. Instead of examining the columns and manually entering “Match” or “Mismatch” into a separate column, you may utilize Excel’s functions to automate the process.

We’ll look at how to compare two columns and detect matching or mismatching data using several Excel functions.

1. How to Highlight Duplicate Data

Whether you want to compare two columns in Excel without adding a third column that shows if the data exists in both columns, you may use the Conditional formatting function.

Choose the data cells to be compared.
Head to the Home tab.
Select the Conditional Formatting option from the Styles group.
Select Highlight Cells Rules > Duplicate Values from the menu.
In the Duplicate Values window, check the Format cells that include option and choose the formatting option next to values with.
Click OK.

Excel will now highlight the names found in both columns.

2. How to Highlight Unique Data

If you wish to find data that isn’t in both columns, you may use the same method.

Select the data set.
Return to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values and repeat the process. Choose Unique for Format cells that include.
Click OK after selecting how the mismatched data should be highlighted.
Open the Home tab and choose the data you wish to compare (without including the headers).

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

While these strategies are simple to use, they may not be efficient for bigger spreadsheets, so you’ll need to consider another strategy of organizing your spreadsheet. So we’ll look at more advanced methods that show you which rows have the same data or utilize an extra column to display values indicating whether or not the data matches.

Renaming a Bluetooth Device on Your iPhone

3. Highlight Rows With Identical Data

If you want a more visually appealing depiction of identical data, you may have Excel look for matching values in two columns and highlight the rows with matching data. We’ll utilize the Conditional formatting function, like we did in the prior technique, but with a few more steps.

This way, you’ll have a visual signal to assist you locate matched data quicker than if you read through each column separately. Follow these steps to compare two sets of data using Excel’s conditional formatting:

Select New Rule from the Conditional Formatting menu.
Click Select a Rule Type from the drop-down menu. To identify which cells to format, use a formula.
Type =$A2=$B2 into the box below, then choose Format values when this formula is true. In this case, A and B represent the two columns being compared.
To change how Excel highlights the rows, click Format and then pick the Fill tab in the Format cells window. You have the option of customizing the background color, pattern style, and pattern color. You will get a sample to examine the design. When you’re through with the modification, click OK.
When you click OK in the New Formatting Rule box, Excel will immediately highlight the rows with matching data.
Click OK in the New Formatting Rule window, so Excel will highlight the rows with matching data instantly.

When using this approach to compare two columns in Excel, you may additionally highlight the rows with different values. Follow the steps above, and then, at step 5, insert the =$A2>$B2 formula in the Format values when this formula is true field.

How to Turn Off Facebook Post Comments

4. Identify Matches With TRUE or FALSE

When comparing two Excel columns, you may add a new column. You will create a third column that will show TRUE if the data matches and FALSE if the data does not match using this way.

To compare the first two columns in the third column, use the =A2=B2 formula. If you fear the TRUE and FALSE rows would overload your spreadsheet, you may use Excel to apply a filter to only display the TRUE values.

5. Compare Two Columns With an IF Function

An IF function is another way to evaluate Excel data from two columns. This approach is similar to the last one, but it has the added benefit of allowing you to personalize the displayed value.

You may specify the value for matching or different data instead of TRUE or FALSE. In this example, we’ll utilize the values Data matches and Data doesn’t match.

The formula for the results column will be =IF(A2=B2,”Data Matches,”Data Doesn’t Match”).

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

A VLOOKUP function may also be used to discover duplication in two columns in Excel. Each cell in the second column will be compared to the cells in the first column by Excel.

For the column showing the results, use =VLOOKUP(B2,$A$2:$A$14,1,0). Just be careful to change the data range.

When you apply this formula, Excel will either show the matching data or return a #N/A result. The #N/A value, on the other hand, may be perplexing, particularly if you share the spreadsheet to someone else. If they are inexperienced with Excel, they may feel there is a mistake.

How to Change Your Xbox Profile's Gamerpic

Upgrade the VLOOKUP function to an IFERROR function to minimize misunderstanding. Use the =IFERROR(VLOOKUP(B2,$A$2:$A$14,1,0),”Data Doesn’t Match”) formula to discover data in column B that is also in column A.

7. How to Compare Two Columns and Extract Data

In addition to comparing two Excel columns for matches, you may retrieve matching data using the VLOOKUP function. This saves you time since you won’t have to manually search the first column for relevant data.

In addition, if data in the second column is missing from the first, Excel will show a #N/A value. Use the =VLOOKUP(D2,$A$2:$B$14,2,0) formula for this.

Use the =VLOOKUP(“*”&D2&”*”,$A$2:$B$14,2,0) formula to safeguard your results from spelling errors. The asterisk (*) serves as a wild card character, capable of substituting any number of characters.

Compare Excel Columns With Ease

As previously noted, there are several methods and tactics you may use to compare two columns in an Excel spreadsheet and get the best results. You may even show the results in a third column.

If you’re new to Excel, you should realize that it can be used for more than just work. There are several formulae available to assist you in solving real-world challenges.

You are looking for information, articles, knowledge about the topic 7 Ways to Compare Two Columns in Excel on internet, you do not find the information you need! Here are the best content compiled and compiled by the appsladder.com team, along with other related topics such as: How To.