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
- Select the range of cells for which you want to find and highlight duplicate values in the cells.
- 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.
- On the Home tab, go to “Conditional Formatting”.
- From the dropdown menu, click on “Highlight Cells Rules”.
- Locate the option “Duplicate values” from the menu that follows.
- Select the kind of highlighting you want from the dropdown menu next to “values with”.
- Click on OK. This will find and highlight the duplicate values in your data and you can delete the ones as you like.
How to Remove Duplicates in Excel
- Select the range of cells for which you want to check duplicate values in the cells.
- 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.
- In the menu bar at the top of Excel, go to the Data section.
- Navigate to the Data Tools category in the Data section.
- Click on the “Remove Duplicates” button from the Data Tools section.
- 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.
- Click on OK to delete the duplicates after selecting or deselecting the columns.
- After the duplicates are removed from your Excel sheet, the dialog box will display the number of duplicate values that were deleted.
Using the Advanced Filter Option
- 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.
- Go to the Data tab from the menu bar at the top.
- In the Sort & Filter section, click on “Advanced”.
- 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.
- If you choose the latter, enter the cell range where you want to copy the filtered data.
- You may or may not enter the cell range in the “List range” and “Criteria range” fields.
- Click on the check box next to “Unique records only”.
- 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.