# How to Sort Unique List in Excel (10 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

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

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

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

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

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. #### 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. ### 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. Read More: How to Create Custom Sort List in Excel

### 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. ### 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. ### 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 Dates in Chronological Order in Excel (6 Easy Ways)

### 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!

## Related Articles Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  