When you have a larger dataset and you need to sort the dataset based on unique values, that is to say, you want to skip duplicate values. Getting such type of output was quite difficult in the earlier version of Excel but it has become an easy task if you are using Excel 365. In this article, I’ll discuss 10 useful methods to sort datasets based on unique values in Excel.

## Download Practice Workbook

## 10 Suitable Methods to Sort Unique List in Excel

This is our today’s dataset where **Employee Name** is provided with their unique **ID No****.**, **Salary** & **Rank****.**

Let’s drive into methods.

### 1. Finding Unique List Sorted

Here, I’ll show two separate ways to get sorted unique lists.

#### 1.1. Using Advanced Filter

**Advanced Filter** is really a sophisticated filtering tool instead of **AutoFilter** that screens data based on specified criteria.

So, let’s see the application of the **Advanced Filter** tool to sort the **Employee Name** field.

The procedure will be as follows-

⇰ Select the whole dataset.

⇰ Go to **Data **tab > Choose the **Advanced **option from the **Sort & Filter **ribbon

⇰ Fix the **List range** as **$C$5:$C$14****.**

⇰ In this case, we have no criteria. So keep the **Criteria range** blank.

⇰ Check the circle before the **Copy to another location **option, later you have to fix the location for getting the filtered data (e.g. **$F$5**).

⇰ As we want to get unique records, always check the box before the **Unique records only **option.

⇰ Finally, press **OK**.

So, the sorted unique list for the name is as follows.

#### 1.2. Combination of the SORT & UNIQUE Function

Although the** Advanced Filter** is an updated tool for filtering, Excel 365 (also Excel web and Excel 2021) introduces us to **UNIQUE** & **SORT** functions. The **UNIQUE **function returns a list of unique values from a given dataset or cell range whether the **SORT **function can sort the dataset in a specified order.

More importantly, we can get the sorted unique data for a specific field like **Employee Name** which is not possible while using the **Advanced Filter** as it extracts all fields of the source dataset. So, we can use the following formula for getting the sort unique list.

`=SORT(UNIQUE(C5:C14))`

Here, **C5:C14 **is the cell range for the name of the employee.

While explaining the above formula, we can say that the **UNIQUE(C5:C14) **syntax returns unique values and then the **SORT **function sorts the found unique values in ascending order.

The above picture depicts the sorted unique list of the name of the employee.

**Read More:** **How to Sort and Filter Data in Excel (A Complete Guideline)**

### 2. Sort Unique List on the Basis of a Value

Assuming that a value (e.g. a value of **Rank**) is given and we have to find the sorted unique data. Also, we’ll use the two methods.

#### 2.1. Using Advanced Filter

In the case of the **Advanced Filter**,

After opening the **Advanced Filter** dialog box, fix the **List range** as** $B4:$D14** and the **Criteria range** as **$F4:$F5**.

If you press **OK**, you’ll get the following output.

#### 2.2. Using Function

You may use the following formula when you want to get the sorted unique list on the basis of a given value.

`=SORT(UNIQUE(FILTER(C5:C14, F5=D5:D14)))`

Here, **C5:C14** is the cell range for the name of the employee, **F5 **is the given value, and **D5:D14** is the cell range for the **Rank **field.

In the **FILTER** function, I fixed **C5:C14** as an array, and **F5=D5:D14** which includes the specific value, thus the function will filter the dataset.

Then the **UNIQUE **function will return the unique value of the filtered data.

Finally, the **SORT **function will sort the found unique values in ascending order.

After entering the formula if you press **Enter**, you’ll find the following output.

**Read More:** **Sort Column by Value in Excel (5 Methods)**

### 3. Sort Unique List on the Basis of a Cell Range

Furthermore, if you want to get the sorted unique list based on a cell range, you also can find that using the two ways.

#### 3.1. Using Advanced Filter

After opening the **Advanced Filter** dialog box, fix the **List range** as** $B$4:$D$14 **and the **Criteria range** as **$F$4:$G$5**.

So, the output will be as follows.

**Read More:** **How to Sort ListBox with VBA in Excel (A Complete Guide)**

#### 3.2. Using Function

While using the functions, insert the following formula.

`=SORT(UNIQUE(FILTER(C5:C14,(D5:D14>=G5)*(D5:D14<=G6))))`

Here, **C5:C14** is the cell range for the name of the employee, **G5 **is the given first value, **G6 **is the second value, and** D5:D14** is the cell range for **Rank **field.

### 4. Sort Unique List Relying on a Single Criteria

Imagine, you want to sort with unique values if the salary is greater than or equal to $50000.

#### 4.1. Using Advanced Filter

In the case of the **Advanced Filter**,

After opening the following dialog box, specify the **List range** as **$B$4:$D$14** and the **Criteria range** as **$F$4:$F$5**.

So, the below picture shows the sorted unique list.

**Read More:** **How to Use Advanced Sorting Options in Excel**

#### 4.2. Using Function

While using the function, just enter the below formula

`=SORT(UNIQUE(FILTER(C5:C14, D5:D14>=F5)))`

Here, **C5:C14** is the cell range for the name of the employee, **F5 **is the given value, and **D5:D14** is the cell range for the **Rank **field.

### 5. Sort Unique List Relying on Multiple Criteria

Say, you have to sort the dataset for which salary is greater than or equal to $50000 and rank is equal to 2.

#### 5.1. Using Advanced Filter

In the case of the** Advanced Filter**,

After opening the dialog box, set the **List range** as **$B$4:$E$14** and the **Criteria range** as **$G$4:$H$5**.

If you do that, the following output will be found.

**Read More:** **How to Auto Sort Multiple Columns in Excel (3 Ways)**

#### 5.2. Using Function

While using the functions, just insert the following formula.

`=SORT(UNIQUE(FILTER(C5:C14,(D5:D14>=G5)*(E5:E14=H5))))`

Here, **C5:C14** is the cell range for the name of the employee, **G5 **is the for the required salary, **H5 **is the second value, **D5:D14** is the cell range for the **Salary **field, and **E5:E14 is **the cell range for the **Rank **field.

**Similar Readings:**

**Sum Using OFFSET and MATCH in Excel (With Alternative Options)****Excel Sort Dates in Chronological Order (6 Effective Ways)****How to Arrange Numbers in Ascending Order in Excel Using Formula****Sort Columns in Excel without Mixing Data (3 Ways)****How to Add Sort Button in Excel (7 Methods)**

### 6. Creating Dynamic Sorted Unique List

Sometimes, we need a dynamic list where the list will be updated automatically if any entry is inserted.

Interestingly, we may prepare such type of dynamic list for the sorted unique data.

For doing this, we have to create an Excel table.

⇰ Select the entire dataset.

⇰ Go to the **Insert **tab > click on **Table**.

⇰ Also, check the box before the **My table has headers**.

⇰ Now, the created table(Table1) is stored in Excel, and we can call the table anytime.

⇰ Then insert the following formula.

`=SORT(UNIQUE(Table1))`

Here, **Table1** is the created table for the source data.

⇰ If you press **Enter**, you will get the following dynamic list of sorted unique data.

Again, you can check the dynamic list whether it is working or not.

For example, insert new data (**ID: 1008**) in the last of the existing dataset, and you see the list is automatically updated.

### 7. Sort Unique List from A to Z (Alphabetically)

If you skip the ** Sort Order** argument in the

**SORT**function, it will automatically return from A to Z(ascending order).

So the formula will be-

`=SORT(UNIQUE(B5:D14))`

Here, **B5:D14** is the dataset.

**Read More:**** How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)**

### 8. Sort Unique List from Z to A

But if you need to sort the dataset from **Z to A** (descending order), you have to insert the value of **Sort Order **argument in the formula

So the adjusted formula will be-

`=SORT(UNIQUE(B5:D14), ,-1)`

Here, **B5:D14** is the dataset, and -1 is for the descending order.

**Read More:** **How to Sort Excel Tabs in Ascending or Descending Order (2 Ways)**

### 9. Sort Unique List Horizontally

When you need to sort the dataset horizontally that means in a row, surely based on unique values, you may utilize the below formula.

`=TRANSPOSE(SORT(UNIQUE(C5:C14)))`

Here,** C5:C14** is the name of the employee.

**Read More:** **How to Sort Drop Down List in Excel (5 Easy Methods)**

### 10. When Some Cells are Blank

In some cases, you may have blank cells in your dataset.

Obviously, we need to ignore the cells while getting the sort unique list in Excel.

So the formula will be as follows-

`=SORT(UNIQUE(FILTER(C4:C14,C4:C14<>"")))`

Here, **C4:C14** is the name of the employee, ” ” is for ignoring blank cells.

**Read More:** **Excel Sort and Ignore Blanks (4 Ways)**

## Things to Remember

Name of Errors | When Occurs |
---|---|

#CALC! |
If the UNIQUE function cannot extract the unique values. |

#SPILL! |
If there is any value in the spill range where the UNIQUE function will return the list. |

#VALUE! |
The SORT function occurs if the output (sorted unique values) is not available in the given dataset. |

## Conclusion

This is how you may get the sort unique list in Excel efficiently. Now, choose any method based on your preference. I hope this article will ease your Excel Journey.

Enjoy Excelling!