When finding duplicate cells in only one column or list, we can apply the COUNTIF function to quickly find out and count duplicate data at ease.
1. Select a blank cell besides the column you will find duplicates within.

2. Type in the formula =COUNTIF($C$3:$C$12, C3)>1, and press the Enter key.

3. Drag the AutoFill handle of the formula cell to copy this formula to other cells.

Notes: In the formula =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 is the column or list that you will find duplicate values within. Since the column is static when we copy the formula to other cells, it generally is absolute reference with “$”.
(2) C3 is the first cell of the specified column. It is relative reference because we need it change automatically when we copy the formula to other cells.
(3) This formula will return TRUE or FALASE. TRUE means the corresponding value is duplicate, while FALSE indicates the corresponding value is unique in the column.
(4) Sometimes, the TRUE or FALSE may not be understood intuitively. We can combine the original formula and IF function to return Duplicates directly.
=IF(COUNTIF($C$3:$C$12, C3)>1,"Duplicates","")

In some cases, you need to compare two columns and find out the duplicate values. For instance, you have two lists of names, and want to find out the duplicate names in the second list comparing with the first one. you can apply the VLOOKUP and IFERROR function to get it done easily.

1. Select a blank cell besides the second name list.

2. Enter the formula =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),""), and press the Enter key.

3. Drag the AutoFill handle of the formula cell to copy the formula to other cells as you need.

Notes: In above formula,
(1) D3 is the first cell in the second name list. The reference needs to change automatically when we copy the formula to other cells, as a result, it is relative.
(2) $B$3:$B$18 is the first name list. It is absolute reference because the range needs to keep static when we copy the formula to other cells.
(3) When a name is duplicate with names in the first list, the formula will return the name; otherwise it returns blank.
(4) You can also apply the formula =IF(COUNTIF($B$3:$B$18,D3)>0,"Duplicates","") to find out duplicate names in the second list comparing with the first list. This formula will return "Duplicates" if corresponding name is duplicate.

(5) If you need to find duplicates in two columns across two sheets, you just need to add the sheet name before the reference of the compared column. In our example, just change $B$3:$B$18 to Sheet1!$B$3:$B$18 in the formula.

Formulas introduced above do not match case when finding duplicates, says they consider "apple" is duplicate with "APPLE". Here, you can apply an array formula to find duplicate values in one column with matching cases.

1. Select a blank cell besides the column you will find duplicates within.

2. Type in the array formula =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,"","Duplicate"), and press Ctrl + Shift + Enter keys.

3. Drag the AutoFill handle of the formula cell to copy the array formula to other cells.

Notes: In above array formula,
(1) $C$3:$C$12 is the column where you need to find duplicate values. The reference is absolute because the reference is static when copying the array formula to other cells.
(2) C4 is the first cell in the column. The reference is relative, because the reference needs to change automatically when copying the array formula to other cells.
(3) If the corresponding cell is duplicate, the array formula will return "Duplicate", otherwise it returns blank.

You can apply the Conditional Formatting feature to highlight duplicate cells quickly in a column or a range.

1. Select the column where you will highlight duplicate cells.

2. Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. See screenshot below:

3. In the popping out Duplicate Values dialog, select Duplicate from the first drop-down list, select a highlight scenario from the second drop-down list, and click the OK button.

Notes: If the preset highlight scenarios cannot meet your needs, you can select Custom Format from the second drop-down list, and then choose highlight color, font, and cell borders as you need in the popping out Format Cells dialog.

Then you will see all duplicate cells are highlighted in the selected column as below screenshot shown.

Notes:  
(1) After highlighting the duplicate cells, we can easily filter these duplicates out. (Click to view how)
(2) After highlighting the duplicate cells, we can also remove the duplicates in bulk easily. (Click to view how)

Some users prefer to highlight rows based on duplicate cells in a certain column. In this situation, we can create a custom conditional formatting rule to get it done.

1. Select the range (excluding the header row) that you will highlight rows based on duplicate cells.

2. Click Home > Conditional Formatting > New Rule.

3. In the New Formatting Rule dialog,
(1) Click to select Use a formula to determine which cells to format option;
(2) Type in the formula =COUNTIF($C$3:$C$12,$C3)>1 in the Format values where this formula is true box;
Tips: In the formula, $C$3:$C$12 is the column containing duplicate cells, and $C3 is the first cell in the column.
(3) Click the Format button.

4. In the Format Cells dialog, specify the fill color, font, cell borders as you need, and click the OK buttons successively to save the settings.

So far, in the selected range, rows are highlighted based on duplicate cells in the specified column. See screenshot:

To highlight duplicate rows in a certain range, you can apply the Conditional Formatting feature to achieve it too.

1. Select the range except the header row.

2. Click Home > Conditional Formatting > New Rule.

3. In the New Formatting Rule dialog:
(1) Click to select the Use a formula to determine which cells to format option;
(2) In the Format values where this formula is true box, type in the formula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Click the Format button.

Notes: In the formula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $B$3:$B$12 is the first column in the range, and $B3 is the first cell in this column;
(2) $C$3:$C$12 is the second column in the range, and $C3 is the first cell in the column;
(3) $D$3:$D$12 is the third column in the range, and $D3 is the first cell in the column;
(4) If there are more columns in your range, you can add the column references and their first cells successively in the formula.

4. In the Format Cells dialog, specify the highlight color, font, cell borders, etc. as you need, and then click OK buttons successively to save the setting.

So far, the duplicate rows are identified and highlighted in the selected range. See screenshot:

You may have noticed that all duplicates are identified or highlighted with above methods. Sometimes, you just want to see which items are duplicated except the first instances. You can also get it done with the Conditional Formatting feature and a different formula.

1. Select the column with header.
Tips: If you need to highlight rows based on duplicates in one column except the first instances, select the range without header row.

2. Click Home > Conditional Formatting > New Rule.

3. In the New Formatting Rule dialog:
(1) Click to highlight the Use a formula to determine which cells to format option;
(2) In the Format values where this formula is true box, type in the formula =COUNTIF($C$3:$C3, C3)>1;
Tips: To highlight rows based on duplicates in one column, type in the formula =COUNTIF($C$3:$C3, $C3)>1.
(3) Click the Format button.

4. In the popping out Format Cells dialog, specify the highlight color, font, cell borders, etc. as you need, and then click OK buttons to save the settings.

Then you will see the duplicate cells except the first instances in the selected column (or rows based on duplicates in the specified column) are highlighted. See screenshot:

When we highlight duplicate cells or rows with the Conditional Formatting feature, all duplicates are highlighted with the same color. However, if different series of duplicates values are highlighted with different colors, it will be easier to read and get the differences. Here, a VBA can help you get it done in Excel.

1. Press Alt + F11 keys together to open the Microsoft Visual Basic for Applications window.

2. In the window, click Insert > Module, and then paste below code into the new module window.

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. Press F5 key or click the Run icon to run this VBA.

4. In the popping out Kutools for Excel dialog, select the column where you will highlight duplicate cells with different colors, and click the OK button.

Then you will see each series of duplicate values is highlighted with a color.

The first tool I will introduce is the Select Duplicate & Unique Cells feature, provided by Kutools for Excel. This feature can quickly find out the unique or duplicate cells easily.

1. Select the column or range where you want to find and highlight the duplicate cells.

2. Click Kutools > Select > Select Duplicate & Unique Cells.

3. In the Select Duplicate & Unique Cells dialog, check options as you need, and click the Ok buttons to finish the operation.

Notes: In the Select Duplicate & Unique Cells dialog,
(1) If you need to select or highlight all duplicates except the first instances, check the Duplicates (Except 1st one) option. Otherwise, check the All duplicates (Including 1st one) option.
(2) If you need to highlight the duplicates, tick the Fill backcolor option, and specify a highlight color as you need.
(3) If you want to select or highlight rows based on duplicates in the selected column, tick the Select entire rows option.
(4) If you want to select or highlight duplicate values with matching cases, tick the Case sensitive option.

Kutools for Excel also provides an amazing tool – Compare Cells, to help us easily find and highlight duplicate cells in two columns.

1. Click Kutools > Compare Cells to open the Select Same & Different Cells dialog.

2. In the Select Same & Different Cells dialog, specify the two columns in the Find Values in and According to boxes, check the Same values option, and tick other options as you need.

Notes:
(1) If you need to find duplicate rows, check Each row option; and to find duplicate cells, check the Single Cell option in the Based on section;
(2) Tick the Fill backcolor option and specify a highlight color if you need to highlight the duplicate rows or cells;
(3) Tick the Selected entire rows option if you need to select or highlight the entire row based on the duplicates;
(4) Tick the Case sensitive option if you want to find or highlight case-sensitive duplicates.

3. Click Ok buttons successively to finish the settings.

Then you will see the duplicates in the column that you specified in the Find values in box are identified and highlighted.

This method will guide you to identify and highlight duplicate cells by a conditional formatting rule, and then filter by the highlight color easily in Excel.

1. Apply conditional formatting to find and highlight duplicates in the specified column. (Click to view how)

2. Click to select the column header of the specified column, and click Data > Filter.

3. Go ahead to click the filter icon  in the column header, and select Filter by Color, and then select the specified conditional formatting color in the drop-down list. See screenshot:

Then you will see only the rows with duplicate cells are filtered out. See screenshot:

Alternatively, we can also identify duplicates with formula in a helper column, and then filter duplicates with the helper column easily in Excel.

1. Besides the original data, add a helper column, and type Duplicate as column header.

2. Select the first blank cell under the column header, enter the formula =IF(COUNTIF($C$3:$C$12,C3)>1,"Duplicate",""), and drag the AutoFill handle of this cell to copy this formula to other cells.

Notes: In above formula, $C$3:$C$12 is the column containing duplicate data, and C3 is the first cell (except the header cell) in the column.

3. Click to select the column header – Duplicates, and click Data > Filter.

4. Then click the filter icon  in the column header, only tick Duplicate, and click the OK button. See screenshot:

So far, rows with duplicate values are filtered out only. See screenshot:

In general, we can apply the =COUNTIF(range, criteria) to count the total number of a certain value appearing in the specified range. Says counting how many times the “Apple” appears in the list A2:A10, We can apply the formula =COUNTIF(A2:A10, "Apple") to count the number of this duplicate value.

However, the formula =COUNTIF(range, criteria) only count the specified duplicate value. How about counting duplicate value with two or multiple criteria? And what if counting case-sensitive duplicates with criteria? Below methods can help you solve these problems.

We can apply an array formula to count case-sensitive duplicate values with criteria in Excel. For instance, to count how many times the value "Apple" appears in the list B2:B21 with matching cases, you can get it done as follows:

1. Select a blank cell.

2. Enter the formula =SUM(--EXACT(B2:B20,E2)).

3. Press Ctrl + Shift + Enter to return the counting result.

Notes: In the array formula,
(1) B2:B20 is the column that you will count duplicates within.
(2) E2 is the cell containing the specified value that you want to count the number of occurrences. You can change the cell reference to the value with quotation marks, says "Apple".

Sometimes, you may want to count the duplicates with two or more criteria. You can apply the COUNTIFS function to get it done.
For example, there is a fruit sales table as below screenshot shown. Here we need to count the repetition times of apple, which was sold on 7/5/2020 and the sales amount is greater than 300. You can count the duplicates with these criteria as follows:

1. Select a blank cell.

2. Enter the formula =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3. Press the Enter key to get the counting result.

Notes: In the above formula,
(1) B3:B20 is the (first) date column, and G4 is the date criteria;
(2) C3:C20 is the (second) fruit column, and G3 is the fruit criteria;
(3) D3:D20 is the (third) amount column, and ">300" is the amount criteria.
(4) If there are more columns and criteria in your table, you can add the column reference and criteria.

To count all duplicates in a column except the first occurrences, please do as follows:

1. Select a blank cell beside the column.

2. Enter the formula =IF(COUNTIF($B$3:B3,B3)>1,"YES",""), and then drag the AutoFill handle down to copy this formula to other cells.

Notes: In above formula,
(1) $B$3:B3 is the range you count the duplicates within. In $B$3:B3, B3 will change automatically when you copy the formula to other cells.
(2) B3 is the first cell in the specified column.
(3) This formula will return YES or blank. YES indicates the corresponding value is duplicate, while blank means unique.

Then all duplicates in the specified column are identified. We can count the formula results to get the total number of duplicates.

3. Select a blank cell.

4. Enter the formula =COUNTIF(C3:C16,"YES"), and press the Enter key.

Notes: In above formula,
(1) C3:C16 is the range we applied formula to identify duplicates in last step.
(2) YES is the value returned by last formula.

Then we get the total number of duplicate values within the specified column. The total number of duplicates does not include the first occurrences.

To count the number of all duplicates including the first occurrences in Excel, you can apply an array formula to achieve it.

1. Select a blank cell.

2. Enter the formula =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

3. Press Ctrl + Shift + Enter to return the counting result.

Notes: In above formula, B3:B16 is the specified column we want to count duplicates including the first occurrences within.

To simplify your work and relieve yourself from memorizing the long boring formulas, you can try the Select Duplicate & Unique Cells feature, provides by Kutools for Excel, to quickly count the number of duplicate values in the specified list or column excluding or including the first occurrences.

1. Select the column where you will count the number of duplicate values, and click Kutools > Select > Select Duplicate & Unique Cells.

2. In the Select Duplicate & Unique Cells dialog, check the Duplicates (Except 1st one) or All duplicates (Including 1st one) option as you need, and click the Ok button.

3. Then all duplicate values including or excluding the first occurrences are selected, and simultaneously a dialog comes out and shows how many cells have been selected. See screenshot above.

Says you want to compare two name lists and count the number of duplicates between them, how could you solve this problem quickly? We can also get it done by a formula in Excel.

1. Select a blank cell.

2. Enter the formula =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))).

3. Press the Enter key.

Notes: In above formula,
(1) B3:B12 is the first column of names you will count duplicates within.
(2) D3:D18 is the second column of names you will count duplicates based on.

Alternatively, we can use a third-party add-in, Kutools for Excel, to quickly count the total number of duplicate cells between two columns easily.

1. Click Kutools > Select > Select Same & Different Cells.

2. In the Select Same & Difference Cells dialog,
(1) Specify the two columns in the Find values in and According to boxes separately.
(2) Check the Single cell option.
(3) Check the Same values option.

4. Click the Ok button.

Then all duplicate cells in the first column are selected, and simultaneously a dialog prompts and shows how many duplicate cells have been selected. See screenshot:

Notes: This feature will count the total number of duplicate values in the column that you specified in the Find values in box in the Select Same & Different Cells dialog. If you need to count the total number of duplicate values in the second column, apply the Select Same & Different Cells feature again with specifying the second column in the Find values in box.

You can quickly count each duplicate value once with a formula as follows:

1. Select a blank cell.

2. Enter the formula =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&"")).

3. Press the Enter key to get the counting result.

Notes: In above formula, C3:C19 is the specified column that you want to count each duplicate value once.

When counting a list, each duplicate value can be counted once with matching cases, you can apply an array formula to get it done easily in Excel.

1. Select a blank cell.

2. Enter the array formula =SUM(IFERROR(1/IF(C3:C19<>"", FREQUENCY(IF(EXACT(C3:C19, TRANSPOSE(C3:C19)), MATCH(ROW(C3:C19), ROW(C3:C19)), ""), MATCH(ROW(C3:C19), ROW(C3:C19))), 0), 0)).

3. Press Ctrl + Shift + Enter keys together to return the counting result.

Notes: In above array formula, C3:C19 is the specified column where you will count each series of duplicates once with matching cases.

If you have Kutools for Excel installed, you can also apply its Count cells with unique values feature to quickly count each series of duplicate values once in Excel.

1. Select a blank cell.

2. Click Kutools > Formula Helper > Statistical > Count cells with unique values (include the first duplicate value).

3. In the Formula Helper dialog, specify the column you will count duplicates once in the Range box, and click the Ok button.

Then the counting result is filled in the selected cell immediately.

We can apply the Subtotal feature to count each series of duplicate values in a column in Excel.

1. Select the column that you will count each series of duplicate values within, and click Data > Sort A to Z or Sort Z to A.

2. In the popping out Sort Warning dialog, check the Expand the selection option, and click the Sort button.

Then you will see the selection has been sorted by the duplicate values of the specified column.

3. Keep the selection selected, and click Data > Subtotal.

4. In the Subtotal dialog,
(1) Select the specified column from the At each change in drop-down list;
(2) Select Count from the Use function drop-down list;
(3) Only tick the specified column in the Add subtotal to list box;
(4) Click the OK button.

Then you will see each series of duplicate value is counted, and the counting result is added below each series of duplicate value, see above screenshot.

We also can create a pivot table to quickly count each series of duplicate value easily in Excel.

1. Select the range containing the specified column, and click Insert > PivotTable.

2. In the Create PivotTable dialog, specify the place you will locate the new pivot table, and click the OK button.

3. In the PivotTable Fields pane, drag the specified column to both Rows and Values sections. Then you will see each series of duplicate value in the specified column are counted in bulk. See screenshot:

If you have installed Kutools for Excel already, you can apply its easy-to-use Advanced Combine Rows feature to quickly count each series of duplicate value in the specified column.

Notes: The Advanced Combined Rows feature will modify the selected range and remove rows based on duplicate values in the specified primary key column. To save your data, it is recommended to backup or copy your data to other place before below operations.

1. Add a blank column right to the original data range, and name the new column as Count.

2. Select the original data range and the new column together, and click Kutools > Merge & Split > Advanced Combine Rows.

3. In the Advanced Combine Rows dialog,
(1) Click to select the specified column that you will count each series of duplicate value, and click Primary Key.
(2) Click to select the new column (Count), and then click Calculate > Count.
(3) Specify combination or calculation types for other columns if necessary.
(4) Click the Ok button.

Then you will see each series duplicate value in the specified column are counted in bulk. See screenshot:

Says there is a list of fruits in a column. Within the list, some fruits appear many times. Now you need to mark each duplicate fruit in the order it appears, how could you solve it? Here, this section will introduce a formula to get it done easily in Excel.

1. Add a blank column right to the original data.

2. Enter the formula =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") in the first cell of the added blank column.

3. Drag the AutoFill handle of this formula cell to copy the formula to other cells.

Notes: In above formula,
(1) $C$3:$C$14 is the specified column that you want to count the duplicate values in order.
(2) C3 is the first cell in the specified column.
(3) If corresponding value is duplicate, this formula will return sequence number 1, 2, 3… based on the appearance order; if corresponding value is unique, this formula will return blank.

You can apply the Remove Duplicates feature to remove all duplicate values except the first occurrences directly.

1. Select the column where you want to remove all duplicate values except the first occurrences.

2. Click Data > Remove duplicates.

3. In the Remove Duplicates Warning dialog, check the Continue with the current selection option, and click the Remove Duplicates button.

Tips: To remove rows based on the duplicate values in the selection, check the Expand the selection option.

4. In the Remove Duplicates dialog, only check the specified column, and click the OK button.

Tips: If you have checked the Expand the selection option in last step, all columns will be listed here. Even so, you need to check the specified column only.

5. Then a dialog prompts and shows how many duplicate values have been removed. Click the OK button to close it.

You can also apply the Advanced Filter feature to remove all duplicate values from the specified column easily.

1. Click Data > Advanced.

2. In the Advanced Filter dialog,
(1) Check the Copy to another location option;
(2) In the List range box, select the specified column you will remove duplicate values from;
(3) In the Copy to box, specify the range you will paste the column to;
(4) Tick the Unique records only option.
(5) Click the OK button.

Then you will see the specified column is pasted to the specified range with all duplicate values removed except the first occurrences. See screenshot:

You can also apply a VBA to quickly remove duplicate values except the first occurrences from a column in Excel.

1. Press Alt + F11 keys to open the Microsoft Visual Basic for Application window.

2. Click Insert > Module, and then paste below VBA code into the new module window.

Sub ExtendOffice_RemoveAllDeplicate()
Dim xRg As Range
Dim xURg, xFRg, xFFRg As Range
Dim xI, xFNum, xFFNum As Integer
Dim xDc As Object
Dim xDc_keys
Dim xBol As Boolean
Dim xStr As String
Dim xWs As Worksheet
Dim xURgAddress As String
On Error Resume Next
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xURg = Intersect(xRg.Worksheet.UsedRange, xRg)
Set xWs = xURg.Worksheet
Set xDc = CreateObject("scripting.dictionary")
xURgAddress = xURg.Address
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If (Not IsError(xFRg)) Then
If xFRg.Value <> "" And (Not IsError(xFRg)) Then
For xFFNum = xFNum + 1 To xURg.Count
Set xFFRg = xURg.Item(xFFNum)
If Not IsError(xFFRg) Then
If xFFRg.Value = xFRg.Value Then
xDc(xFFRg.Address) = ""
End If
End If
Next
End If
End If
Next
xStr = ""
xDc_keys = xDc.Keys

For xI = 1 To UBound(xDc_keys)
If xStr = "" Then
xStr = xDc_keys(xI)
Set xURg = xWs.Range(xStr)
Else
xStr = xStr & "," & xDc_keys(xI)
Set xURg = Application.Union(xWs.Range(xDc_keys(xI)), xURg)
End If
Next
Debug.Print xStr
xWs.Activate
xURg.Select
Selection.Delete Shift:=xlUp
xWs.Range(xURgAddress).Select
Application.ScreenUpdating = xBol
End Sub

3. Press F5 key or click the Run button to run this VBA.

4. In the popping out dialog, specify the range you will remove duplicate values from, and click the OK button.

Then all duplicate values except the first occurrences are removed immediately from the specified range.

Note: This VBA code is case sensitive.

We can highlight all duplicate values including the first occurrences in a column or a list with a conditional formatting rule, and then filter out all duplicate values by the highlighting color. After that, we can select all filtered out duplicate cells and then remove them in bulk.

1. Apply the conditional formatting to highlight duplicate values. (Click to view how)

2. Select the column you will remove duplicate values (including the first occurrences) from, and click Data > Filter.

3. Click the Filter icon  in the column header of the specified column. In the drop-down menu, select Filter by Color, and then specify the highlight color from the submenu.

Then all duplicate values are filtered out.

4. Select all filtered cells, right click, and select Delete Row from the context menu. And in the popping out reconfirming dialog click OK button to go ahead.

5. Then all duplicate values are removed in bulk. Keep the filtered list selected, and click Filter > Data again to cancel the filter.

Till now, you will see all duplicate cells including the first occurrences are removed in bulk, and only unique values are left.

Notes: This method will remove rows based on the duplicate values in the specified column.

We can also apply a formula to identify duplicate values including the first occurrences in a helper column, then filter out duplicate values by the formula results, and finally remove these filtered out duplicate values in bulk.

1. Add a helper column beside the specified column, enter the formula =COUNTIF($B$3:$B$11,B3) into the first cell of helper column, and then drag the AutoFill handle down to copy this formula to other cells. See screenshot:

Note: In above formula, $B$3:$B$11 is the specified column you will remove duplicate values from, and B3 is the first cell in the specified column.

2. Select the helper column, and click Data > Filter.

3. Click the filter icon  in the helper column header, then in the drop-down menu check all values except 1, and click the OK button. See screenshot:

4. Now all duplicate values are filtered out. Select the filtered-out cells in the helper column, right click, and select Delete Row in the context menu.

5. In the popping out reconfirming dialog, click the OK button to go ahead.

6. Now all duplicate values and their rows are removed in bulk. Go ahead to click Data > Filter again to cancel the filter.

Then you will see all duplicate values including the first occurrences are deleted in bulk.

If you have Kutools for Excel installed, you can also apply its Select Duplicate & Unique Cells feature to quickly select and delete the duplicate values including or excluding the first occurrences easily in Excel.

1. Select the column you will remove duplicates from.

2. Click Kutools > Select > Select Duplicate & Unique Cells.

3. In the Select Duplicate & Unique Cells dialog, check the All duplicates (Including 1st one) option, and click the Ok button.

Notes:
(1) To select and delete duplicate values excluding the first occurrences, check the Duplicates (Except 1st one) option.
(2) To select and delete rows based on duplicate values in the specified column, check the Select entire rows option.
(3) To select and delete duplicate values with matching cases, check the Case sensitive option.
(4) To select, highlight, and delete duplicate cells or rows, check the Fill backcolor or Fill font color options and specify fill or font colors as you need.

4. Then a dialog prompts and shows how many cells have been selected, click the OK button to close it.

5. Right click the selected cells, and select Delete from the context menu.

6. In the coming Delete dialog, check the Shift cells up option, and click the OK button.

So far, all duplicate values including the first occurrences have been removed in bulk.

In most cases, we identify duplicate values in a column, and then remove the entire rows by the duplicate values. To be honest, this operation is quite similar as removing duplicate values from a single column. As a result, we can use the similar solutions to delete rows based on duplicates in the specified column.

The first method is to apply the built-in Remove Duplicates feature to remove rows by duplicates in the specified column. Just select the range you will remove rows, click Data > Remove Duplicates to enable the feature, only tick the specified column in the Remove Duplicates dialog, and then click OK to finish the removing operation.

We can also apply the Conditional Formatting and Filter feature to remove rows based on duplicate values in the specified column. First of all, highlight rows based on duplicate values in a certain column by a conditional formation rule (click to view how). Secondly, filter the range by color. Thirdly, delete all filtered-out rows easily. At last, clear or cancel the filter, and you will see only rows with unique values in the specified column are left.

Alternatively, you can add a helper column, and apply the formula =COUNTIF($C$3:$C$21,C3) to identify duplicates in the specified column. Then filter out numbers greater than 1 in the helper column, and remove all filtered-out rows easily. After clearing the filter, you will see rows with unique values in the specified column are left only.

The third-party add-in Kutools for Excel also brings an extraordinary handy feature, Select Duplicate & Unique Cells, to quickly select rows based on duplicate values in the specified column, and then you can quickly remove these selected rows by right-clicking menu at ease.

Kutools for Excel’s Advanced Combine Rows feature also can quickly remove rows based on the duplicate values in the specified primary key column.

We can add a helper column and apply a formula to identify the duplicate values between two columns, and then filter and delete the duplicate values easily.

1. Add a blank column beside the specified column that you will remove duplicate values from.

2. In the first cell of the helper column (excluding the header cell), type in the formula =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Unique","Duplicate"), and then drag the AutoFill handle down to copy the formula to other cells.

Notes: In above formula,
(1) C2 is the first cell in the specified column you will remove duplicate values from;
(2) $A$2:$A$13 is the other column we need to compare with.
(3) This formula will return Duplicate if the corresponding value is duplicate with values in the other column, and return Unique if different with values in the other column.

3. Select the helper column, and click Data > Filter.

4. Click the filter icon  in the helper column header, then in the drop-down menu only check Duplicate, and click the OK button.

5. Now all duplicate values are filtered out. Select the filtered cells, right click, and select Delete Row from the context menu. Then click OK in the popping up reconfirming dialog.

6. Then all duplicate values are removed from the specified column. Go ahead to click Data > Filter again to cancel filter.

Then you will see only unique values are left in the specified column. You can remove the helper column as you need.

Notes: This method will remove the entire rows based on the duplicate values in the specified column.

If you have Kutools for Excel installed, you can use its amazing Select Same & Different Cells feature to quickly select the duplicate values between two columns, and then remove them easily.

1. Click Kutools > Select > Select Same & Different Cells to enable this feature.

2. In the Select Same & Different Cells dialog, specify both columns in the Find Values in and According to boxes separately, check the Single cell and Same values options, and click the Ok button. See screenshot:

3. Then all duplicate values amount two columns are selected in the first column (the column you specified in the Find value in box). And click the OK button in the popping out dialog.

4. You can press Delete key to remove these duplicate values directly, or right click them and select Delete from the context menu.