Home

 › 

Articles

 › 

How to Delete Duplicates in Excel Permanently

excel

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

excel
Selecting cells in Excel.

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

excel
Selecting Conditional Formatting

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

excel
Selecting Duplicate Values under Highlight Cells Rules.

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

excel
Selecting Duplicate under Duplicate Values.

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. 

Here is a quick, step-by-step video for further reference:

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

excel
Selecting the range of data in Excel.

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.  

excel
Selecting Remove Duplicates.

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

excel
Selecting columns with duplicates.

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

excel
Excel searching for duplicates.

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

excel
Using the Advanced Filter.

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 

excel
Checking the Unique Records Only box.

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

excel
Highlighting the data to remove from the sheet.

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

excel
Selecting Duplicate Values under Conditional Formatting.

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

excel
Highlighting duplicates from the dialog box.

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

excel
Entering 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

excel
Applying the function to other cells.
excel
Dragging the function down.

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

excel
Selecting the column with the COUNTIF function.

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

excel
Clicking on the Filter function.

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

excel
Selecting rows not equal to 1.

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

excel
Checking which values appear 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.

excel
Restoring hidden values.
excel
Deleting the COUNTIF row.

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

excel
Selecting the Data option in Google Sheets.

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

excel
Selecting Remove duplicates under Data cleanup.

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

excel
Checking for the Data has header row option.

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

excel
Selecting Remove duplicates under Columns to analyze.

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.

excel
Inserting the UNIQUE function.

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

excel
Getting a copy of the unique data.

#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. 

excel
Dragging the cells to copy the function.

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:

Frequently Asked Questions

Why is Excel not removing all duplicates?

If Excel does not remove duplicates, the values probably have different formats, and excel takes them as unique. For instance, the values 10 and 10.00 are unique to Excel.

This is also possible if one of the unique values have extra space. Excel sees the extra space as a character, making the values unique.

Which is the quickest way to delete duplicates in Excel?

The quickest way to delete duplicates in Excel is by using the Remove duplicates option. You select the data you want to remove, click on Remove duplicates under the data ribbon, and delete them.

Can you identify duplicates without deleting them?

To highlight the dupes before deleting them, you will use the Conditional Formatting option found under the home ribbon. Under it, select Highlight Cell Rules and then Duplicate values.

What is the difference between filtering and deleting duplicate cells?

Filtering duplicate cells means temporarily hiding them. On the other hand, deleting duplicate cells means permanently removing duplicate values.

Should you remove duplicates in Excel?

You should remove duplicates in Excel since having them can lead to an inaccurate dataset. This can later affect other decisions made using an analysis.

To top