6 practical ways to compare two columns in Microsoft Excel

When you are analyzing Excel files, comparing different Excel columns can be a time-consuming and tedious task. But below we will discuss 6 practical ways to do this that can make it easier.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

When you are analyzing Excel files, comparing different Excel columns can be a time-consuming and tedious task. But below we will discuss 6 practical ways to do this that can make it easier.

Highlighting duplicate data

If you want two columns to find duplicate data, just follow these steps:

  • First select both columns.
  • Then go to the Home tab.
  • As you can see in the image below, click on the Conditional Formatting option.
  • Then go to Highlight Cells Rules> Duplicate Values.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

  • In the window that opens, set the first option to Duplicate and specify in the side section how the duplicate data is displayed.
  • After clicking OK, you can see duplicate data.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

Highlight non-repeating data

You can now do the same method as before to highlight non-repeating data.

  • First both Select the column
  • Go to Home> Conditional Formatting> Highlight Cells Rules> Duplicate Values.
  • In the Format cells that contain section, select Unique.
  • Then Click OK to specify how non-repeating data should be displayed.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

Identifying rows with duplicate data

If you want to see rows with duplicate data in Excel, you must do the following:

  • First the columns Select the desired one (without selecting the title).
  • In the Conditional Formatting section, click on New Rule.
  • In the Select a Rule Type section, use the Use a formula to determine which cells to format option. Select .
  • In the bottom section, enter =$A2=$B2. In this price, A and B are the columns we want to compare.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

  • For some customization of how duplicate data is displayed, click Format and select the Fill tab in the Format cells window. In this section, after choosing the desired options to personalize this topic, click on OK.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

  • Finally, click OK in the New Formatting Rule window so that you can see the rows with duplicate data.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

  • If you also want to do this for non-repeating data, just enter =$A2<>$B2 instead of the desired expression.

Determining duplicate or non-duplicate data in a new column

  • Using this method, a new column is created that If the rows are duplicates, TRUE will be displayed and if the data were non-duplicates, you will encounter FALSE.
  • In order to do this, for the third column of the formula =A2=B2> use.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

Compare two columns with function IF

One of the other ways to compare two Excel columns We can refer to the use of the IF function. This method is similar to the previous trick, but in the third column, the expression you want will be displayed.

For this, it is enough, in the third column, the expression =IF(A2=B2, "Data Matches" Write "Data Doesn't Match"). In this case, if the data is the same, you will see the statement Data Matches and otherwise, the statement Data Doesn't Match.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

Comparison of two columns with the VLOOKUP function

You can also use the VLOOKUP function to compare two columns in Excel. To do this, write =VLOOKUP(B2,$A$2:$A$14,1,0) in the third column. In this case, you will either encounter duplicate data or see #N/A. Considering #N/A is confusing for some users, you can use the next method.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

To avoid displaying #N/A, just write the formula =IFERROR(VLOOKUP(B2,$A$2:$A$14,1,0),Data Doesn't Match). With this, if the data is non-repeating, the phrase Data Doesn't Match will be displayed.

BingMag.com 6 practical ways to compare two columns in Microsoft Excel

To In general, the Microsoft Excel program uses a wide range of features, we can do all kinds of work in different ways. In this article We discussed only 6 of these methods, and apart from these, there are many more tricks.

  • 8 tips to learn Microsoft Excel quickly

Leave a Reply

Your email address will not be published. Required fields are marked *