Filter for unique values or remove duplicate values

In Excel, you can filter for unique values or remove duplicate values to clean up your data. Here’s how you can do each:

1. Filter for Unique Values:

If you want to view only unique values in a range or column, Excel has a built-in feature that allows you to filter out duplicates.

Steps to Filter for Unique Values:

1. Select the Data Range:
   • Click on the column or range of cells that you want to filter. Make sure the range has headers, if applicable.
2. Apply a Filter:
   • Go to the Data tab on the ribbon.
   • In the Sort & Filter group, click Filter. Small drop-down arrows will appear in the column headers.
3. Filter Unique Values:
   • Click the drop-down arrow in the header of the column you want to filter.
   • Choose Text Filters (for text) or Number Filters (for numbers), then select Custom Filter.
   • In the custom filter dialog, choose equals and enter the value or criteria you want to filter for.

Alternatively, you can use the Advanced Filter to directly filter unique records:

• Go to the Data tab and click Advanced in the Sort & Filter group.
• In the Advanced Filter dialog box, check Unique records only, then click OK.

2. Remove Duplicate Values:

If you want to remove duplicate values from a range or column, Excel provides a simple tool to do that.

Steps to Remove Duplicates:

1. Select the Data Range:
   • Highlight the range of cells or select the entire column where you want to remove duplicates.
2. Open the Remove Duplicates Tool:
   • Go to the Data tab on the ribbon.
   • In the Data Tools group, click Remove Duplicates.
3. Select Columns to Check for Duplicates:
   • In the Remove Duplicates dialog box, Excel will display all columns in the selected range. By default, all columns are selected.
   • If you want to remove duplicates based on specific columns, uncheck the columns you don’t need to check.
4. Click OK:
   • Click OK to remove duplicates. Excel will display a message telling you how many duplicates were removed and how many unique values remain.

3. Remove Duplicates Using Conditional Formatting (Alternative Method):

If you prefer not to remove the duplicates outright but want to highlight them, you can use Conditional Formatting to mark duplicates.

Steps for Conditional Formatting:

1. Select the Data Range:
   • Highlight the range where you want to check for duplicates.
2. Apply Conditional Formatting:
   • Go to the Home tab on the ribbon.
   • In the Styles group, click Conditional Formatting, then select Highlight Cells Rules, and choose Duplicate Values.
   • In the dialog box, select the formatting style you want to use for the duplicates and click OK.

This highlights the duplicate values in your range, making it easy to visually identify them.

4. Filter Out Duplicates Without Removing Them:

• You can use the Remove Duplicates feature and then undo the action (press Ctrl + Z) if you want to keep the original data intact. However, if you only need to filter for unique values temporarily, it’s better to use a filter or advanced filter as described earlier.

By using these methods, you can efficiently filter for unique values or remove duplicate values in Excel, helping to keep your data clean and organized.