Removing duplicates in Excel is an essential task if you are working with large sets of data in Excel. Duplicate data can be useful, other times duplicates make it harder to understand your data. We have highlighted steps to follow to remove duplicate data in your Excel worksheet.
How to remove duplicates in Excel.
You can use the Remove Duplicates feature in Excel to remove duplicates in Excel:
#1. Find the Duplicates:
Select the cells you want to check its duplicates.
- Then click Home.
- Select Conditional Formatting.
- Highlight Cells Rules.
- Select Duplicate Values.
- You will find a box next to values with, pick any formatting you want to apply to the duplicate values.
- click OK.
#2. Remove duplicates:
When you find the duplicates, proceed to remove them using the “Remove Duplicates feature”.
- Select the data or range of cells you want to remove duplicates from.
- Go to the Data tab in the ribbon and click on the “Remove Duplicates” button.
- In the “Remove Duplicates” dialog box, Excel will select all the columns in the cell range. Then you can uncheck the boxes next to the columns you don’t want to include.
- Once the columns you want to remove duplicates from have been selected, click OK. Excel will remove any rows that contain identical data in the selected columns, leaving only one instance of each unique value.
Remove duplicates in Excel using Formula
There are other methods you can also use, you can use Formulas to remove duplicates based on specific criteria. Also if you don’t want to delete the duplicates but only highlight them. Here are the steps to follow:
- The first step is to insert a new column to the right of your data range by right-clicking on the column header to the right of your data and select INSERT.
- In the first cell of the new column, enter the formula below: =COUNTIF(C$1:C1,C1). This formula counts the number of times a value in the first column appears in the range up to the current row.
- Then copy the formula down the column to apply it to the entire range. Select the cell with the formula and drag the fill handle down the column to do this.
- Select the entire range , go to the Data tab ribbon and click Filter. This filters the data range by the newly created column.
- Then go ahead to filter the new column to show only values greater than 1. This will show only the duplicate values in the data range.
- From here, you decide whether to delete the duplicates or keep them.
The counts of duplicate and unique values given after remove sometimes includes empty cells, spaces, etc.
Removing duplicates in Excel does not have to be so tedious for you, simply follow the steps above to know how to remove duplicates in Excel. Remove duplicates successfully and maintain the accuracy of your data.
If you liked this tutorial, then kindly subscribe to our YouTube Channel for more Microsoft Excel video tutorials.
