How ToTips & Tricks

How to Remove Duplicates in Excel

3 Mins read
How To Remove Duplicates In Excel
How To Remove Duplicates In Excel

Microsoft Excel is a boon in the name of a tool. Large datasets which cannot be comprehended visibly are simplified using the services offered by Microsoft Excel. In today’s age, data is king. It drives the growth of multinational companies, small businesses, and solopreneurs alike and can escalate their businesses if accurate insights are derived from it. This is why it is becoming increasingly crucial to optimize data as much as possible. One of the many ways to do this is by removing redundancy from data in your Excel sheet. In this article, we shall discuss the ways to remove duplicates in Excel.

 How to Find and Highlight Duplicates in Excel


  1. Select the range of cells for which you want to find and highlight duplicate values in the cells. 
  2. You may click on a single cell and then drag the cursor through your left mouse downwards and stop after you have selected all the cells in which you want to search for duplicates OR Click on a single cell and press Shift + Downward Arrow until you have selected the entire range of cells.
select duplicates in excel
  1. On the Home tab, go to “Conditional Formatting”.
Conditional formatting option in Excel
  1. From the dropdown menu, click on “Highlight Cells Rules”.
  2. Locate the option “Duplicate values” from the menu that follows.
Find duplicate values through conditional formatting
  1. Select the kind of highlighting you want from the dropdown menu next to “values with”.
  2. Click on OK. This will find and highlight the duplicate values in your data and you can delete the ones as you like. 
highlight values to remove duplicates in excel

How to Remove Duplicates in Excel


  1. Select the range of cells for which you want to check duplicate values in the cells. 
  2. You can do this by clicking on a single cell and dragging the cursor through your left mouse downwards and stopping till you have selected all the cells within which you want to look for duplicates OR You can click on a single cell and press Shift + Downward Arrow till you have selected the entire range of cells.
  3. In the menu bar at the top of Excel, go to the Data section.
  4. Navigate to the Data Tools category in the Data section.
  5. Click on the “Remove Duplicates” button from the Data Tools section.
remove duplicates option in data tools in excel
  1. A pop-up window will be displayed. You can deselect the columns which you neither want to check for duplicate data nor want to delete such data. Make sure to deselect the “My data has headers” option if your dataset has headers.
  2. Click on OK to delete the duplicates after selecting or deselecting the columns.
Remove Duplicates pop up box in Excel
  1. After the duplicates are removed from your Excel sheet, the dialog box will display the number of duplicate values that were deleted. 
Number of duplicates removed in Excel

 Using the Advanced Filter Option


  1. Select the range of cells for which you want to find and highlight duplicate values in the cells. You can use either of the two methods mentioned above.
  2. Go to the Data tab from the menu bar at the top.
  3. In the Sort & Filter section, click on “Advanced”.
  4. Choose the “Filter the list, in-place” option if you simply want to hide the duplicate values from view OR Choose the “Copy to another location” option if you want to copy the filtered data to another location.
Advanced filter to remove duplicate values in Excel
  1. If you choose the latter, enter the cell range where you want to copy the filtered data.
  2. You may or may not enter the cell range in the “List range” and “Criteria range” fields.
  3. Click on the check box next to “Unique records only”.
Advanced filter method to get unique records only
  1. Click on OK. This will remove the duplicates from your Excel sheet within the selected range.

As you can see, there are multiple ways to find and remove duplicate values in your Excel sheet. Optimizing your data has never been this easier, has it? We hope you find these methods useful and come to a step closer to achieving a sleeker dataset.

Do comment below if you know any other methods to delete duplicate values from Excel. 

385 posts

About author
Howdy, I’m Varun Sharma. I’m an Engineer & Geek. I am a fan of technology, programming, and entrepreneurship. Also, an artist & full entertainer dose by nature.
Articles
Related posts
How ToiPhone

How to Turn Off Contact Sharing in iOS 17

5 Mins read
With the advent of iOS 17, Apple has once again pushed the boundaries of what’s possible with smartphone technology. By introducing contact…
How ToiPhone

How to Enable Contact Sharing in iOS 17

5 Mins read
In the latest iteration of Apple’s mobile operating system, iOS 17, a standout feature named NameDrop. It uses AirDrop and revolutionizes the…
How ToiPhone

How to Setup Call Forwarding on iPhone

2 Mins read
In today’s fast-paced world, managing communication effectively is crucial. However, there are times when you need uninterrupted focus or simply want to…
Intel Newsletter

Get the latest how-to guides, tips and tricks in your inbox.

Leave a Reply

Your email address will not be published. Required fields are marked *

×
GoogleHow To

How to Get The Most Out of Google Calendar