How to Remove Duplicate Entries in Excel?

Microsoft Excel is a program that’s used worldwide for different purposes but mainly for business analysis, but the main issue arises in this application when the data on it starts duplicating, and you cannot find ways to remove it. Especially if you have tons of data and information you are tackling, this issue can become a bigger hassle and cause frustration. The repeated entries on your Excel spreadsheet create redundancy and can cause many problems if the matter is not handled well.

So, how can one remove duplicate entries in an Excel sheet? To know the answer to this question, continue reading this article.

Three ways to Remove duplicate entries in Excel

There are a few ways you can overcome data duplication problems in Excel spreadsheets, and we will explain the process below.

1. Through the Remove Duplicates Tool

A tool named Remove Duplicates is available in all Microsoft Excels post-2007, which can help remove duplicate entries. With the help of this tool, you can look for duplicate entries and subsequently remove them that can consist of individual cells or entire rows. To use this tool, follow these steps:

You must select the row or cells which have the duplicate entries
Click on the “Data” tab on the Excel sheet
Then click on “Data Tools:
The option of Remove Duplicate will become visible, so click on that
When the dialogue box opens, select all the columns and select the duplicate ones based on the comparison
If your data consists of column headers, choose the option “My data has headers
Click on OK
Excel will then display a dialogue box summarising the deleted duplicate entries.

This means your work here is done, and you shouldn’t have any issues with duplicate entries, and neither do you have to waste time in maj=nually selecting and deleting each duplicate entry on your Excel spreadsheet.

2. Through Conditional Formatting

You can also remove duplicate entries with the help of conditional formatting, which is the method you should use if you need that duplicate data in some places but not in all. To do this, follow these steps.

Select the cells that you want to check for duplicate entries
Go to the Home tab on the toolbar
Click on Conditional Formatting
Click on Highlight Cells Rules
Select the option of Duplicate values
Format the duplicate entries based on your requirement
Click on Ok once you are done formatting and remove any unnecessary duplicates that appear on your Excel sheet

This should enable you to easily remove duplicate entries in Excel without worrying about removing other necessary duplicate entries and removing the ones that you need to remove.

3. Through Power Query Tools

Power Query tool is another effective way to remove duplicate entries from your Excel sheet to use this tool effectively, follow these steps:

Select a cell
Go to the Data tab
Select the Get and Transform Data Section
Click on Table/Range
A box with a power query table will appear on your screen; make sure to recheck the selected cells or range that you have selected for the duplicate entries.
Click on the option of Ok; once you are sure.
A Power Query editor window will appear with two options of whether removing the duplicate; entire table or one column, or more.
To remove based on one or more columns, select the required column header and right-click on it.

(You may select more columns with the help of the CTRL button and remove duplicate entries accordingly)

To remove based on the table, locate the button on the left corner on top in the data preview and further select the option of Remove duplicate.
This will help remove duplicate values from your entries
Click on the Close and Load option, and you will get the updated version of your Excel sheet without duplicate values

This is yet another way of removing duplicate entries in your Excel sheet, so if you don’t understand the above two ways or for some reason, they are not working out for you, you could always try this one, and it might work out for you!

FAQs

How do I delete duplicates in Excel but keep one?

To delete duplicates in Excel but keep one of them, you can use the Advanced Filter option, but it would be advised to keep a backup spreadsheet in case something goes wrong. To use the Advanced filter option do the following:

Choose a cell from the spreadsheet and click on Data
Select the option of Advanced filter which is available on the far right corner
Choose from wither the option of Copy to another location (this will make a copy of the list) or Filter the list, in place (this will hide the rows of duplicate data entries on your Excel spreadsheet)
Select the Unique records only option
Click on Ok, and viola, Excel will remove all the duplicate entries

Does Excel delete both duplicates?

In general, Excel doesn’t delete all duplicate entries, rather the first entry of all the duplicate entries is kept whereas the other ones are deleted.

What is the Excel formula to remove duplicates?

To remove duplicate data entries in Excel you can also use a formula to make your work easy which is =UNIQUE() and this will make life easier for you.

How to Find Duplicates in Excel?

To find duplicates in Excel follow the steps below:

Select the range of A1:C10
Click on the Home tab
Select Styles Group
Choose conditional formatting
Choose the option of Highlight Cells Rules
Further, select Duplicate values
Choose a formatting style best suited to you
Click on Ok

Conclusion

In the end, we can state that whenever you face a problem of having duplicate entries on your Excel you don’t have to worry about not being able to remove it. Make sure to follow each step carefully, or it may result in you losing some important data.

We hope this article simplified the process for you and you are not clueless the next time you face an issue about duplicate entries!

Read also:

How to Add a Printer to an iPhone

How To Fix Can’t Hear Anyone on Discord

How To Turn Off Tumblr Safe Mode

How to Fix Your Organization's Data Cannot be Pasted Here?

How to Remove Duplicate Entries in Excel?