In a spreadsheet with large information data, it is difficult to check and filter which data is duplicated and which is not. Especially if you manually / eye check it is very time consuming. Thankfully, Excel has a tool that makes it possible for users to filter for duplicate values in Excel (unique values).
In a spreadsheet with large information data, it is difficult to check and filter which data is duplicated and which is not. Especially if you manually / eye check it is very time consuming. Thankfully, Excel has a tool that makes it possible for users to filter for duplicate values in Excel (unique values).
Use the advanced filtering tool to retrieve only the unique Advanced Filter data
For example, you have the following spreadsheet to create a Store Name list:
The filtering method to retrieve only unique data is as follows:
Step 1: Highlight (Select) the data to retrieve. As an example, the table above is B2: B12 (1) => On the Data tab (2) => Select Advanced Filter (3) .
Step 2: Advanced Filter window appears.
Item Action (1) : If you want to replace the old data => Filter the list, in-place . If you want to copy only data => select Copy to other place .
Items List Range (2) : It is the need to filter the data that you have blacked out in Step 1 .
Item Copy to (3) : If you choose Copy to other place in (1) , you will have to fill in the location that you want to copy.
Select ( tick ) the Unique only box (4) => copy only data.
Then press the OK button (5) .
Step 3: The result is the unique values are copied to column I.
Delete duplicate data with Remove Duplicates
For the above table example, you can delete the duplicate values directly on the original data table with the Remove Duplicates tool .
Step 1: Select (highlight) the data area to be deleted (1) => on the Data tab (2), click the Remove Duplicates icon (3) .
Step 2: The Remove Dupilcates Warning window appears.
(1) If you want to delete the entire line containing duplicate data, select Expand the selection . If you just want to delete the cell containing the duplicate data, select Continue with the current selection .
(2) Click the Remove Duplicates button.
Step 3: The Remove Duplicates window appears.
If your title is in the area you selected in Step 1 , then select the My data has header (1) check box . Then press the OK button (2) .
Step 4: Excel will notify the result of how much duplicate data and how much unique data (1) . The resulting duplicate lines will be deleted and the data will automatically be moved to replace the deleted cell position (2) .
Above Software Tips showed you two ways to filter data that do not overlap in Excel. Good luck!