5 Ways to Remove Duplicate Rows Based on One Column in Excel

When you have to delete or remove duplicate data from a large dataset, doing it manually may cost you a lot of time. But Excel has a few cool tricks to help you make the process much easier and more time efficient. So, today we are going to help you learn those tricks to remove duplicate rows based on one column in Excel with examples.

Contents hide

📁 Download Excel File

Learn to Remove Duplicate Rows Based on One Column in Excel with these 5 Methods

1. Applying Remove Duplicates from Data Tools

2. Using COUNTIF with Filter

3. Administering Advanced Option From Sort & Filter

4. Utilizing Pivot Table

5. Executing VBA

📄 Important Notes

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

For a better understanding, download this file.

Remove Duplicate Rows Based on One Column.xlsm

Learn to Remove Duplicate Rows Based on One Column in Excel with these 5 Methods

Today we are going to show you 5 simplistic methods to single out duplicate values based on one column and how you can remove them. We are going to use a list of books that are categorized by Genre. Since we have recurring genres, we will keep one of every genre and remove the rest of them. Let’s go to the Excel worksheet to see how we can filter and remove duplicate rows based on one column.

Method 1

1. Applying Remove Duplicates from Data Tools

Our first method maybe is the most used method to remove duplicate rows based one column in Excel. You can clearly figure that out simply by noticing the name. To remove duplicates Excel has a couple of built-in features. Let’s show you the first one.

⬇️⬇️ STEPS ⬇️⬇️

First, you have to select the range where you want Excel to find duplicates. In our case, we’ll be selecting the range B5:D20.
Next, go to the Data Tab, where you’ll see the Data Tools. Here, click the Remove Duplicates option.

A Remove Duplicates dialogue box will appear before you. In the box, you will see all of your column headers. For reassurance, check the box My data has headers. If you don’t have headers in your columns Excel will just name them column 1, column 2, column 3, and so on.

Now, in the checkboxes, we will have to check the ones we want to find duplicates and remove them. Select just those columns in order to eliminate partial duplicates based on one or more key columns. Since we want unique values of Genre, we will check the box beside Genre and uncheck the rest.

That’s it. You have your unique data of Genre and the duplicates are gone. A message box will also notify you how many duplicate values Excel removed and how many unique values remained.

Method 2

2. Using COUNTIF with Filter

There is another way to remove duplicate rows in Excel by using the COUNTIF function in conjunction with the Filter feature. To use the COUNTIF function first we will create another column where we will count how many times one type’s Genre reappears. Then we will remove those which appear more than 1 by using the Filter option. Then follow these steps with me.

⬇️⬇️ STEPS ⬇️⬇️

Select cell E6.
Then we will write the following formula.

=COUNTIF($C$6:$C6,C6)

Press Enter and drag the Fill Handle downward to copy the formula in all the Rows. You’ll see some of the rows are showing values of more than 1. Now, we’ll eliminate them.

Next, selecting the entire table, we shall go to Home Tab in the Ribbon, where we will select Sort & Filter and subsequently Filter.

As a result, there will be some Filter buttons beside every Column header just like in the picture. Click the drop-down button beside ‘Counting no’ and filter options will open up.

Since we want to remove the duplicates we will select everything except the one with value 1 and click OK.

Excel will show duplicate values.

Now delete them.

Next, open up Filter options by clicking the ‘Counting no’ Filter button. Check the box behind 1 and press OK.

You’ll be left with the unique rows without the duplicate ones.

Method 3

3. Administering Advanced Option From Sort & Filter

Excel can also eliminate duplicates by distinguishing unique data using the Advanced filtering option from Sort & Filter group under Data Tab. Let’s learn how in detail.

⬇️⬇️ STEPS ⬇️⬇️

Select the column you want to filter or sort out unique values. In our case, we are selecting The Genre column, C5:C20.
Select the Sort & Filter group under the Data tab, then select the Advanced option.

An Advanced Filter dialogue window will appear where you check the Filter the list, in-place radio button, and also check the box behind Unique records only. Finally, click OK.

Unique values will be shown removing the duplicate genres.

Method 4

4. Utilizing Pivot Table

Although PIVOT tables are generally used to analyze data, we can also use them to show unique values as our preference. But keep in mind, we won’t be actually removing duplicates using a PIVOT table, rather solely showing the distinctive values from the information set. Let’s get into creating a PIVOT TABLE.

⬇️⬇️ STEPS ⬇️⬇️

First, select any cell within your dataset or the entirety of it.
Next, click on the Insert Tab and select PivotTable.

A dialogue box titled PivotTable from table or range will appear. Here, you will have to select your cell range. We selected our table range D5:D20. You will also be asked where you want to put your data. Then create a new worksheet or select the existing one. We will place our Pivot Table on our existing worksheet. Then place your location as we did by selecting M5 in the location box. Lastly, press OK to create PivotTable.

Now you may wonder what just happened as there are a lot of things going on at this moment. Two things will appear. One, a blank PivotTable, and second, PivotTable Fields. In the PivotTable Fields, our column headings will be shown and there are 4 areas where we can drag our data headings to make the table. In this example, we will use only the Rows area.

The layout of the resulting pivot table needs to be modified to make it tabular. So, go to the Design tab and select Report Layout while the pivot table is chosen.

Here, you must make changes to two selections: Show in Tabular Form and Repeat All Item Labels.

One more thing we need to do before creating the table. We must eliminate any subtotals from the pivot table. To do that, again go to the Design Tab and now select Subtotals, and then choose Do Not Show Subtotals.

Now, from the PivotTable Fields, check the box of every column of your data or the ones you want to see. We don’t need the writers’ names so we will exclude them. Remember to select the serial you want to see them. First, We will select ‘Genre’ and then ‘Books’.

Consequently, your pivot table will resemble a table of data. This pivot table will automatically ignore any duplicate values from your data because pivot tables only display unique values for items in the Rows area.

Remember, PivotTable will show unique values in the first column since it only analyses data with respect to the first data field in the Rows area.

Method 5

5. Executing VBA

Finally, utilizing the VBA script is the only remaining option to remove duplicate rows based on one column in Excel. We enabled the Developer tab on our Ribbon before beginning to write VBA code. Click on the link on how to access the Developer tab.

Now, to write our code, do the follows step by step:

⬇️⬇️ STEPS ⬇️⬇️

First, select your data range.
While staying on the Developer Tab, click Visual Basic in the left-hand corner of the Ribbon.

Then a window will appear. To get the writing module first select Insert and then Module.

Now copy and paste the following code.

Option Explicit
Sub filteringduplicaterows()
Dim unique As Range
Set unique = Selection
unique.RemoveDuplicates Columns:=Array(2), Header:=xlYes
End Sub

To apply the code, press F5 or click on Run and click Run Sub/Userform.

Your duplicates will automatically disappear from the worksheet.

📄 Important Notes

🖊️ Advanced Filter can copy your unique data to another location but only to the same worksheet.

🖊️ PivotTable can copy your data to another worksheet.

🖊️ PivotTable doesn’t remove any duplicate data, it just hides them.

📝 Takeaways from This Article

📌 We explained 4 built-in features of Excel including 1 VBA script to remove duplicates.

📌 Readers will be able to use Excel more efficiently and use the same features in other works.

📌 Explained the ups and downs of the features to avoid confusion.

Conclusion

Finally, we reach this article’s conclusion. I hope I explained the procedures clearly enough for you to grasp them. It will be a success of my efforts if you can understand them. Please leave a remark if you have any questions. I’ll do my best to react as quickly as I can. Also if you know any more interesting ways to remove duplicate rows that I haven’t described feel free to comment. Enjoy using Excel, and you can find additional tutorials at Excelden.com.Thanks.

(Visited 69 times, 1 visits today)