Excel: Combining Unique and Sort functions

Posted on:  04/14/2021
Excel: Combining Unique and Sort functions

The UNIQUE function in Excel returns a list of unique values in a list or range. The SORT function in Excel sorts the contents of a range or array. The functions are great individually, but I frequently combine them. In the video below, I show Unique and Sort in seven different examples.

Who can use Unique and Sort functions?

The Unique and Sort function work with 

  • Excel for Microsoft 365
  • Excel for Microsoft 365 for Mac
  • Excel for the web
  • Excel for iPad
  • Excel for iPhone
  • Excel for Android tablets
  • Excel for Android phones
Unique Function in Excel with text

Unique Function in Excel with text

Unique Function in Excel with numbers

Unique Function in Excel with numbers

Sort function in Excel

Sort Function in Excel

Sort function in Excel in descending order

Sort function in Excel with descending order

UNIQUE function Syntax and Arguments

The Unique function has three arguments. Two are optional. 

Syntax

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

Arguments

  • array - Range or array from which to extract unique values.
  • by_col - [optional] How to compare and extract. By row = FALSE (default); by column = TRUE.
  • exactly_once - [optional] TRUE = values that occur once, FALSE= all unique values (default).

Unique vs. Distinct

The function is called the Unique function, but technically it is finding Distinct values. What is the difference between Distinct vs. Unique?

Unique values are the items that appear only once. Distinct values are all different items in a list. So something Unique is Distinct, but something Distinct may not be Unique. See my example below.

I have a list of names in A2 through A8

  • In B2 is the function =UNIQUE(A2:A8) which shows Distinct values. 
  • In C2 is the function =UNIQUE(A2:A8,,TRUE), which shows UNIQUE values. Mary is Unique since she in the dataset only one time. The third argument in the Unique function is set to TRUE, which is why only Unique and not Distinct show.
Unique vs. Distinct in Excel

Unique vs. Distinct

YouTube video

SORT function Syntax and Arguments

The SORT function has four arguments. Three are optional.

Syntax

=SORT (array, [sort_index], [sort_order], [by_col])

Arguments

  • array - Range or array to sort.
  • sort_index - [optional] Column index to use for sorting. Default is 1.
  • sort_order - [optional] 1 = Ascending, -1 = Descending. Default is ascending order.
  • by_col - [optional] TRUE = sort by column. FALSE = sort by row. Default is FALSE.
Sort with the second argument 2

Sort function with the second argument 2. Sorting by Salary, the second column.

Excel file to download

Here is the file used in the video.

FAQ

Why not just use the sort feature, Data - Sort - instead of the SORT function?

With the SORT function, your data is always sorted in a the order you want. If you use the Data - Sort and change your data, you will need to sort your data again.With the SORT function the data is alway in the correct order.

Can't I find unique values with Remove Duplicates?

Remove duplicates is great, but if you change your data, remove duplicates does not change. With the Unique function, when your data changes, the Unique function shows the updated results.

Other Excel articles

Chris Menard

Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories