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.

**Download Excel Workbook**

**8 Easy Ways to Filter Unique Values in Excel**

**Method 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** **duplicates values found and removed;** **7 unique values remain**.

Click **OK**.

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

**Method 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 duplicates 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.

`=COUNTIF($D$5:D5,D5)=1`

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

`=COUNTIF($D$5:D5,D5)>1`

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 **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 select **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**

**Method 3: Using Data Tab Advanced Filter Feature to Filter Unique Values**

The earlier methods delete or remove entries from the dataset to filter unique. 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 **Sort & Filter** section).

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

Select **Copy to another location** action under **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.

**Method 4: Filter 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**).

`=UNIQUE(D5:D19)`

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

**Similar Readings**

**Excel Filter Data Based on Cell Value (6 Efficient Ways)****How to Add Filter in Excel (4 Methods)****Shortcut for Excel Filter (3 Quick Uses with Examples)****How to Use Text Filter in Excel (5 Examples)**

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

`=UNIQUE(FILTER(D5:D19,C5:C19=E4))`

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:** **Filter Multiple Criteria in Excel**

**Method 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 unique.

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

`=IFERROR(INDEX($D$5:$D$19, MATCH(0, COUNTIF($G$4:G4, $D$5:$D$19), 0)),"")`

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

`=IFERROR(INDEX($E$5:$E$19, MATCH(0,IF(ISBLANK($E$5:$E$19),1,COUNTIF($H$4:H4, $E$5:$E$19)), 0)),"")`

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.

**Step 1:** Apply the below formula in cell **I5**.

`=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))`

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.

**Method 7: Excel Filter Unique Values Using VBA Macro Code**

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

**Method 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 **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.

**Read more:** **How to Filter Excel Pivot Table**

**Conclusion**

Filter unique is a common operation to perform in Excel. In this article, we use various features, 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.