© Kaspars Grinvalds/Shutterstock.com

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.

©History-Computer.com

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

excel
Selecting Conditional Formatting

©History-Computer.com

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

excel
Selecting Duplicate Values under Highlight Cells Rules.

©History-Computer.com

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

excel
Selecting Duplicate under Duplicate Values.

©History-Computer.com

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

excel
Selecting the range of data in Excel.

©History-Computer.com

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.

©History-Computer.com

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

excel
Selecting columns with duplicates.

©History-Computer.com

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.

©History-Computer.com

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.

©History-Computer.com

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.

©History-Computer.com

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.

©History-Computer.com

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.

©History-Computer.com

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.

©History-Computer.com

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.

©History-Computer.com

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.

©History-Computer.com

excel
Dragging the function down.

©History-Computer.com

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.

©History-Computer.com

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

excel
Clicking on the Filter function.

©History-Computer.com

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

excel
Selecting rows not equal to 1.

©History-Computer.com

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

excel
Checking which values appear more than once.

©History-Computer.com

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.

©History-Computer.com

excel
Deleting the COUNTIF row.

©History-Computer.com

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.

©History-Computer.com

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

excel
Selecting Remove duplicates under Data cleanup.

©History-Computer.com

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

excel
Checking for the Data has header row option.

©History-Computer.com

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

excel
Selecting Remove duplicates under Columns to analyze.

©History-Computer.com

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.

©History-Computer.com

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

excel
Getting a copy of the unique data.

©History-Computer.com

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

©History-Computer.com

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 Delete Duplicates in Excel Permanently FAQs (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.

About the Author

Follow Me On:

More from History-Computer

  • https://support.microsoft.com/en-us/office/filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2#:~:text=In%20Excel%2C%20there%20are%20several,%3E%20Data%20Tools%20%3E%20Remove%20Duplicates. (1970) Microsoft support
  • https://www.simplilearn.com/tutorials/excel-tutorial/remove-excel-duplicate (1970) Simpli learn
  • https://www.ablebits.com/office-addins-blog/remove-duplicate-rows-values-excel/ (1970) Able bits
  • https://www.makeuseof.com/remove-duplicates-in-excel/ (1970) Make use of
  • https://www.freecodecamp.org/news/how-to-remove-duplicates-in-excel-delete-duplicate-rows-with-a-few-clicks/ (1970) Free code camp
  • https://www.indiatoday.in/information/story/how-to-remove-duplicates-in-excel-1963021-2022-06-16 (1970) India today
  • https://www.digitaltrends.com/computing/how-to-remove-duplicates-in-excel/ (1970) Digital trends