Remove duplicates with built-in functionality

Step 1: Select the cell range from which to remove duplicates

Select the cells that may have duplicate values: You can select a whole dataset; if your data is too large, you can click on any single cell inside the dataset, Excel will automatically select the dataset in the next step.

Step 2: Select Data > Remove Duplicates

Step 3: Select which columns to check for duplicates

In the pop-up dialog box, select which columns to check for duplicates, and then click on OK.

Step 4: View summary

A dialog box pops up showing how many duplicate values are found and removed, and the count of remaining unique values. Click OK.

Result

As you can see, the duplicate rows excluding their first appearances are removed.

Select duplicates with Kutools with more options

Kutools for Excel’s Select Duplicate & Unique Cells feature helps you select duplicates with more options - You could select duplicate rows (or cells) including or excluding the first appearances; or select unique rows (or cells) that appear exactly once or every distinct rows or values. You could find case-sensitive duplicates; or mark duplicate values with a specific background or font color.

To select duplicates excluding their first appearances, please select the cell range that contains duplicates, and then click Kutools > Select > Select Duplicate & Unique Cells, and do as follows:

1. Select Each row or Single cell according to how you want to check for duplicates.
2. Select Duplicates (Except 1st one) in Rule section.

Hide duplicates with Advanced Filter

If you’d rather have duplicate values hidden instead of removed, you can use Advanced Filter to filter for unique values.

Step 1: Select the cell range from which to filter out duplicates

Select the cells that may have duplicate values: You can select a whole dataset; if your data is too large, you can click on any single cell inside the dataset, Excel will automatically select the dataset in the next step.

Step 2: Select Data > Advanced

Step 3: Filter for unique values

In the pop-up dialog box, please do as follows:

1. In the Action section, select Filter the list, in-place.
2. See if the cell range in the List range box is the dataset you will filter. If not, click the range-selecting icon to select your dataset.
3. Check the Unique records only option and then click OK.

Result

You would see the row numbers where the filter is applied turned blue, which indicates that the duplicate rows (pointed by green arrows) are filtered out.

Remove duplicates with Excel formulas

In this section, we will talk about two approaches with Excel formulas to help you either delete duplicates easily with IF and COUNTIF functions, or view your data without duplicates with UNIQUE, an awesome new Excel function.

Delete duplicates with IF and COUNTIF functions

Suppose your data that may have duplicates is in range A2:C10, you can use an Excel formula with IF and COUNTIF to filter for unique values and thus to remove duplicates.

Step 1: Combine columns' values

Combine the cells on the same row with the concatenation operator (&). In our example, you should enter the below formula in cell D2, and then copy the formula to below cells.

=A2&B2&C2

Step 2: Get duplicate marks

Mark a row as duplicates by entering the one of the formulas below in cell E2 according to your needs. (Here I will use the first formula to mark duplicates excluding first occurrences.)

• Duplicates excluding first occurrences

• =IF(COUNTIF($D$2:D2,D2)>1,"Duplicates","")

• Duplicates including first occurrences

• =IF(COUNTIF($D$2:$D$10,D2)>1,"Duplicates","")

Step 3: Filter for duplicates

1. Select any cell within the dataset, and click Filter on the Data tab.
2. Cick the arrow at the top of the Mark column, and select Duplicates to keep only duplicate values visible on the screen.

Step 4: Delete duplicates

Select all the duplicate rows, then right-click and select Delete Row from the menu.

Result

On the Data tab, click on the Filter button again to remove the filter. You will see the table now contains only unique values with duplicate rows deleted.

View without duplicates with UNIQUE function (Excel 365/2021)

If you are Microsoft 365 or Excel 2021 user, and you don’t want to remove duplicates from original data, nor apply a filter, but just view the data without duplicates, Excel’s new UNIQUE function can do you good.

Step 1: Apply the UNIQUE formula

In a blank cell, type in the below formula (Note that you should replace A1:C10 with your actual cell range that may have duplicates), and then press Enter.

=UNIQUE(A1:C10)

Result

As you can see, the duplicate rows are removed from the formula result in no time.

Related articles

• Excel: How to remove duplicates ignore spaces
• Here is a list that includes some duplicate values, when you use the Remove Duplicates feature in Excel to remove the duplicate values, some duplicates will keep if there are some spaces as the below screenshot shows.
• How to automatically remove duplicates form a list in Excel?
• In Excel, we can use the Remove Duplicates feature to remove the same values from a list, but is there any trick to auto remove the duplicates from the list? In this article, I introduce the tricks on automatically removing duplicates if there is duplicate appearing in a specify Excel column.
• How to remove duplicate rows and keep highest values only in Excel?
• For example, you have a purchase table in Excel as the first screenshot shown. Now you need to remove duplicate rows based on the Fruit column, and keep highest values of corresponding fruits in the Amount column as the second screenshot shown. Any solution can you think of? This article will share two tricks to solve it.
• How to remove duplicate rows but keep the one with latest date in Excel?
• I have a range of data which contains some duplicate values in column A, and in column B is populated with some date, now, I need to remove the duplicate rows in column A but keep the latest date from corresponding column B as following screenshot shown, do you have any good ideas to solve this problem in Excel quickly and easily?
• How to remove duplicates and replace with blank cells in Excel?
• Normally when you apply Remove Duplicates command in Excel, it removes the entire duplicate rows. But sometimes, you want the blank cells to replace the duplicate values, in this situation, the Remove Duplicate command will not work. This article is going to guide you remove duplicates and replace with blank cells in Excel.
• More tips and tricks for Excel...