Excel is a popular tool, but it can be difficult to find and delete duplicates in the spreadsheet. When working with large datasets, removing duplicates is a common operation in Excel. In a spreadsheet, duplicate entries can occur when you merge data from multiple tables or when many users update the same document. This information is, thus, superfluous. The likelihood of seeing duplicate records increases as dataset size grows. If they are not properly discovered and dealt with, they might cause problems.
Using a sample sports dataset, we’ll show you how to quickly and easily delete duplicates in Excel. The 2012 Olympic medalists are included in this data set.
Seek Out And Eliminate Duplicates
While having duplicates can be helpful in some situations, it typically only complicates things. It’s preferable to find, highlight, and review duplicates before deleting them altogether.
In This Article, We Will Teach You How To Delete Duplicate Data In Excel:
In Excel, you can use an in-built function to assist you in removing duplicate data points. The procedure for eliminating duplicates in Excel will be examined here.
Step 1: Select the data from which you wish to remove duplicates by clicking on a single cell or a range of cells. Excel will figure out the range for you if you just click on one cell.
Step 2: Go to the “Remove Duplicates” menu and click it.
The DATA button, the Data Tools subheading, and the Remove Duplicates option.
Step 3: A dialogue box like the one seen here should open up. You can choose which columns to compare and use to look for duplicates.
To proceed if your data has column names, check the box labeled “My Data Has Headers,” and then press the “OK” button.
When the header option is selected, the first row is ignored when searching for and deleting duplicates.
Step 4: Excel will prompt you to confirm the deletion of the duplicate rows. The number of unique values and the total number of duplicates discovered and deleted are both displayed in the dialogue box.
Step 5: You can see that the duplicates have been eliminated.
Move forward with me as I explain how to use Excel’s Advanced Filter to get rid of duplicates.
Master The Process Of Removing Duplicates From A Dataset By Filtering For Unique Values
With the goal of obtaining a list of distinct values, you can either filter for unique values or eliminate duplicates. Each activity contributes to a larger goal. But there is a major distinction between the two. Filtering for unique values temporarily hides the duplicate values, while the remove duplicate values option deletes them permanently.
It’s also important to realize that the value displayed in a cell has no bearing on whether or not the value contained in the cell is a duplicate. If two cells both have the value “3/9/2022,” they are considered to have two different values, regardless of whether or not they are structured as a date.
Make it a habit to double-check before you delete anything in case it’s a duplicate. If you can’t get the desired results by just filtering, try conditionally formatting the unique values instead.
Filter For Unique Values
Follow these instructions to isolate one-of-a-kind values:
- To get started, pick the range of cells to examine. It’s up to you to make sure that the currently selected cell is located within a table.
- The next step is to go to the Sort & Filter section and pick the Advanced filter option.
Select “Advanced” in the Data tab’s “Sort & Filter” sub-section.
- Third, a window labeled “Advanced Filter” will display. One of the following options is available to you:
- Select Filter the list in place if you need to narrow down the selection of cells or tables without leaving your current workspace.
- Follow these steps if you need to save the filtered data somewhere else:
- To copy the values, select “Copy to another location” from the menu.
- Type the cell reference into the “Copy to” box where the values should be copied.
- To temporarily hide the pop-up window, select “Collapse Dialog” (). To continue, choose a cell on the worksheet and then go to the “Expand” menu ().
- Mark the box labeled “Unique records only,” and then press OK.