Remove duplicates in Excel (Easy step-by-step tutorial)
Duplicate values can make information less clear and more difficult to get an accurate assessment. In this tutorial, we'll explore four distinct methods to remove, select or hide duplicates, or simply view your data without duplicates in Excel.
Video: Remove duplicates in Excel
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.
- To delete duplicate rows with completely identical values across all columns, leave the checkboxes all selected as shown above.
- To delete duplicate rows with the same values in certain columns, check those columns only.
- If your data has column headers, check the My data has headers option, so that the header (first row) will not be considered for removing duplicates.
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 row or value. 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:
- Select Each row or Single cell according to how you want to check for duplicates.
- Select Duplicates (Except 1st one) in Rule section.
- To remove the selected duplicate rows, right click on any of the selected rows, and then select Delete to delete the duplicate rows.
- To use the Select Duplicate & Unique Cells feature, you should have Kutools for Excel installed on your computer, please click here to download and install. The professional Excel add-in offers a 30-day free trial with no limitations.
- To know more about the Select Duplicate & Unique Cells feature, take a look at this tutorial: Quickly select the duplicate or unique values or entire rows from a column or a range.
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:
- In the Action section, select Filter the list, in-place.
- 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.
- 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.
- You can also get rid of duplicates by copying only unique values (including the first duplicate occurrences) to another worksheet or workbook by selecting Copy to another location in the Advanced Filter dialog box in Step 3, and then specifying the first cell of the destination range where to paste the filter results in the Copy to box.
- You can click on Clear in Sort & Filter group on Data tab to clear the filter and reverse the data to its original look.
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
- In the formula, A2, B2 and C2 are cells in the first row to check for duplicates. You should change them to the actual cells in first row of your data.
- If your data only contains one column, please skip this step.
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, and copy the formula to below cells. (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
- Select any cell within the dataset, and click Filter on the Data tab.
- Click 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.
- #SPILL errors are returned when the spill range (E1:G8 in this case) for the formula isn't blank.
- With the formula, you can easily view your dataset without duplicates. However, you could not edit the formula result. In order to edit the cell value or formats, you can use Kutools’ To Actual feature, which converts formula results to constant values in one click.
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 them with blank cells in Excel.
- More tips and tricks for Excel...
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
