# 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.

## 1. Find and Highlight Duplicates in Two Columns in Excel Using Conditional Formatting

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

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

## 2. Find Duplicates in Two Columns in Excel Using True/False 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:

1. Add a new column. I have named the new column ‘Result’.
2. Write this formula in the first cell of that column: =A2=B2
3. 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.
4. Now double-click on the Fill Handle. The results will come up as TRUE or FALSE indicating a match or mismatch respectively.

## 3. Find Duplicates in Two Columns in Excel Using the 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:

1. Create a new column first. I have named the new column header ‘Result’.
2. 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.
3. Then press ENTER.
4. 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.

## 4. Create a New Rule to Find Duplicates in Two Columns in Excel

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:

1. Select the two columns you want to compare.
2. Then go to the Home tab.
3. Click on the Conditional Formatting in the Styles group.
4. Now hit ‘New Rule’ from the Conditional Formatting drop-down list. A dialog box called New Formatting Rule will come along.
5. From the dialog box, select Use a Formula to Determine Which Cells to Format as a rule type.
6. Now type this formula inside the Format Values Where This Formula Is True bar: =\$A1=\$B1
7. Then click on the Format button. The Format Cells dialog box will show up.
8. Now, change the Background Color under the Fill section as per your preference. For example, I have used yellow to highlight.
9. 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.

## 5. Match Two Columns and Output the Third in Excel

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:

1. Select a cell.
2. Type the following formula: =VLOOKUP(E2,\$A\$2:\$B\$12,2,FALSE)
3. After that, press the ENTER key.
4. Now drag down the Fill Handle over the range of cell range F2:F6. And done! You will get your desired results this way.

## 6. Use the “Duplicate Values” Command to Find Duplicates in Two Columns in Excel

1. Select the two columns you want to compare.
2. Then go to the Home tab from the ribbon.
3. Click on the Conditional Formatting drop-down in the Styles group.
4. 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.
5. Check that the “Duplicate” option is selected.
6. 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:

## 7. Compare Two Columns in Excel and Return the Differences Using IF & COUNTIF Functions

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

1. Create a new column. I have created a new column in Column C and named it Matches.
2. 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.
3. Press ENTER.
4. 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:

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

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.

## Similar Posts

1. Mark G says:

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.

1. Rhidi Barma says:

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.