Filter data that doesn't overlap in Excel - Filter for unique values ​​in Excel

Filter data that doesn't overlap in Excel - Filter for unique values ​​in Excel Picture 1

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:

Filter data that doesn't overlap in Excel - Filter for unique values ​​in Excel Picture 2

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) .

Filter data that doesn't overlap in Excel - Filter for unique values ​​in Excel Picture 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) .

Filter data that doesn't overlap in Excel - Filter for unique values ​​in Excel Picture 4

Step 3: The result is the unique values ​​are copied to column I.

Filter data that doesn't overlap in Excel - Filter for unique values ​​in Excel Picture 5

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) .

Filter data that doesn't overlap in Excel - Filter for unique values ​​in Excel Picture 6

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.

Filter data that doesn't overlap in Excel - Filter for unique values ​​in Excel Picture 7

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) .

Filter data that doesn't overlap in Excel - Filter for unique values ​​in Excel Picture 8

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) .

Filter data that doesn't overlap in Excel - Filter for unique values ​​in Excel Picture 9

Above Software Tips showed you two ways to filter data that do not overlap in Excel. Good luck!