6
 min read

How do I remove duplicates in Excel?

3 ways to remove duplicates in the same column and across columns in Microsoft Excel

Weeding out duplicates in Excel can be a daunting task if you’re working with a large dataset. Fortunately, there are several simple strategies for managing duplicates. In this article, we'll show you how to quickly and effortlessly find and remove duplicates in Excel.


Take the below example about inspections carried out on specific dates and their findings:

Image


Let’s say you want to highlight the duplicates in the “FINDING” columns.


Quickly find duplicates using Conditional Formatting

A quick and easy way to find duplicates is by using Conditional Formatting. Conditional Formatting allows you to highlight a cell based on a criterion you set, including highlighting duplicates in a column.

Step 1: Select the desired column.

Image


Step 2: On the HOME tab, click Conditional Formatting> Highlight Cells Rules> Duplicate Values

Image

Step 3: Press OK.

Image


Result: That's it! Your selected column will have all the duplicates highlighted.

Image


Note: The limitation with using this option is that it only finds the duplicate values and doesn’t remove them.


Delete duplicate entries using the “Remove Duplicates” feature

Let’s say we both want to find the identical values and also to get remove them.

Step 1: Click any cell containing your data. Head to the “Data” tab, and look for the “Remove Duplicates” option. The screenshot below shows where you can find it.

Image


Step 2: Click on Remove Duplicates. You’ll be greeted with this dialogue box.

Image

Step 3: Select the column you want to delete duplicates from. Make sure to check the “My data has headers” option if your data has headers. Uncheck all other columns

Image

Selecting multiple columns will delete records that are exact matches in all selected columns. We will discuss this feature in a bit more detail under "Removing duplicates across multiple columns in Excel."


Step 4: Press OK. Excel will remove all the duplicates within the selected column. A dialogue box will let you know how many duplicates were removed and how many values are left.

Image

Result: Your selected column will have only unique values now.


Note: This action will delete the whole row of the duplicate entry. If you're worried about losing your data, we recommend copying the sheet somewhere else.


Filter duplicate rows using Advanced Filters

Unlike the “Remove Duplicates” option, Advanced Filters enables hiding duplicate rows instead of deleting them. You can use this option to copy unique data elsewhere while leaving the original data intact.

Step 1: Go to the “Data” tab and head to the “Sort & Filter” section. There will be an option labeled “Advanced”.

Image

Step 2: Clicking “Advanced” will open the following dialogue box where you’ll see the following options:

Image

Here, you can either filter the list where it is or copy the filtered list somewhere else on the same sheet.

Step 3 (A): To filter the list in place, select the option that says so, make sure to check the “Unique records only” checkbox, and click OK.

Result: Duplicate rows will be filtered out.

Image

Step 3 (B): Alternatively, you can copy the unique results somewhere else. Do everything the same way until step 2 but this time, select the “Copy to another location” option.

Image

Step 4: Specify an area on your sheet where the filtered unique rows will show. You can do this by clicking the “Copy to:” field and clicking any empty cell on your sheet.

Image

Step 5: Check “Unique records only” and click OK.

Image

Result: You’ll have two sets of data: one with all the rows and another with the duplicate rows filtered out.

Note: If you include multiple columns in your range, the advanced filter will filter out only rows which match other rows across all columns. For example, you can still see some duplicates in the “Finding” column, but they weren’t filtered out because the values under the “Date” and “S/N” columns were different.

Removing duplicates across multiple columns in Excel

The “Remove Duplicates” function removes the whole row if it finds a duplicate value in one column. But what if we want to delete values only if they have the same values across multiple columns?

For this, we once again use the “Remove Duplicates” function; however, we’ll do things a little differently this time.

Step 1: Head over to data and click Remove Duplicates

Image

Step 2: Here, check all the columns you want to delete duplicates from. Selecting all columns means Excel will check if the contents of each column match exactly before deleting the whole row.

       

Select columns in any combination

Selecting two columns means Excel will check if the combination of those two columns is the same before deleting the whole row.

Step 3: After you’ve made your selection, click OK

Image

Frequently asked questions
What is the shortcut to remove duplicates in Excel?

You can use key combinations to get to the commands we’ve mentioned above that help you delete duplicates. Here’s how: https://www.extendoffice.com/documents/excel/4892-excel-shortcut-to-remove-duplicates.html 

How do I remove duplicates in Excel without deleting data?

For that, we recommend using the “Advanced Filters” method discussed above. Advanced Filters only hide the duplicate rows. Clearing those filters will bring those hidden rows back.

How do I remove duplicates from a large file in Excel?

You can use any of the methods discussed above; they work with data of any size.

How do I remove duplicates but not blanks?

The “Remove Duplicates” function can remove blank cells as well. Here’s how you can keep blanks when removing duplicates: https://www.extendoffice.com/documents/excel/5366-excel-remove-duplicates-keep-blanks.html 

How do I remove duplicates from one column in Excel?

You can remove single-column duplicates easily using the “Remove duplicates” function or OneSchema.

Can you remove duplicates in a pivot table?

Yes. You can use either one of the methods we’ve discussed above to remove duplicates from pivot tables. Here’s how: https://excelinexcel.in/ms-excel/formulas/find-and-remove-duplicates/

Does remove duplicates in Excel remove both items?

This depends on the function you use. Conditional Formatting selects both values, whereas Remove Duplicates and Advanced Filters functions only remove the repeated entries and keep the first one intact.

How do I remove duplicates from the bottom?

Duplicate data is always deleted from the bottom. The top value is left intact after all its duplicates have been deleted.

How do I consolidate duplicates in Excel?

For that, Excel has the “consolidate” function. Here’s how you can use it to consolidate duplicate data: https://www.extendoffice.com/documents/excel/1268-excel-combine-duplicate-rows-and-sum.html 

Conclusion

Handling a large dataset in Excel leaves you prone to making mistakes, like having multiple instances of the same value. The Excel tools described in this article will enable you to highlight duplicate values easily, delete duplicates across a single or multiple columns, and filter duplicate rows. If you want help dealing with duplicate data while importing data from a third-party source, check out our tools at OneSchema. Stop messing around with Excel and let OneSchema do the heavy-lifting around data cleaning for you.

Want receive the best maketing insights? Subscribe now!

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Massa adipiscing in at orci semper. Urna, urna.

Thanks for joining our newsletter.
Oops! Something went wrong.