How to Extract Duplicates in Excel
Last Updated on October 17, 2024
If you use Microsoft to work on large databases or even consolidate several small worksheets into bigger ones, chances are that you have run into duplicates. In Excel, duplicates refer to values in a row that are the same as those in another row or multiple rows.
Duplicates can appear in your worksheet by double-typing or copy-pasting files to consolidate a larger worksheet. However, regardless of the type of error that created the duplicates, their presence in a worksheet makes it less useful and can alter the result of an analysis.
You can learn more advanced options if you take Microsoft Excel online training, but this article will explore easy ways to highlight and remove duplicates from your worksheets.
Extracting Duplicates From an Excel Worksheet
The larger the dataset, the higher the chance of duplicates. However, you may want to review duplicates first to ensure they are redundant instead of outright removing them. Because of this, learning how to highlight duplicates is essential.
Here are the options to find and extract duplicates from your worksheets:
Highlighting With Conditional Formatting
This method is used when you want to review duplicates first. Click ‘Home’ in the top left of your screen, then select ‘Conditional Formatting’ > ‘Highlight Cell Rules’ > ‘Duplicate Values.’ A drop-down window will appear.
In the drop-down window, find ‘Format With.’ Click on it to choose the color scheme that will highlight the duplicates in your worksheet. Click ‘Done,’ and all the duplicates will be highlighted in the color scheme you chose. Now, you can easily review the duplicates and manually remove rows in Excel based on criteria.
Highlighting the COUNTIF Function
If you have a list of items in column A and want to check for duplicates, you can use the COUNTIF function. For this function, input = ‘COUNTIF(A:A, A2)>1’ in B2, then drag the fill handle to copy the formula in other B cells.
Excel will return the Boolean values TRUE for duplicate cells and FALSE for unique cells. You can now review the cells and manually remove duplicates. Understanding the types of Excel data types will help you better use the COUNTIF function.
Removing Duplicates in Excel
Excel allows you to automatically remove duplicates if you don’t want to review them first. To do this, highlight all the cells from which you want to extract the duplicates. Select the ‘Data’ tab, and choose ‘Remove Duplicates.’
In the pop-up window, select which columns you’d like to include in your search for duplicate data and click ‘OK.’ If you want to remove duplicates from column headers, select the ‘My data has headers’ option and click ‘OK.’
Excel will let you know how many duplicates were removed from your worksheet and how many unique values remain.
Using the UNIQUE Function
The UNIQUE function will command Excel to scan your worksheet, remove duplicates, and return unique values. This function will not replace your existing data but will extract the unique values into another part of your worksheet.
To use this function, select an empty column of your worksheet. In the second cell (let’s say G2), enter the cell range you want to extract the duplicates from, for example, ‘=UNIQUE(A2:E17).’
In the empty column you have selected, Excel will extract and paste the unique values from the selected cell range.
Extracting Duplicates Using Unique Values
Select the range of cells you want to sort for to extract duplicates from your spreadsheet. Select the ‘Data’ tab at the top of your screen. In the ‘Sort and Filter’ group, click ‘Advanced Filter.’ The ‘Advanced Filter’ popup will appear on your screen.
Select ‘Copy to another location’ and enter the reference cells from which Excel will extract the duplicates. You can use the ‘Collapse Dialogue’ option ‘( )’ to hide the window and manually copy empty cells where the values will be placed. Click the ‘Expand’ option when you are done.
Mark the ‘Unique records only’ option box, and click ‘OK.’ Excel will automatically extract the duplicates from your datasheet and paste them into your chosen reference cells.
Closing Thoughts
There are multiple ways to find duplicates in an Excel worksheet. The method you decide to use will depend on if you want to review the duplicates first or not. Before removing duplicates, we recommend that you save a copy of the original file in case you ever need to return to the original worksheet.
