How to Delete Duplicates in Excel Permanently

Key points

• Permanently deleting dupes means that you remove them forever.
• It is possible to delete duplicate cells manually, especially if your worksheet is not large.
• The Remove duplicate feature is an in-built command available in the data ribbon.

Excel is one program that can seem too good to be true. It has features that help store, organize, and get meaning out of a vast amount of data quickly and accurately. 

While there are many reasons to praise Excel, it has a downside – duplicates. These mainly occur when you import data into an excel sheet. They can also occur as a database continues to grow or if many people are accessing the worksheet. These duplicates can affect your analysis and mess up your data. 

Fortunately, Excel has features to help rectify this problem. Although you can delete the dupes manually, it’ll be both faster and more accurate to use these excel features. Let’s get started.

How to Identify Duplicates in Excel

Permanently deleting dupes means that you remove them forever. Therefore, before you confirm that you want to continue, it is wise to highlight the cells to confirm that they’re the ones you want to delete. Here are the steps to follow:

Step 1: Select all the cells that you want to check for updates

Step 2: On the Home tab, go to the styles group and click on conditional formatting

Step 3: Select Highlight Cells under Conditional Formatting, then duplicate values

Step 4: Click OK on the pop-up that appears

After you’ve clicked OK, excel highlights all duplicate content. You can then confirm whether the content selected is the one you want to do away with. 

4 Methods for Deleting Duplicates in Excel

It is possible to delete duplicate cells manually, especially if your worksheet is not large. However, if you have a large database, it becomes hectic. So, why struggle when you have excel features to help you out?

Here are the different ways in which you can delete these dupes permanently.  

#1: Using the Remove Duplicates Feature

The Remove duplicate feature is an in-built command available in the data ribbon. Here is how to use it. 

Step 1: Select the range of data you wish to highlight on the Excel sheet

Step 2: Under the data ribbon, click on Remove Duplicates

In case you have not selected all the data, excel gives you the option to expand your search. Click OK on this option.  

Step 3: Under Columns, mark the boxes in which you would like duplicates to be deleted

Excel automatically ignores the first column since it assumes that these are headers. If your work has no titles, uncheck the headers section so that excel can check duplicates from this section. 

Step 4: Click OK to prompt Excel to search the selected section

After eliminating the dupes, a pop-up shows you the number of duplicates removed and how many unique values remain. If there were no duplicate values, Excel says it couldn’t find any.

#2: Using Advanced Filters

The Advanced Filters option removes data by hiding it from the dataset. This feature applies when deleting duplicates from the entire table, since there is no option to delete data partially. However, it allows you to delete duplicates and save a copy of the original. Here are the steps to follow:

Step 1: Select the data, then click on Advanced under the data ribbon

Step 2: On the pop-up, choose either Copy to another location or Filter the list

Since you want to delete the duplicates permanently, select the second option to make a copy of the data in another location. The Filter the list, in-place option will temporarily hide the dupes. 

Fill in the Copy to section after checking the Copy to another location option. The location should be another cell in the same sheet, as shown above. You can leave the “list range” and “criteria range” empty.  

Step 3: Check the Unique Records Only box to eliminate the duplicates 

After clicking OK, Excel deletes all the duplicate data. The system always assumes that the document’s first row is a header; therefore, duplicates are not removed in this row. You will have to delete any duplicates there manually. 

#3: Delete Duplicates Using Conditional Formatting

Conditional Formatting helps you delete duplicates by setting clear rules and conditions. This feature also allows you to highlight the duplicates first, then confirm that this is what you intend to delete. Here are the three steps to follow:

Step 1: Select the data that you want to remove

Step 2: Under the Home tab, click Conditional Formatting, hover over to Highlight Cell Rules, and select Duplicate Values

Step 3: In the dialog box, select how you want the duplicates to be highlighted, then click OK

After the duplicates have been highlighted, you can manually delete them. This method is good to use when some of the duplicates are not accidental.

#4: Delete Duplicates in Excel Using a Formula

Excel uses functions and formulas in various situations and when deleting duplicates, you can use the COUNTIF function. However, this function only works when the cell’s content is less than 256 characters long.

There is also a command that highlights all the unique features, but this is only available for Google Sheets and Microsoft Excel 365. Here are the steps to follow when using the COUNTIF function:

Step 1: Go to the next cell in the adjacent column and enter the COUNTIF function

The function to enter is =COUNTIF ( A$2:A2, A2 ), as shown above. This function will show the number of occurrences of each name. 

Step 2: Drag down to the rest of the cells to apply the same function and count the number of occurrences

Step 3: After identifying the duplicates, delete them manually

However, if the data is too large to delete manually, you can delete it using Excel Autofilter to delete them. To do this:

i. Select the column with the COUNTIF function

ii. On the Data tab, click on the filter function, and a drop-down appears above the column 

iii. Select the rows that are not equal to 1, then click OK. 

iv. After clicking OK, you get a spreadsheet with all values that occur more than once. 

v. Highlight these values, then right-click and select Delete. This will delete the duplicates and restore the unique values that were previously hidden.

vi. Delete the COUNTIF row, and you will restore the original spreadsheet format without the duplicates. 

How Do You Remove Duplicates in Google Sheets?

Deleting duplicates from Google Sheets is not so very different from deleting them in Microsoft Excel. Here are some of the ways in which you can delete these duplicates. 

#1: Deleting Duplicates Using the Remove Duplicates Tool 

Step 1: Select the dataset you want to check for duplicates, then select the data option

Step 2: Under Data, go to Data cleanup and click the Remove duplicates option

Step 3: In the dialog box, check that the Data has header row option

Step 4: Under Columns to analyze, check Select all and click on Remove duplicates

This process will immediately remove all the duplicates without impacting the data around the cells. 

#2: Delete Duplicates Using the Unique Function 

Google Sheets has a UNIQUE function that you can use to eliminate all duplicates. Just insert the UNIQUE function and type the range of data you want to check the duplicates, as shown below.

When you click OK, you get a copy of the unique data from your record.

#3: Delete Duplicates Using the COUNTIF Function

The COUNTIF function highlights duplicates in the Google Sheet, which you can then delete. Here are the steps to follow:

Step 1: Click on a column next to the data you want to check, and insert the formula

=COUNTIF(A$2:A2,A2)>1

This formula will count the number of times each entry appears in the worksheet. If the value shows once, the formula will give the FALSE result. However, if it shows up twice, the count will be 2, making the formula TRUE. Drag down the cell to show the results for the rest of the cells. 

Step 2: Select the TRUE values and delete them

You can then delete the COUNTIF column to get back the original dataset. 

Wrapping it Up

There are several ways to delete duplicates in Microsoft Excel and Google Sheets. You can select the method you find simplest, or the one you favor based on the duplicates you want to delete. It is wise to always have a copy of the original before deleting the duplicates in case you get rid of something you didn’t mean to.

Up Next…

Check out some of this awesome content:

• How to Convert Excel to Google Sheets in 5 Easy Steps
• How to Convert CSV to Excel in 3 Easy Steps
• How to Convert Excel to PDF in Easy Steps