How to compare two columns in Excel and delete duplicates (highlight, color, move)

Variant A: both columns are on the same list

1. In the first empty cell, in our example this is Cell C1, write 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 that we want to use for comparison. $B$1 and $B$10000 are the addresses of the first and the last cell of the 2nd column that you want to compare against.Pay attention to the absolute cell reference - dollar signs ($) preceding the column letters and row numbers. I use the absolute reference on purpose, in order for the cell addresses to remain unchanged when copying the formula.

   If you want to find dupes in Column B, swap the column names so that the formula looks like this:

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

   Instead of "Unique"/"Duplicate" you can write your own labels, e.g. "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 such presentation is more convenient 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 do this, put the cursor to 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 holding it down drag the border downward selecting all cells where you want to copy the formula. When all needed cells are selected, release the left mouse button:

   Tip: In large tables, it is faster to copy the formula using shortcuts. Click on cell C1 to select it and 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. Awesome, all duplicated cells are flagged as "Duplicate":

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

1. In the 1st cell of the 1st 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 on which the 2nd column is located, and $A$1:$A$10000 are the addresses of the first and last cells of that 2nd column.

2. Similar to Variant A.
3. We have the following result:

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

Show only duplicated rows in Column A

If your columns do not have headers, you need to add them. To do this, put the cursor on the number indicating the 1st 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:

Give names to your columns, e.g. "Name" and "Duplicate?". Then switch to the Data tab and click Filter:

After that click a tiny grey arrow next to "Duplicate?" to open a drop down list, uncheck all items other than Duplicate in that list, and click OK:

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

In order 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:

Color or highlight found duplicates

If the "Duplicate" flag does not suffice for your purposes and you want to mark duplicated cells by font color or fill color or in some other way…

Then filter the duplicates as explained above, select all filtered cells and press Ctrl + F1 to open the Format Cells dialog box. As an example, let's change the background color of duplicated rows to bright yellow. Of course, you can change the background color of cells using the Fill color option on the Home tab, but the advantage of the Format Cells dialog box is that it lets you make all formatting changes at a time:

Now you definitely won't miss a single duplicated cell:

Remove duplicates from the first column

Filter your table so that only cells with duplicated values show up, and select all those cells.

If 2 columns you are comparing are located on different worksheets, i.e. in separate tables, right-click the selected range and choose "Delete Row" from the context menu:

Click OK when Excel will ask you to confirm that you really want to "Delete entire sheet row" and then clear the filter. As you can see, only the rows with unique values are left:

If 2 columns are located on one worksheet, next to each other (adjacent) or not touching each other (nonadjacent), the removing duplicates is a bit more complex. We cannot delete entire rows that contain duplicate values because this would delete corresponding cells in the 2nd column too. So, in order to leave only unique entries in Column A, you do the following:

1. Filter the table so that only duplicated cells are displayed and select all those cells. Right click the selection and choose "Clear contents":
2. Clear the filter.
3. Select all cells in Column A starting from cell A1 up to the last cell that contains data.
4. Go to the Data tab and click Sort A to Z. In the dialog window that opens, choose "Continue with the current selection" and click Sort:
5. Delete the column containing the formula because you do not need it any longer, only "Uniques" are left there by now.
6. That's all, now Column A contains only unique data that do not exist in Column B:

As you see, it's not so difficult to remove duplicates between two Excel columns using formulas. Though it's very time-consuming and boring process to write and copy the formula, apply and clear the filter every time you need to compare 2 columns in your worksheets. The other solution I am going to bring to your attention is much simpler and will take just a fraction of time we've spent on the first method. I believe you will find more pleasant things to spend the saved time on ;)