Log in  \/ 
x
or
x
x
Register  \/ 
x

or

How to delete rows based on duplicates in one column in Excel?

We can quickly delete duplicate rows by Data > Remove Duplicates feature in Excel. But do you know how to delete entire rows based on duplicates in a single column in Excel? This article comes up with several easy methods to settle this matter.


Delete rows based on duplicates in one column with Remove Duplicates feature

This method will introduce the Remove Duplicates feature to remove entire rows based on duplicates in one column easily in Excel.

1. Select the range 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, please only check the Column whose duplicate values you will remove entire rows based on, and click the OK button. See screenshot:

3. And then a dialog box will come out and show how many duplicate values have been found and removed. Just click the OK button to close it.

And now entire rows are deleted based on the duplicate values in the specified column. See screenshot:


Delete rows based on duplicates in one column with Kutools for Excel

Sometimes, you may want to delete rows based on all duplicate values in a column, and remain rows with unique values only. In this case, you can try Kutools for Excel’s Select Duplicate & Unique Cells utility.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

1. Select the column whose duplicate values you will remove entire rows based on, and click Kutools > Select > Select Duplicate & Unique Cells. See screenshot:

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, check the Select entire rows option, and click the Ok button. See above screenshot:

3. And then a second Select Duplicates & Unique Cells dialog box comes out and shows how many rows based on the duplicates in the selected column have been selected. Click the OK button to close it.

4. Click Home > Delete > Delete Sheet Rows to delete all selected rows with duplicate values. So far you have removed all rows based on the duplicate values in the specified column. See screenshot:

Original range:

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

 

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

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Delete rows and combine/sum/average/count values in columns based on duplicates in one column

If you want to delete entire rows based on duplicate values in the specified column, but combine values in other columns based on the duplicates, or just remain the calculation results of summing/ averaging/counting, etc.) in other columns, you can try Kutools for Excel’s Advanced Combine Rows utility.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Select the range where you want to delete rows based on duplicates in one column, and click Kutools > Content > Advanced Combine Rows. See screenshot:

2. In the opening Advanced Combine Rows dialog box, please do as follows:
(1) In the Column section, select the column which contains the duplicate values, and click the Primary Key;
(2) It’s optional. In the Column section, select the column where you will combine values based on duplicates in the specified column, click Combine and then specify a separator from the drop down list. In our case we select the Price column and click the Combine > Comma.
(3) It’s optional. In the Column section, select the column where you will calculate (sum, average, count, etc.) values based on duplicates in the specified column, click the Calculate and then select a calculation type from the drop down list. In our case we select the Amount column and click the Calculate > Sum.

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. See screenshot:

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


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


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Related articles:


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-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
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!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    kellytte · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    gwen · 1 years ago
    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!
  • To post as a guest, your comment is unpublished.
    master · 1 years ago
    u really helped me