Open the EXCEL table, put the names into the same table, and use conditional formatting to fill the repeated values ​​with different colors to compare the repeated names.

Click Conditional Formatting

Open the EXCEL table, put the names in the two tables into one worksheet, and click “Conditional Formatting” on the menu bar.


Drop down and select “Highlight Cell Rules – Duplicate Values”.

View Results

A small window pops up, select the fill color of the repeated value, and the name filled with the color represents the repeated value.

How to compare duplicates in two columns in Excel?

Select two columns of cells, in the data interface, click “Duplicates”. In the drop-down window, click “Set Highlight Duplicates”.

  1. Open the table on the computer and select two columns of cells
  2. In the data interface, click “Duplicates”.
  3. In the drop-down window, click “Set Highlight Duplicates”.
  4. The Show Duplicates window appears, click “OK”.
  5. The repeated parts in the two columns of data in the excel table have been compared.

How does Excel operate to compare the two lists and filter out the overlapping parts?

Open the Excel table on the computer, which is the data of Sheet1.

excel 1

This is the data of Sheet2.

excel 2

To filter out the duplicate data in Sheet1 and Sheet2, enter the formula =VLOOKUP(A1,Sheet2!A:A,1,0).

excel 3

After pressing Enter, the same data will be displayed if repeated.

excel 4

Pull down the copy formula, if it is not repeated, #N/A will be displayed.

excel 5

Recommended reading
Fouad WhatsApp Download
FMWhatsApp Download

Leave a Reply

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