Buzz
In a spreadsheet with extensive information, identifying and filtering duplicate and non-duplicate data is challenging, especially if done manually. Fortunately, Excel provides a tool to help users filter and extract unique values effortlessly.
Use the advanced filtering tool to selectively extract unique data - Advanced Filter
For example, suppose you have the following spreadsheet and need to create a list of Store Names:
How to filter to retrieve only unique data is as follows:
Step 1: Highlight (Select) the data range to be extracted. For example, in the table above, it is B2:B12 (1) => On the Data (2) tab => Select Advanced Filter (3).
Step 2: The Advanced Filter window appears.
Action (1): If you want to replace the old data => choose Filter the list, in-place. If you want to copy unique data => choose Copy to other place.
List Range (2): This is the data range you need to filter, already highlighted in Step 1.
Copy to (3): If you choose Copy to other place in (1), you must provide the location where you want to copy to.
Select (tick) the Unique only (4) checkbox => only copy unique data.
Then press the OK (5) button.
Step 3: The result is the unique values copied to column I.
Remove Duplicates to Eliminate Duplicate Data
In the example table above, you can directly remove duplicate values from the original data table using the Remove Duplicates tool.
Step 1: Select (highlight) the data range to remove duplicate data (1) => on the Data (2) tab, click the Remove Duplicates (3) icon.
Step 2: The Remove Duplicates Warning window appears.
(1) If you want to delete entire rows containing duplicate data, choose Expand the selection. If you only want to delete cells containing duplicate data, choose Continue with the current selection.
(2) Press the Remove Duplicates button.
Step 3: The Remove Duplicates window appears.
If your headers are within the range you selected in Step 1, then check My data has header (1). Then press the OK (2) button.
Step 4: Excel will report how many duplicate and unique data (1). The duplicate rows' results will be deleted, and the data will automatically move up to replace the deleted cells' position (2).
Here, Software Tips has guided you through two ways to filter non-duplicate data in Excel. Wishing you success!
