It will take you approximately 10 minutes to read this article, and in the next 5 minutes (or even faster if you opt for the 2nd solution described in the article) you will effortlessly compare two Excel columns for duplicates and either eliminate or highlight the identified duplicates. Alright, the countdown begins!

Excel is a highly potent and truly remarkable program for generating and processing vast sets of data. Once you have numerous workbooks filled with a pool of data, or perhaps just one extensive table, you might wish to compare two columns for duplicates and then perform some action on the discovered entries, such as deleting duplicate rows, coloring duplicates, or clearing the contents of duplicated cells. These two columns could be positioned within a single table, either consecutively or non-consecutively, or they might be housed in two distinct worksheets or even workbooks.

Let’s say you have two columns containing people's names—five names in column A and three names in column B—and you aim to compare these two columns to identify duplicates. Understandably, this is fictitious data for a quick illustration; in actual spreadsheets, you typically encounter thousands or tens of thousands of entries.

Scenario A: Both columns are situated on the same sheet, within a single table: Column A and Column B

Scenario B: The two columns are located on different sheets: Column A in Sheet2 and Column A in Sheet3

The built-in Remove Duplicate tool available in Excel 2016, Excel 2013, and Excel 2010 cannot manage this situation since it cannot compare data between two columns. Moreover, it can only remove duplicates, offering no other options such as highlighting or coloring, unfortunately.

Moving forward, I will outline two potential methods for comparing two Excel columns that enable you to locate and remove duplicate entries:

Scenario A: both columns are on the same list

1. In the first blank cell, in our example this is Cell C1, enter the following formula: =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   

   In our formula, A1 is the first cell of the first column you wish to compare. $B$1 and $B$10000 are the addresses of the first and the last cell of the second column you want to compare against. Pay attention to the absolute cell reference—the dollar signs ($) preceding the column letters and row numbers. I use absolute references intentionally to ensure the cell addresses remain constant when copying the formula.

   If you want to find duplicates in Column B, swap the column names so the formula appears as follows:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   Instead of "Unique"/"Duplicate" you can write your own labels, for instance, "Not found"/"Found", or leave only "Duplicate" and type "" instead of "Unique". In the latter case, you will have empty cells next to cells for which duplicates were not found, I believe this presentation is more practical for data analysis.

2. Now let's copy the formula to all cells of column C, up to the last row that contains data in column A. To accomplish this, place the cursor in the lower right corner of cell C1, and the cursor will change to a black cross, as shown in the image below:

   Click the left mouse button and hold it down while dragging the border downward to select all cells where you want to copy the formula. When all necessary cells are selected, release the left mouse button:

   

   Tip: In large tables, it is faster to copy the formula using shortcuts. Select cell C1, press Ctrl C (to copy the formula to clipboard), then press Ctrl Shift End (to select all non-empty cells in Column C), and finally hit Ctrl V (to paste the formula into all selected cells).

3. Excellent, all duplicate cells are marked as "Duplicate":

Scenario B: two columns are on different worksheets (workbooks)

1. In the first blank cell of the first empty column in Sheet2 (column B in our case), write the formula: =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   Where Sheet3 is the name of the sheet where the second column is located, and $A$1:$A$10000 are the addresses of the first and last cells of that second column.

2. Similar to Scenario A.

3. The result is as follows:

Click to download the worksheet with the above examples and the formula to compare 2 columns to find duplicates.

Working with found duplicates

Great, we have identified the entries in the first column (Column A) that also exist in the second column (Column B). Now we need to do something with them :)

It would be quite inefficient and would take far too long to manually review the entire table and examine the duplicate entries. There are far better ways.

Display only duplicated rows in Column A

If your columns lack headers, you need to add them. To do this, place the cursor on the number indicating the first row and it will change to a black arrow as shown in the screenshot:

Right-click the selected row and choose "Insert" from the context menu:

Name your columns, for example, "Name" and "Duplicate?". Then switch to the Data tab and click Filter:

Afterward, click the tiny grey arrow next to "Duplicate?" to open a drop-down list, uncheck all items except "Duplicate" in that list, and click OK:

That's it, now you see only those cells of Column A that have duplicate values in Column B. There are only three such cells in our test worksheet, naturally, in real sheets there are likely to be more, far more of them:

To display all rows of Column A again, click the filter symbol in Column B that now looks like a funnel with a tiny arrow

and check "Select all". Alternatively, you can do the same via Data tab -> Select & Filter -> Clear, as shown in the screenshot: ![](/uploads/20250601/1748739699683ba67354

The above is the detailed content of Compare two columns and remove duplicates in Excel. For more information, please follow other related articles on the PHP Chinese website!