How to delete rows based on duplicates in one column in Excel?
In Excel, removing duplicate rows is a straightforward task using the Remove Duplicates feature on the Data tab. However, the process of deleting entire rows based on duplicate values in just one column might not be as immediately obvious. This article introduces several simple methods to efficiently address this scenario, ensuring your data remains accurate and concise. Deleting rows based on duplicates in a specific column can be vital when preparing data for reporting, analysis, or when you need to maintain only unique records according to a certain criterion. The following methods range from built-in Excel functions to formula and VBA options, as well as solutions provided by Kutools for Excel, enabling users to select the most appropriate approach for their context and workflow needs. Each approach provides unique advantages based on your task complexity, data volume, and personal proficiency with Excel tools.
- Delete rows based on duplicates in one column with Remove Duplicates feature
- Delete rows based on duplicates in one column with Kutools for Excel
- Use VBA code to delete rows with duplicate values in a specific column
- Use formulas and helper columns to identify and remove duplicates in a column
- Delete rows based on duplicates in one column and combine/sum/average/count values from removed rows
Delete rows based on duplicates in one column with Remove Duplicates feature
This approach focuses on utilizing Excel's Remove Duplicates feature to effortlessly delete entire rows that contain duplicate entries in a specific column. This is one of the quickest solutions and works best when your data is already organized and you want to remove all but one occurrence of each repeated value.
- Select the range where you will delete rows based on duplicates in one column, and then click Data > Remove Duplicates.
- In the popping up Remove Duplicates dialog box, only check the box next to the column that contains the duplicate values you want to use as a basis for removing entire rows, and click the OK button.
Note: Double-check your column selection. Only the columns checked will be evaluated for duplicates. If you mistakenly check additional columns, you may not remove all intended duplicates.
- A subsequent dialog box will display the count of duplicate values found and removed. Click OK to finish and close the dialog box. This message provides feedback on the number of duplicate rows deleted and unique rows remaining for your reference.
Pros: Fast for straightforward tasks, no add-ins or formulas needed, retains the first occurrence of each duplicate.
Cons: Removes all columns from deleted rows, offers no flexibility to “review” flagged duplicates before deletion.
Delete rows based on duplicates in one column with Kutools for Excel
With Kutools for Excel’s Select Duplicate & Unique Cells utility, you can also delete rows based on all duplicate values in a column, and remain rows with unique values only. Additionally, this utility offers the flexibility to choose between deleting rows that contain duplicates except their first occurrences, or removing all duplicate rows including the first occurrences. This method is especially suitable for users who need to quickly manage duplicates with more control compared to native Excel features.
- Select the column that contains the duplicate values you want to use as a basis for row deletion, and click Kutools > Select > Select Duplicate & Unique Cells.
Tip: It’s recommended to select only the column for which you want to check duplicates to avoid unintended deletions across unrelated data.
- In the opening Select Duplicate & Unique Cells dialog box, please check the Duplicates (Except1st one) option or All duplicates (Including1st one) option as you need in the Rule section. Then, check the Select entire rows option, and click the Ok button.
Parameter explanation:
• Duplicates (Except1st one): Keep the first occurrence. All other duplicate rows are selected for deletion.
• All duplicates (Including1st one): Selects every row where the column has duplicate values, including the first occurrence. - A dialog box as shown below comes out displaying the number of rows that have been selected based on duplicate values in the selected column. Click the OK button to close it.
- Click Home > Delete > Delete Sheet Rows to delete the selected rows with duplicate values. So far you have removed all rows based on the duplicate values in the specified column.
Tip: Consider saving a backup of your file before deletion. Deleted rows cannot be restored with Undo after major further operations or saving/closing the file.
Original data:
Results after removing duplicates:
If selecting the Duplicates (Except1st one) option:
If selecting All duplicates (Including1st one) option:
Pros: Flexible selection and preview, can delete all or only certain duplicates, preserves control.
Cons: Requires installation.
Delete rows based on duplicates in a column using VBA code
This method employs a VBA macro to automatically loop through your selected data range and delete entire rows when duplicate values are found in a specific column. This solution is especially valuable for handling large datasets or repetitive clean-up tasks, and offers more automation than manual approaches. Before running VBA code, always backup your workbook, as changes made by macros cannot be easily undone.
- Click Developer > Visual Basic to open the Microsoft Visual Basic for Applications editor window. If you do not see the Developer tab, you can enable it via File > Options > Customize Ribbon.
- In the VBA editor, click Insert > Module to create a new code module, then paste the following code into the module window:
Sub DeleteRowsWithDuplicateInColumn()
Dim rng As Range
Dim i As Long, j As Long
Dim lastRow As Long
Dim colNum As Long
Dim ws As Worksheet
Dim dict As Object
On Error Resume Next
Set ws = ActiveSheet
Set rng = Application.InputBox("Select your data range (including column headers)", "KutoolsforExcel", ws.UsedRange.Address, Type:=8)
If rng Is Nothing Then Exit Sub
colNum = Application.InputBox("Enter the column number to check for duplicates (e.g.,2 for column B):", "KutoolsforExcel", 1, Type:=1)
Set dict = CreateObject("Scripting.Dictionary")
lastRow = rng.Rows.Count
For i = lastRow To 2 Step -1
If dict.Exists(rng.Cells(i, colNum).Value) Then
rng.Rows(i).EntireRow.Delete
Else
dict.Add rng.Cells(i, colNum).Value, 1
End If
Next i
End Sub3. After entering the code, click the 
button to execute the code. When prompted, select your data range (including headers), then enter the number of the column where you want to check for duplicates (e.g., enter 2 for column B).
The macro will automatically loop through the data from bottom to top, deleting any rows where a duplicate value is found in the specified column, while preserving the first occurrence of each unique value.
Identify and delete rows with duplicates using Excel formulas and helper columns
If you prefer a formula-based solution, you can use helper columns to flag duplicate values and then delete the flagged rows manually. This method is ideal when you want to visually review which rows are duplicates before deleting and gives more transparency, though some steps are manual.
1. Add a new helper column to your data table (for example, in column D) and enter the following formulas in a blank cell beside your data, for example D2:
=COUNTIF($A$2:$A2,A2)>12. Copy or fill this formula down the helper column for all relevant rows. A result of TRUE marks a duplicate, while FALSE indicates a first occurrence.
3. Apply the Filter feature to your data range including the helper column. Then filter the helper column for TRUE values—these are the duplicate rows beyond the first occurrence.
4. Select the filtered rows and use Home > Delete > Delete Sheet Rows to remove them from your table. Remove or hide the helper column afterward if desired.
5. After deleting the duplicate rows, click Filter to cancel filter and display the hidden data.
Flag all duplicate values, including their first occurrence: Use this formula in your helper column:
=COUNTIF($A$2:$A$24,A2)>1After filling down, all records where the value appears more than once in the specified range will return TRUE regardless of position. Filtering and deleting as above will remove all rows containing duplicated values, leaving only unique items.
Cons: Manual steps needed for deletion, less efficient for very large data sets.
Delete rows based on duplicates in one column and combine/sum/average/count values from removed rows
If you're looking to delete entire rows based on duplicate values in a specific column, and want to preserve data from these duplicates, please try using Kutools for Excel's Advanced Combine Rows utility. The tool enables you to efficiently remove rows with duplicate entries in a chosen column and simultaneously carry out operations such as combining, summing, averaging, or counting the data from the removed rows in other columns, thereby maintaining the integrity and usefulness of your dataset. This approach is particularly suitable when your business process requires aggregation of related data after duplicate removal, such as consolidating sales records or transaction logs.
- Select the range where you want to delete rows based on duplicates in one column, and click Kutools > Merge & Split > Advanced Combine Rows.
- In the opening Advanced Combine Rows dialog box, as an example, we will delete rows based on duplicate values in the "Fruit" column. Additionally, we will combine the price information in the "Price" column and sum the total amount for each type of fruit in the "Amount" column. Please follow these steps to execute the process:
- In the Columns list section of the dialog box, locate and select the "Fruit" column, which contains the duplicate values you want to delete rows based on, and then choose Primary Key from the Operation drop-down list.
- Select the "Price" column where we will combine all values associated with the duplicates in the specified column. And then choose a separator from the Operation drop down list under the Combine section.
- Select the "Amount" column where calculations (sum, average, count, etc.) will be applied to values based on duplicates in the specified column. And then choose the Sum option from the Operation drop down list in the Calculate group.
- Click the OK button. All rows are now deleted based on duplicates in the specified column, while other columns are simultaneously combined or calculated.
Demo: delete rows based on duplicates in one column in Excel
Related articles:
Count duplicate values in a column in Excel
Remove duplicate rows and keep highest values only in Excel
Hide rows if duplicate in 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in