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, I will introduce four different techniques to either remove duplicates, select duplicates, hide duplicates, or just view your data without duplicates in Excel.
Remove duplicates with built-in functionality
Important Note: Before you remove duplicates, always make a copy of your worksheet so that you will have the original data if you accidentally delete something important.
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.

Note:
- To delete duplicate rows that have completely same values across all columns, leave the checkboxes all selected as shown above.
- To delete duplicates 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 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:
- Select Each row or Single cell according to how you want to check for duplicates.
- 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:
- 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.

Note:
- 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

Note:
- In the formula, A2, B2 and C2 are the cells in first row to check for duplicates. You should change them to the actuall 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. (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","")

Note: In the above formulas, D2 is the first cell in the Conbine column, and D10 is the last cell in the Conbine column. You should keep the dollar signs ($) the way they are in the formula.
Step 3: Filter for duplicates
- Select any cell within the dataset, and click Filter on the Data tab.
- 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.

Note: If you don't want to delete duplicates but just filter out duplicate rows, instead of selecting Duplicates in the step 3, you can select (Blanks) in the filter box. So that you can make the duplicate values invisible without deleting them.
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.

Note:
- #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.

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-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
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!