# 7 Methods to Find Duplicates in Two Columns in Excel

There are great tools and commands to find duplicates in two or more columns in Excel that will ease your work and save a lot of time to treat lengthy datasheets. In this article, I will show you 7 easy methods to find duplicates in two columns in Excel.

**Find Duplicates in Two Columns with Conditional Formatting**

To find and highlight duplicates in two columns in Excel using **Conditional Formatting**, follow these steps:

- Select the two columns that you want to compare.
- Navigate to the
**Home**tab in the Excel ribbon. - Click on
**Conditional Formatting**in the**Styles**group. - Now, go to
**Highlight Cells Rules > Duplicate Values**. - In the
**Duplicate Values**dialog, check that**Duplicate**is selected. - Choose a formatting style from the
**Values with**drop-down. - Finally, hit
**OK**.

**Find Duplicates in Two Columns in Excel with Logical Formula**

To find the duplicate names in columns, I have a list of brands around the world here below.

Now to check the duplicates in two columns in Excel, follow the following steps:

- Add a new column. I have named the new column ‘
**Result**’. - Write this formula in the first cell of that column:
**=A2=B2** - Then press
**ENTER.**

If there is a match between the columns, the result will be**TRUE**. If there is a mismatch, the result will be**FALSE**. - Now double-click on the
**Fill Handle**. - The results will come up as
**TRUE**or**FALSE**indicating a match or mismatch respectively.

**Find Duplicates in Two Columns in Excel Using IF Function**

Using the **IF** function is another easy way to find the similarities and dissimilarities between two distinct columns. It gives a more expressive result than the first one I personally feel.

To apply the **IF **function to find duplicates in two columns in Excel:

- Create a new column first. I have named the new column header ‘
**Result**’. - Write this formula in the first cell of the new column:
**=IF(A2=B2,”Duplicate”,”Different”)**In this formula,**A2=B2**depicts a logical test. If the value is true, the result will be**Duplicate.**If the value is false, the result will be**Different**. - Then press
**ENTER**. - Now drag the
**Fill Handle**to the end of the column (**C2:C12**).

Instead of the words **Duplicate **and **Different**, you can use other words also. Such as ‘Match and Mismatch’, ‘Positive and Negative’, ‘Same and Unique’ etc.

**5 Ways to Find Matching Values in Two Worksheets in Excel****How to Compare Rows in Excel for Duplicates (7 Ways)****How to Find Similar Text in Two Columns in Excel (5 Ways)**

**Find Duplicates in Two Columns in Excel by ****Creating New Rule **

Highlighting can make your work of sorting duplicate characters very easy. It works great especially when you are in a hurry because you can look at the colored cells quickly.

To find and highlight duplicates in two columns in Excel by creating a new rule, follow these steps:

- Select the two columns you want to compare.
- Then go to the
**Home**tab. - Click on the
**Conditional Formatting**in the**Styles**group. - Now hit
**‘New Rule’**from the**Conditional Formatting**drop-down list.

A dialog box called**New Formatting Rule**will come along. - From the dialog box, select
**Use a Formula to Determine Which Cells to Format**as a rule type. - Now type this formula inside the
**Format Values Where This Formula Is True**bar:**=$A1=$B1** - Then click on the
**Format**button. The**Format Cells**dialog box will show up. - Now, change the
**Background Color**under the**Fill**section as per your preference. For example, I have used yellow to highlight. - Hit
**OK**to save the changes.

This will highlight the cells which have the same characters located in the same row and they are easy to find out in this way if the list is overlong.

**Find Duplicates in Two Columns in Excel & Output Third by VLOOKUP**

This method is only applicable where we need to compare two columns with some similar names or values and at the end, the comparison will return a result in the third column co-related with the 1st column names/values.

For instance, I have a list of Product Codes in the column named **Product Code **and their prices in the column **Price**. **Product Code-2 **has some of the same products as **column A (Product Code) **and the final result will return the values in **column F** **(Price-2)** corresponding with **column B (Price)**.

To match two columns in Excel and return data from a third column:

- Select a cell.
- Type the following formula:
**=VLOOKUP(E2,$A$2:$B$12,2,FALSE)** - After that, press the
**ENTER**key**.** - Now drag down the
**Fill Handle**over the range of cell range**F2:F6**.

And done! You will get your desired results this way.

**Find Duplicates in Two Columns in Excel ****Using Duplicate Values Command **

To find duplicated in two columns in Excel with **Duplicate Values** command, go through these steps below:

- Select the two columns you want to compare.
- Then go to the
**Home**tab from the ribbon. - Click on the
**Conditional Formatting**drop-down in the**Styles**group. - Now put the cursor on the
**Highlight Cells Rules**command and click on**Duplicate Values**from the new drop-down list.

The**Duplicate Values**dialog box will come along. - Check that the
**“Duplicate”**option is selected. - Change the formatting style in the
**Values with**drop-down.

I choose the**Light Red Fill**highlight and here is the end result in the image below:

**4+ Methods to Filter Duplicate Values in Excel****Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]****5+ Formulas to Find Duplicates in One Column in Excel**

**Find Duplicates in Two Columns by ****IF & COUNTIF Functions Returning Difference**

These steps will compare two columns in Excel and show the unique values as the difference between the columns:

- Create a new column. I have created a new column in
**Column C**and named it**Matches**. - Now write this formula on the top cell of the column:
**=IF(COUNTIF($B$2:$B$12,$A2)=0,””,$A2)**The formula checks if the value in cell**A2**exists in the range from**B2**to**B12**. If it’s found, it copies the value from**A2**; if not, it leaves the cell blank. - Press
**ENTER**. - After that, drag the
**Fill Handle**down from cell**C2**to cell**C12**.

**How to Count Duplicates in Excel**

Suppose you have a lengthy list of names and you want to count how many times one name is repeated. This method will do that for you.

Here is a long list of different brand names. To count the duplicates within a list, follow these steps:

- First, create two new columns to find the duplicate values. I have named them
**Brands-2**and**Count**. - Insert the values that you want to count their number of occurrences.
- Insert the following formula in cell
**D2**:**=COUNTIFS(A2:A20,C2)** - Press
**ENTER**. It will show the formula result. - Finally, drag the
**Fill Handle**down from**D2**to**D5.**

**4+ Ways to Find Duplicates in a Column and Delete Rows in Excel****Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]****How to Remove Duplicates in Excel [13 + Different Methods]**

**Conclusion**

I hope you got an overall idea about all possible techniques to find the duplicate values in columns in Excel. Use whichever method you feel comfortable with. Also if you have any questions or confusion regarding this topic, feel free to comment here. I will try to respond as soon as possible.

## Frequently Asked Questions

### What is the fastest way to find duplicates in Excel?

The fastest way to find duplicates in Excel is by using the **Conditional Formatting** feature:

- Select the column or range where you want to find duplicates.
- Go to the
**Home**tab. - Click on
**Conditional Formatting**. - Choose
**Highlight Cells Rules**and then**Duplicate Values**.

Excel will instantly highlight duplicate values in the selected range, allowing for quick identification and analysis.

### How do I filter only duplicates?

To filter only duplicates in Excel, use the following steps:

- Select the column containing potential duplicates.
- Go to the
**Data**tab. - Click on
**Remove Duplicates**. - Choose the column(s) where you want to find duplicates.
- Click
**OK**.

This will filter and display only the duplicate values in the selected column(s). Verify and manage duplicates as needed.

This is really good information but hasn’t helped what I am trying to accomplish. Maybe you may be able to get me in the right direction.

I have a master list of part numbers with the quantities three different locations have on hand. On another workbook I have a list of the parts each location has on order.

I am trying to compare the master list of items to the list of orders. I can run different formulas but I think having the same part number ordered is throwing the formulas off. I was simply trying to run a rule to get a true/false next to the item number on the master list if it has been ordered.

Thank you for your comment and for sharing your specific scenario. I understand that comparing part numbers between the master list and the list of orders can be a bit tricky, especially if the formulas are impacted by the presence of the same part number in both lists.

To achieve your goal of marking items on the master list as “ordered” or “not ordered,” you can use a combination of formulas or methods in Excel. Here’s a suggestion:

On the master list sheet, you can use the VLOOKUP function or INDEX and MATCH functions to check if each part number in the master list exists in the list of orders.

This would involve creating a new column next to the item numbers with a formula like:

=IF(ISNUMBER(MATCH(A2, Orders!$A$2:$A$100, 0)), “Ordered”, “Not Ordered”)

Here, A2 is the reference to the part number in the master list, and Orders!$A$2:$A$100 is the range of part numbers on the orders sheet.

Please adjust the cell references and ranges based on the actual layout and size of your data. If you encounter any specific challenges or have additional requirements, feel free to provide more details, and I’d be happy to assist you further.