Find, highlight, filter, count, delete duplicates in Excel

In Excel, duplicate data occurs time after time when we record data manually, copy data from other sources, or for other reasons. Sometimes, the duplicates are necessary and useful. However, sometimes the duplicate values lead to errors or misunderstanding. Here, this article will introduce methods to quickly identify, highlight, filter, count, delete duplicates by formulas, conditional formatting rules, third-party add-ins, etc. in Excel.

Table of Content


1. Find and highlight duplicates

When encountering duplicate values in a column or range, you probably want to quickly find out the duplicates. Here, this part talks about how to quick find or identify duplicate data in columns, and highlight duplicate cells, rows, or highlight rows based on duplicates in a certain column in Excel.

1.1 Find duplicates with formula

This section introduces some formulas to quickly find or identify duplicate values in one or two columns.

1.1.1 Find duplicate cells in one column with formula

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","")

1.1.2 Find duplicate cells in two columns with formula

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.

1.1.3 Find case-sensitive duplicate cells with 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.

1.2 Find and highlight duplicates with conditional formatting

Sometimes, you may need to mark duplicate values or rows to remind or warn yourself or your readers. This section will guide you to highlight supplicate cells or rows with conditional formatting rules.

1.2.1 Find and highlight duplicate cells with conditional formatting

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)

1.2.2 Find and highlight rows based on duplicate cells

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:

1.2.3 Find and highlight duplicate rows with conditional formatting

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:

1.2.4 Find and highlight duplicates except the first instances

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:

1.3 Find and highlight duplicates in different colors

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.

VBA: Highlight duplicate cells in different colors:

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.

1.4 Find and highlight duplicates with a third-party add-in

In this section, some easy-to-use tools provided by a third-party addon will be recommended to quickly find, select, highlight duplicate cells or rows based on duplicates in one column with ease.

1.4.1 Find and highlight duplicate cells in one column

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.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

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.

1.4.2 Find and highlight duplicate cells in two columns or sheets

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

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

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.


2. Filter duplicates

Sometimes, duplicates occur in a column, and we want to view the records related to the duplicate data only. Therefore, in this part, I will introduce two solutions to filter out duplicate data only.

2.1 Filter duplicates with conditional formatting

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:

2.2 Filter duplicates with a helper column

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:


3. Count duplicates

This part will guide you to count the number of duplicate values in Excel. It will introduce methods about counting duplicates with criteria, counting the total number of duplicates, counting duplicates only once, and counting each duplicate value in bulk, etc.

3.1 Count duplicate values with criteria

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.

3.1.1 Count case-sensitive duplicates with criteria

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

3.1.2 Count duplicates with multiple criteria

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.

3.2 Count total number of duplicates in one column

Supposing there is a series of values in a column, you want to count the total number of duplicates in the list, how could you deal with it? Here, this section will show you the guidance to count the total number of duplicate values in one column in Excel.

3.2.1 Count duplicates in a column excluding the first occurrence

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.

3.2.2 Count duplicates in a column including the first occurrence

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.

3.2.3 Count duplicates in a column including/excluding the first occurrences

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.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

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.

3.3 Count duplicates in two columns

3.3.1 Count duplicates between two columns with formula

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.

3.3.2 Count duplicates between two columns with a third-party add-in

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.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

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.

3.4 Count duplicates only once

Sometimes, there are duplicate values in the column. When we count values in the column, we need to count the duplicates once. Take an example, there are a series of values A, A, B, C, C, C, D, E, E, and we require to count the values and get 5 (A, B, C, D, E). Here, this section will introduce two formulas to solve this problem.

3.4.1 Count each duplicate value once with formula

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.

3.4.2 Count case-sensitive duplicate value once with array formula

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.

3.4.3 Count each duplicate value once with third-party add-in

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.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

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.

3.5 Count each duplicate value in one column

In general, we can use the COUNTIF function to count one duplicate value at a time, and repeat the operations to count other duplicate values one by one. However, this solution will waste a lot of time for multiple duplicates. Here, this section will introduce three solutions to quickly finish this work at ease in Excel.

3.5.1 Count each duplicate value in one column with SUBTOTAL function

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.

3.5.2 Count each duplicate value in one column with PivotTable

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:

3.5.3 Count each duplicate value in one column with an amazing tool

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.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

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:

3.6 Count duplicates in order

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.


4. Delete duplicates

When a number of duplicate values stacking in a column or a range, some users seek for easy ways to quickly remove the duplicate values. Here, this part will introduce multiple solutions to delete duplicate values at ease in Excel.

4.1 Delete duplicates except one in a column

This section will show you the tutorial to quickly remove duplicate values except the first occurrence from a column or list in Excel.

4.1.1 Delete duplicates except one with Remove Duplicates feature

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.

4.1.2 Delete duplicates except one with Advanced Filter feature

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:

4.1.3 Delete duplicates except one with VBA

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.

VBA: Remove duplication values except the first occurrences

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.

4.2 Delete duplicates and original

In general, we usually find out duplicate values and remove duplicates except the first occurrences. However, in some cases, some users prefer to remove all duplicate values including the original ones. And this section brings some solutions to deal with this issue.

4.2.1 Delete all duplicates and original values with Conditional Formatting

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.

4.2.2 Delete all duplicates and original values with a helper 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.

4.2.3 Delete all duplicates and original values with an amazing tool

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.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

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.

4.3 Delete rows based on duplicates in one column

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.

4.4 Delete duplicates in two columns

Sometimes, we need to compare two lists or columns, and then remove the duplicates between them in Excel. Here, this section brings two solutions for you.

4.4.1 Delete duplicates in two columns with helper 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.

4.4.2 Delete duplicates in two columns with an amazing tool

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.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

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.


More articles ...


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.