Skip to main content

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.


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.

  1. Select the range where you will delete rows based on duplicates in one column, and then click Data > Remove Duplicates.

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

  3. A subsequent dialog box will display the count of duplicate values found and removed. Click OK to finish and close the dialog box.


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.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

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

  2. In the opening Select Duplicate & Unique Cells dialog box, please check the Duplicates (Except 1st one) option or All duplicates (Including 1st one) option as you need in the Rule section. Then, check the Select entire rows option, and click the Ok button.

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

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

Original data:

Results after removing duplicates:

  • If selecting the Duplicates (Except 1st one) option:

  • If selecting All duplicates (Including 1st one) option:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Delete rows based on duplicates in one colum 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 tools 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.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

  1. Select the range where you want to delete rows based on duplicates in one column, and click Kutools > Merge & Split > Advanced Combine Rows.

  2. 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:
    1. 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.
    2. 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.
    3. 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.

  3. Click the OK button. And now all rows are deleted based on duplicates in the specified column, and simultaneously other columns are combined or calculated based on the duplicates in the specified column too.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Demo: delete rows based on duplicates in one column in Excel


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

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

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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

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!
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thanks a lot
This comment was minimized by the moderator on the site
Hi gwen,
The first method by Remove Duplicates feature, will remove rows from the specified range containing the column.
If you need to remove the entire rows from the whole worksheet, the Select Duplicate & Unique Cells feature of Kutools for Excel is recommended.
This comment was minimized by the moderator on the site
the first section is incorrect. When "Removing Duplicates" and only selecting the column you wish to remove duplicates from, the remaining cells are moved up. The row is not deleted!
This comment was minimized by the moderator on the site
u really helped me
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations