How to Filter Unique Values in Excel (8 Easy Ways)

Filter Unique is an effective way to get around with numerous entries in a dataset. Excel offers multiple features to filter unique data or remove duplicates, no matter what we call it. In this article, we’ll demonstrate the ways to filter unique data from a sample dataset.

Let’s say we have three simple columns in an Excel dataset containing Order Date, Category, and Product. We want the unique ordered products within the entire dataset.

1. Using Excel Remove Duplicates Feature to Filter Unique Values

To fathom entries in a huge dataset, we sometimes need to remove duplicates. Excel offers the Remove Duplicates feature in the Data tab to omit duplicate entries from datasets. In this case, we want to remove duplicates from the Category and Product column. As a result, we can use the Remove Duplicates feature to do so.

Step 1: Select the range (i.e., Category and Product) then Go to Data Tab > Select Remove Duplicates (from the Data Tools section).

Step 2: The Remove Duplicates window appears. In the Remove Duplicates window,

Checked all the columns.

Tick the option My data has headers.

Click OK.

Step 3: A confirmation dialog box appears saying 8 duplicate values found and removed; 7 unique values remain.

Click OK.

All the steps lead to the following consequences as shown in the below image.

2. Using Conditional Formatting to Filter Unique Values

Another way to filter the unique is to Conditional Formatting. Excel Conditional Formatting can format cells with numerous criteria. However, in this case, we use a formula to conditionally format cells in a range (i.e., Product column). We have two options to apply Conditional Formatting; one is the conditional formatting to filter unique values and the other one is to hide duplicate values from the range.

2.1. Conditional Formatting to Filter Unique Values

In this case, we use a formula in Conditional Formatting options to Excel filter unique entries.

Step 1: Select the range (i.e., Product 1) then Go to Home Tab > Select Conditional Formatting (from Styles section) > Select New Rule.

Step 2: The New Formatting Rule window pops up. In the New Formatting Rule Window,

Select Use a formula to determine which cells to format under Select a Rule Type option.

Type the following formula under the Edit the Rule Description option.

In the formula, we directed Excel to count each cell in the D column as Unique (i.e., equal to 1). If the entries match with the imposed condition it returns TRUE and Color Format the cells.

Click on Format.

Step 3: In a moment, the Format Cells window appears. In the Format Cells window,

In the Font section- Select any formatting color as depicted in the below image.

Then Click OK.

Step 4: Clicking OK in the previous step takes you to the New Formatting Rule window again. In the New Formatting Rule window, you can see the preview of unique entries.

Click OK.

In the end, you get the unique entries color formatted as you want them similar to the picture below.

2.2. Conditional Formatting to Hide Duplicates

Without meddling with the unique values, we can simply hide the duplicate values using Conditional Formatting. To hide the duplicates, we have to apply the same formula as we did to filter out the unique except assigning them to values greater than 1. After selecting the White Font color, we can hide them from the rest of the entries.

Step 1: Repeat Steps 1 to 2 of method 2.1 but Change the inserted formula with the below one.

The formula directs Excel to count each cell in the D column as Duplicates (i.e., greater than 1). If the entries match with the imposed condition it returns TRUE and Color Format (i.e., Hide) the cells.

Click on Format.

Step 2: Clicking on Format takes you to the Format Cells window. In the Format Cells window,

Select the Font color White.

Then Click OK.

Step 3: After selecting the Font color, Clicking OK hovers you to the New Formatting Rule window again. You can see the preview as bleak because we selected White as the Font color.

Click OK.

Following all the steps lead you to a depiction similar to the image below for duplicate values.

You have to select White as a Font color otherwise duplicate entries won’t hide.

Read more: How to Filter Data in Excel using Formula

3. Using the Data Tab Advanced Filter Feature to Filter Unique Values

The earlier methods delete or remove entries from the dataset to filter uniqueness. It’s quite dangerous while we work on certain datasets. There may be situations where we can’t alter the raw datasets, in those cases we can use the Advanced Filter option to filter unique in the desired position.

Step 1: Select the range (i.e., Product column). Then Go to Data Tab > Select Advanced (from the Sort & Filter section).

Step 2: The Advanced Filter window appears. In the Advanced Filter window,

Select Copy to another location action under the Action option. You can choose either Filter the list, in-place, or Copy to another location however, we are choosing the latter one for not altering the raw data.

Assign a location (i.e., F4) in the Copy to option.

Checked the Unique records-only option.

Click OK.

Clicking OK gets you the unique values in the destined location as directed in the steps.

4. Filtering Unique Values Using Excel UNIQUE Function

Displaying unique values in another column can also be achieved by the UNIQUE function. The UNIQUE function fetches a list of unique entries from a range or array. The syntax of the UNIQUE function is

UNIQUE (array, [by_col], [exactly_once])

The arguments,

array; range, or array from where the unique values get extracted from.

[by_col]; ways to compare and extract values, by row = FALSE (default) and by column = TRUE. [optional]

[exactly_once]; once occurring values = TRUE and existing unique values = FALSE (by default). [optional]

Step 1: Type the following formula in any blank cell (i.e., E5).

Step 2: Press ENTER then in a second all the unique entries pop up in the column similar to the picture below.

The UNIQUE function spills all the unique entries at a time. However, you can’t use the UNIQUE function other than Excel 365 version.

Read More: Excel Filter Data Based on Cell Value

5. Using UNIQUE and FILTER Functions (with Criteria)

In method 4, we use the UNIQUE function to spill out the unique values. What if we want unique entries depending on a condition? Let’s say we want unique Product names of a certain Category from our dataset.

In this case, we want the unique Product names of the Bars (i.e., E4) category from our dataset.

Step 1: Write the below formula in any cell (i.e., E5).

The formula instructs to filter the D5:D19 range, imposing a condition on range C5:C19 to be equal to the cell E4.

Step 2: Hit ENTER. After that products under the Bars category, appear in the cells of the Bars column as shown in the following screenshot.

You can choose any Category to filter unique products from. It’s quite an effective way to handle huge sales datasets. The FILTER function is only available in Excel 365.

Read more: How to Filter with Multiple Criteria in Excel

6. Using MATCH and INDEX Functions (Array Formula)

For simpler demonstration, we use a dataset with no blanks or case-sensitive entries. So, how can we handle such a dataset that has blanks and case-sensitive entries? Before demonstrating a way out, let’s filter the non-blank range (i.e., Product 1) using a combined formula. In this case, we use the MATCH and INDEX functions to filter uniqueness.

6.1. MATCH and INDEX Functions Filter Unique Values from a Non-Blank Range

We can see there are no existing blank cells in the Product 1 range.

Step 1: Type the following formula in cell G5 to filter out the unique.

By the formula,

First, COUNTIF($G$4:G4, $D$5:$D$19); counts the number of cells in the range (i.e., $G$4:G4) obeying the condition (i.e., $D$5:$D$19). COUNTIF returns 1 if it finds $G$4:G4 in the range otherwise 0.

Second, MATCH(0, COUNTIF($G$4:G4, $D$5:$D$19), 0)); returns the relative position of a product in the range.

At last, INDEX($D$5:$D$19, MATCH(0, COUNTIF($G$4:G4, $D$5:$D$19), 0)); returns the cell entries that meet the condition.

The IFERROR function restricts the formula from displaying any errors in outcomes.

Step 2: As the formula is an array formula, Press CTRL+SHIFT+ENTER altogether. All the unique entries from the Product 1 range appear.

6.2. MATCH and INDEX Functions to Filter Unique Values from Existing Blank Cells in a Range

Now, in the Product 2 range, we can see multiple blank cells exist. To filter out the unique among the blank cells, we have to insert the ISBLANK function.

Step 1: Paste the below formula in cell H5.

This formula works in the same way as we described it in 6.1. section. However, the extra IF function with the logical test of the ISBLANK function enables the formula to ignore any blank cells in the range.

Step 2: Hit CTRL+SHIFT+ENTER and the formula ignores the blank cells and fetches all the unique entries as depicted in the following picture.

6.3. MATCH and INDEX Functions to Filter Unique Values from a Case-Sensitive Range

If our dataset has case-sensitive entries, we have to use the FREQUENCY function along with the TRANSPOSE and ROW functions to filter out the unique ones.

Step 1: Apply the below formula in cell I5.

Sections of the formula,

• TRANSPOSE($I$4:I4); transpose previous values by converting semicolon into comma. (i.e., TRANSPOSE({“unique values (case sensitive)”;Whole Wheat”}) becomes {“unique values (case sensitive)”,”Whole Wheat”}
• EXACT($F$5:$F$19, TRANSPOSE($I$4:I4); checks whether strings are the same and case-sensitive or not.
• IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)); returns the relative position of a string in the array if TRUE.
• FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), “”); calculates how many times a string is present in the array.
• MATCH(0, FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), “”), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19))), 0)); finds first False (i.e., Empty) values in the array.
• INDEX($F$5:$F$19, MATCH(0, FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), “”), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19))), 0)); returns unique values from the array.

Step 2: You need to press CTRL+SHIFT+ENTER altogether and the case-sensitive unique values appear in the cells.

 So, the whole dataset looks like the below image after sorting all types of entries in their respective columns.

You can change any of the Product data types to fulfill your demand and apply formulas according to that.

7. Using VBA Macro Code to Filter Unique Values

From the dataset, we know we have a Product column, and we want the unique values from the column. To achieve the job, we can use VBA Macro code. We can write a code that assigns values from selection and then sends it through loops unless it gets rid of all duplicates.

Before we apply the VBA Macro code, let’s ensure that we have a dataset of the following type and select the range from where we want to filter the unique.

Step 1: In order to write a macro code, press ALT+F11 to open up Microsoft Visual Basic window. In the window, Go to the Insert tab (in the Toolbar) > Select Module.

 Step 2: The Module window appears. In the Module, Paste the following code.

Sub Unique_Values()
Dim Range As Variant, prdct As Variant
Dim mrf As Object
Dim i As Long
Set mrf = CreateObject("scripting.dictionary")
Range = Selection
For i = 1 To UBound(Range)
mrf(Range(i, 1) & "") = ""
Next
prdct = mrf.keys
Selection.ClearContents
Selection(1, 1).Resize(mrf.Count, 1) = Application.Transpose(prdct)
End Sub

In the Macro code,

After declaring variables, mrf = CreateObject(“scripting.dictionary”) creates an object that is assigned to mrf.

Selection assigned to the Range. The For Loop takes each cell and then matches with the Range for duplicates. After that, the code clears the Selection and appears with the unique.

Step 3: Hit F5 to run the macro then by returning to the worksheet, you see all the unique values from the selection.

8. Using Pivot Table to Filter Unique Values

Pivot Table is a strong tool to export a unique items list from selected cells. In Excel, we can easily insert a Pivot Table and achieve what we desire here.

Step 1: Select a certain range (i.e., Product). Afterward, Go to Insert Tab > Select Pivot Table (from the Tables section).

Step 2: The PivotTable from a table or range window appears. In the window,

The range (i.e., D4:D19) will be automatically selected.

Choose Existing Worksheets as where you want the PivotTable to be placed option.

Click OK.

Step 3: The PivotTable Fields window appears. In the PivotTable Fields window, there is only one field (i.e., Product).

Checked the Product field to make the unique product list appear as shown in the picture below.

Download Excel Workbook

Conclusion

Filter unique is a common operation to perform in Excel. In this article, we use various features, and functions such as UNIQUE, FILTER, MATCH, INDEX as well as VBA Macro code to filter out the unique values. Functions keep the raw data intact and display the resultant values in another column or destination. However, features alter raw data by removing the entries from the dataset permanently. I hope this article gives you a lucid concept of dealing with duplicates in your datasets and extracting unique values. Comment, if you have further queries or have something to add. See you in my next article.

Further Readings

• How to Perform Custom Filter in Excel
• How to Convert Text Filter to Date Filter in Excel
• How to Filter Horizontal Data in Excel
• How to Use Text Filter in Excel