Nehad Ulfat
In Microsoft Excel, there are numerous methods available to find duplicates in a single column. We can insert a function or use a combined formula to identify the duplicates or matches in our Excel spreadsheet. In this article, you’ll get to learn all possible simple methods to find duplicate values in one column with examples and proper illustrations.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
8 Suitable Ways to Find Duplicates in One Column with Excel Formula
1. Use COUNTIF Function to Find Duplicates Along with 1st Occurrence
In the following table, there are several names lying under the Name header in Column B. And under the Duplicate header in Column C, we’ll use the COUNTIF function to find out if any name in the left column has duplicates or not. The formula will return TRUE for duplicate names and FALSE for unique ones.
In the first output Cell C5, the formula with the COUNTIF function will be:
=COUNTIF($B$5:$B$14,B5)>1After pressing Enter and auto-filling the rest of the cells in Column C, we’ll get the following results.
In this formula, the COUNTIF function returns the number of counts for each name and by using the logical operator, we have looked for the counts that are greater than 1. Thus we can identify the duplicates by looking for the boolean value ‘TRUE’ only.
Read More: Formula to Find Duplicates in Excel (6 Easy Ways)
2. Create a Formula with IF and COUNTIF Functions to Find Duplicates in One Column
We can also combine IF and COUNTIF functions to return the outputs with customized texts. Under the Output header, the formula will return ‘Duplicate’ for the duplicate names present in Column B. And if a text is unique in the Name column then the formula will return a blank for the corresponding text value.
So, the required formula combining the IF and COUNTIF functions in Cell C5 should be:
=IF(COUNTIF($B$5:$B$14,B5)>1,"Duplicate","")Now press Enter, use Fill Handle to autofill the other cells under the Output header and you’ll find the following outputs at once.
In this formula, the IF function looks for the counts of more than 1 and if found, it returns the specified text ‘Duplicate’, otherwise a blank cell.
Read More: How to Find Duplicate Values in Excel Using Formula (9 Methods)
3. Find Duplicates in One Column without 1st Occurrence in Excel
In this section, we’ll insert a formula that will display ‘Duplicate’ from the 2nd occurrence of a similar text. For example, if a name in Column B is present thrice, then the formula will return the defined text ‘Duplicate’ for the 2nd and 3rd occurrences only.
The required formula for the first output Cell C5 will be:
 =IF(COUNTIF($B$5:$B5,B5)>1,"Duplicate","")After pressing Enter and dragging down to the last cell in the Output column, we’ll get the following return values.
For the first output in Cell C5, we’ve defined the cell range with $B$5:$B5 only, and therefore, the formula will look for the first cell only to find a duplicate value. While dragging down the Fill Handle to find the next outputs, the number of cells in the defined range for the COUNTIF function increases by 1 for each successive cell. So, the 1st occurrence of any text in the Name column is not counted for more than 1 here.
Read More: How to Find Duplicates in a Column Using Excel VBA (5 Ways)
4. Excel Formula to Find Case-Sensitive Duplicates in a Single Column
Now we’ll apply another combined formula to find the case-sensitive duplicates. We’re going to combine the IF, SUM, and EXACT functions here. The EXACT function checks whether two strings are exactly the same. The SUM function simply sums the numerical values.
In the first output Cell C5, the combined formula with the referred functions will be:
=IF(SUM((--EXACT($B$5:$B$14,B5)))<=1,"","Duplicate")Now press Enter and autofill the entire column to find all return values.
If you notice, you’ll find that the name ‘Fred’ is present thrice in the Name column. But the formula has returned ‘Duplicate’ for the first two occurrences only and the third one is ignored since its first letter case does not match with the other ones.
🔎 How Does the Formula Work?
- The EXACT function here looks for the case-sensitive and exact matches for the first text in the Name column and thereby returns the following output:
{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
- With the use of double-unary (–), the return values convert into numbers, ‘1’ for TRUE and ‘0’ for FALSE. So, the return values here will be:
{1;0;0;0;0;0;0;0;0;0}
- The SUM function then sums up all the numeric values found in the preceding step.
- =SUM((–EXACT($B$5:$B$14, B5)))<=1: This part of the formula checks if the sum or the return value found in the last step is equal to or less than 1.
- Finally, the IF function looks for the sum less than or equal to 1 and returns a blank cell, and if not found then it returns the defined text ‘Duplicate’.
- The formula becomes applicable for the rest of the cells in the Output column after we fill down the first cell.
Read More: Find Duplicates in Two Columns in Excel (6 Suitable Approaches)
Similar Readings
- Excel Find Duplicates in Column and Delete Row (4 Quick Ways)
- Excel Find Duplicate Rows Based on Multiple Columns
- How to Find Duplicate Rows in Excel (5 Quick Ways)
- Excel Top 10 List with Duplicates (2 Ways)
- How to Compare Rows in Excel for Duplicates
5. Find Sequence of Duplicates with Excel Formula
In this section, we’ll look for the duplicates with the COUNTIF function, and then it’ll return the sequential number of each occurrence of the similar text in the Output column.
The required formula in the first output Cell C5 is:
=COUNTIF($B$5:$B5,B5)Press Enter, fill down the entire column and you’ll get the following return values. In the picture below, the name Fred is present thrice and in the highlighted output cells, you’re seeing the sequential numbers for all duplicates including the 1st occurrence as well.
In this formula, we have used the relative cell reference for the selected cell range. So, when the formula starts to go down in the Output column, it intakes the increased number of cells up to the corresponding output cell only. Thus the duplicate text in all next cells gets ignored until the formula reaches down that corresponding duplicate value.
6. Filter and Delete Duplicates in One Column in Excel
After applying the formula to find the duplicate values, we can filter and delete them right away. In the picture below, the output data have been found by following the previous method. Now we’ll go through the next steps to meet our objectives in this section.
📌 Step 1:
➤ Select the entire table first including its headers.
➤ Under the Home tab, select the option Filter from the Sort & Filter drop-down in the Editing group of commands.
So, we’ve just activated the Filter buttons for our headers in the following table.
📌 Step 2:
➤ Click on the Output drop-down and unmark the first option showing the numeric value ‘1’.
➤ Now press OK.
We’re now seeing the duplicate texts without their 1st occurrences in the filtered table below.
📌 Step 3:
➤ Select the cells containing the names and corresponding outputs.
➤ And delete them all.
📌 Step 4:
➤ Open the Output filter again.
➤ Mark the option showing ‘1’ only.
➤ Press Enter and you’re done.
Like in the following screenshot, now you’ll get all the unique text data or names only.
Read More: How to Find & Remove Duplicate Rows in Excel
7. Excel Formula to Find Duplicates in One Column Based on Condition
We can also insert a condition and find out the duplicates accordingly for the values lying in a column. In the picture below, we have an additional column now that represents the departments for all employees in an organization.
Now we may have two employees with a similar name but in different departments. And one of their names with the corresponding department might be present with duplications in the following dataset. By combining the IF and COUNTIFS functions, we’ll now look for those duplicate rows.
The required formula in the first output Cell D5 will be:
=IF(COUNTIFS($B$5:$B$14,B5,$C$5:$C$14,C5)>1,"Duplicate","")
After pressing Enter and filling down the entire Output column, we’ll get the return values as shown below.
In Column B, we have the name ‘Fred’ thrice but only of them is with the Sales department (Row 7). The other two occurrences of the similar name are with the Marketing department (Row 9 and Row 13). So, one of them is a duplication. Thus, we can input multiple conditions in the COUNTIFS function to find the required output.
Read More: Find Matches or Duplicate Values in Excel
8. Find and Highlight Duplicates with Conditional Formatting Rule
In the last section, we’ll find out the duplicates and highlight them by using the Conditional Formatting command. We’ll assign a formula with the COUNTIF function in the Rule Box and then define the format of the cells where our formula will return the boolean value ‘TRUE’ only.
Let’s follow the steps below now to meet the criteria:
📌 Step 1:
➤ Select all the names under the Name header in Column B.
➤ Under the Home ribbon, choose the option New Rule from the Conditional Formatting drop-down.
A dialog box named New Formatting Rule will appear.
📌 Step 2:
➤ Select the Rule Type as ‘Use a formula to determine which cells to format’.
➤ In the Rule Description box, embed the following formula:
=COUNTIF($B$5:$B$14,B5)>1➤ Press Format.
📌 Step 3:
➤ In the Format Cells window, switch to the Fill tab and select a background color for the duplicate cells.
➤ Press OK.
📌 Step 4:
➤ You’ll find a preview of the format of the cell as shown in the picture below.
➤ Press OK for the last time and we’re done.
Finally, you’ll notice the highlighted duplicates including the 1st occurrences as displayed in the following screenshot.
Read More: How to Find, Highlight & Remove Duplicates in Excel
Concluding Words
I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when you have to identify duplicates with formulas. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
