SORT Function in Google Sheets

The SORT function lets you sort a range (or array) of data. The function creates a new range of data with the new, sorted output from the function. SORT is often used with or instead of the FILTER function when widdling down large amounts of data. FILTER is for reducing data and SORT is for, well, sorting it.

The SORT function is typically accessed from the user menu, but it can also be typed into a cell just like any other function. Excel, the most popular spreadsheet program, only has it as a menu function so learning to use SORT as a function can be a new experience even to long-time spreadsheet users.

Video Explanation

Purpose

Returns sorted data from your specified source.

Syntax

=SORT(range,sort_column,is_ascending,[sort_column2, is_acending2,...])

  • range – The entire range of data that is to be sorted
  • sort_column – The column of data that contains the criteria that you are sorting. Can only be one column at a time.
  • is_ascending – Two possible answers – TRUE for ascending (1,2,3), FALSE for descending (3,2,1)

Tip: When using SORT, start your formula in a cell that should be the upper left most cell for the output. This function writes the output in, below, and to the right of your starting point. The formula text will remain in the upper left cell and can be changed.

Examples

Example 1

Using a simple SORT function with one column of data to be sorted in ascending order. The first rows are your raw data, then the rows below the SORT formula are the output.

Shows the starting data and the formula typed into the last cell.
A
1 2
2 5
3 3
4 =SORT(A1:A3,1,TRUE)
5
6
The first three rows are the original data and the last three rows are the dynamic results of the function. The function is still in cell A4 and you can see it if you select that cell.
A
1 2
2 5
3 3
4 2
5 3
6 5

Example 2

Let’s look at an example now of sorting a table with two columns of data and sorting in descending order with the second column as the value to sort by.

A B
1 2 b
2 5 t
3 3 r
4 =SORT(A1:B3,2,FALSE)
5
6
The first three rows are the original data and the last three rows are the dynamic results of the function. The function is still in cell A4 and you can see it if you select that cell.
A B
1 2 b
2 5 t
3 3 r
4 5 t
5 3 r
6 2 b

Example 3

Two column SORT, sorted by only column 2 in ascending order.

Shows the starting data and the formula typed into row 7.
A B
1 Huey Duck
2 Dewey Duck
3 Louie Duck
4 Duckworth Butler
5 Bubba Caveduck
6 Tootsie Triceratops
7 =SORT(A1:B6,2,TRUE)
8
9
10
11
12
The first six rows are the original data and the last six rows are the dynamic results of the function. The function is still in cell A7 and you can see it if you select that cell.
A B
1 Huey Duck
2 Dewey Duck
3 Louie Duck
4 Duckworth Butler
5 Bubba Caveduck
6 Tootsie Triceratops
7 Duckworth Butler
8 Bubba Caveduck
9 Huey Duck
10 Dewey Duck
11 Louie Duck
12 Tootsie Triceratops

Example 4

Two column SORT, sorted by column 2 in ascending order then by column 1 in ascending order. Now Dewey comes before Louie.

Shows the starting data and the formula typed into row 7.
A B
1 Huey Duck
2 Dewey Duck
3 Louie Duck
4 Duckworth Butler
5 Bubba Caveduck
6 Tootsie Triceratops
7 =SORT(A1:B6,2,TRUE,1,TRUE)
8
9
10
11
12
The first six rows are the original data and the last six rows are the dynamic results of the function. The function is still in cell A7 and you can see it if you select that cell.
A B
1 Huey Duck
2 Dewey Duck
3 Louie Duck
4 Duckworth Butler
5 Bubba Caveduck
6 Tootsie Triceratops
7 Duckworth Butler
8 Bubba Caveduck
9 Dewey Duck
10 Huey Duck
11 Louie Duck
12 Tootsie Triceratops

 Live examples in Sheets

Go to this spreadsheeet for several examples of the SORT function that you can study and use anywhere you would like.

SORTN Function in Google Sheets

The SORTN function lets you sort a range of data and return the first n items from that range. The function creates a new range of data with the new, sorted output from the function. SORTN can significantly reduce the number of steps that it takes to get the data that you need out of a table.

SORTN is similar to FILTER, which filters a specified range, but SORTN returns the highest n amounts while FILTER returns the matching amounts.

Excel, the most popular spreadsheet program, does not have this function.

Purpose

Returns n number of rows from a range of data.

Video explanation

Syntax

=SORTN(range,[n],[display_ties_mode],[sort_column1, is_acending1], ...)

  • range – The entire range of data that is to be sorted
  • n – The number of items to return
  • display_ties_mode [OPTIONAL] – An optional number designating the way to display ties.
    • 0: Show at most the first n rows in the sorted range.
    • 1: Show at most the first n rows, plus any additional rows that are identical to the nth row.
    • 2: Show at most the first n rows after removing duplicate rows.
    • 3: Show at most the first n unique rows, but show every duplicate of these rows.

    sort_column1 [OPTIONAL] – The column of data that contains the criteria that you are sorting. Can only be one column at a time. If this value is not specified, the lowest index column in the range will be used and subsequent columns will be used if a tie-breaker is needed.

  • is_ascending1 [OPTIONAL] – Two possible answers – TRUE for ascending (1,2,3), FALSE for descending (3,2,1)
  • sort_column2 - [OPTIONAL] – Add as many of these as you want. On and on….

Tip: When using SORTN, start your formula in a cell that should be the upper left most cell for the output. This function writes the output in, below, and to the right of your starting point. The formula text will remain in the upper left cell and can be changed.

Tip: If sort_column1 and is_ascending1 aren’t included, the sort is performed on the lowest-index column in range, with subsequent columns used to sort if there are ties.

Examples

Example 1

Using a simple SORTN function with one column of data and extracting the two highest values. The display_ties is set to 0 which means show two highest values regardless of ties. The zero isn’t specified, but it is the default. This is the same as typing “0”.

Shows the starting data and the formula typed into the last cell.
A
1 2
2 5
3 3
4 3
5 =SORTN(A1:A4,2)
6
The first three rows are the original data and the last two rows are the dynamic results of the function. The function is still in cell A5 and you can see it if you select that cell.
A
1 2
2 5
3 3
4 3
5 2
6 3

Example 2

The display_ties parameter has been changed to “1” in this example which tells Sheets to show the two highest values including any ties. The two first values are both 2, and it has included all three occurrences of 2.

Shows the starting data and the formula typed into the last cell.
A
1 2
2 2
3 2
4 5
5 3
6 3
7 =SORTN(A1:A6,2,1)
8
9
The first six rows are the original data and the last three rows are the dynamic results of the function. The function is still in cell A5 and you can see it if you select that cell.
A
1 2
2 2
3 2
4 5
5 3
6 3
7 2
8 2
9 2

Example 3

The display_ties parameter is set to 2 which means show at most only 2 rows but don’t show any duplicates

Shows the starting data and the formula typed into the last cell.
A
1 2
2 2
3 2
4 5
5 3
6 3
7 =SORTN(A1:A6,2,2)
8
The first six rows are the original data and the last two rows are the dynamic results of the function. The function is still in cell A7 and you can see it if you select that cell.
A
1 2
2 2
3 2
4 5
5 3
6 3
7 2
8 3

Example 4

The display_ties parameter is set to 3 which means show at most only 2 items but show all duplicates of these items. 2 and 3 are the two highest items so it is showing all 2s and 3s no matter how many there are. Note: It seems to me that this function should also be showing all of the threes, but it is not. I submitted this as a question on the Google Docs forum and will update this notes with what I find out.

Alert! This function, as of early September 2017, is not working properly. If you look at the shared Sheet, starting at row 33, the function is not returning all of the 3’s that it should. The bug has been reported to Google here.
Shows the starting data and the formula typed into the last cell.
A
1 2
2 2
3 2
4 5
5 3
6 3
7 =SORTN(A1:A6,2,3)
8
9
10
11
The first six rows are the original data and the last four rows are the dynamic results of the function. The function is still in cell A7 and you can see it if you select that cell.
A
1 2
2 2
3 2
4 5
5 3
6 3
7 2
8 2
9 2
10 3
11 3

Example 5

Two column SORTN, only sorted one column.

Shows the starting data and the formula typed into row 5.
A B
1 Hen Brown
2 Sheep White
3 Bull brown
4 Goat White
5 =SORTN(A1:A4,2,1)
6
Because I specified only the first column of my range, the output of the formula was only the first column.
A B
1 Hen brown
2 Sheep White
3 Bull brown
4 Goat white
5 Bull
6 Goat

Example 6

Two column SORTN, specifying both columns in the range.

Shows the starting data and the formula typed into row 5. Notice that the range now is both column A and column B.
A B
1 Hen Brown
2 Sheep White
3 Bull brown
4 Goat White
5 =SORTN(A1:B4,2,1)
6
Because I specified both columns of this range, the output of the formula is both columns.
A B
1 Hen brown
2 Sheep White
3 Bull brown
4 Goat white
5 Bull brown
6 Goat white

 Live examples in Sheets

Go to this spreadsheeet for several examples of the SORTN function that you can study and use anywhere you would like.

FILTER Function in Google Sheets

The FILTER function allows you to narrow down a range of data to include only items that meet the criteria that you specify. The function creates a new range of data with the new, smaller output from the function. FILTER is often used with or instead of the SORT function when whittling down large amounts of data. FILTER is for reducing data and SORT is for, well, sorting it.

FILTER Options

The filter function is typically accessed from the user menu, but it can also be typed into a cell just like any other function. Excel, the most popular spreadsheet program, only has it as a menu function.

Video Explanation

Purpose

Filter button on the desktop toolbar
Filter button on the desktop toolbar

Returns data from your specified source with only the criteria that you selected. Yes, it gets rid of what you don’t want.

Syntax

=FILTER(range,condition1,[condition2...])

Basic information about the inputs:

  • range – The entire range of data that is to be filtered
  • condition1 – The criteria upon which the range is to be filtered
  • condition2... – The optional additional criteria upon which the range is to be filtered

Examples

Tip: When using FILTER, start your formula in a cell that should be the upper left most cell for the output. This function writes the output in, below, and to the right of your starting point. The formula text will remain in the upper left cell and it can be changed.

Example 1

Using the FILTER function with one column of data. The first table is before the function is applied and the second is after.

Shows the starting data and the formula typed into the fifth row.
A
1 2
2 5
3 3
4 3
5 =FILTER(a1:a4,a1:a4<4)
7
8
The first four rows are the original data and the last three rows are the dynamic results of the function. The function is still in cell A5 and you can see it if you select that cell.
A
1 2
2 5
3 3
4 3
5 2
7 3
8 3

Example 2

Let’s look at an example now of filtering a table with two columns of data using one condition.

Shows the starting data and the formula typed into the fifth row.
A B
1 2 c
2 5 c
3 3 hh
4 3 c
5 =FILTER(a1:b4,b1:b4="c")
6
7
The first four rows are the original data and the last three rows are the dynamic results of the function. The function is still in cell A5 and you can see it if you select that cell.
A B
1 2 c
2 5 c
3 3 hh
4 3 c
5 2 c
6 5 c
7 3 c

Example 3

Three columns of data filtered by two conditions.

Shows the starting data and the formula typed into the fifth row.
A B C
1 2 c 3
2 5 c 2
3 3 hh 3
4 3 c 3
5 =FILTER(a1:c4,b1:b4="c",c1:c4=3)
6
The first four rows are the original data and the last two rows are the dynamic results of the function. The function is still in cell A5 and you can see it if you select that cell.
A B C
1 2 c 3
2 5 c 2
3 3 hh 3
4 3 c 3
5 2 c 3
6 3 c 3

 

 Live examples in Sheets

Go to this spreadsheeet for several examples of the FILTER function that you can study and use anywhere you would like.

UNIQUE Function – Google Sheets

The UNIQUE function is a quick way to remove duplicate lines from a table of data in a spreadsheet. The function works from the top of your data down, discarding every row that is a repeat while keeping the first instance of each row in the order in which it originally occurred. When finished with the function, you end up with a new list of data with the duplicates removed.

Another way of removing duplicates is to create a pivot table. However, using the UNIQUE function is a faster and simpler method of removing duplicates and you aren’t left with the clutter of a pivot table. However, it less versatile as a pivot table has hundreds of other uses in addition to just removing duplicates. The UNIQUE function is a one-and-done function that does one thing but does it well.

When using UNIQUE, start your formula in a cell that will be the upper left most cell for the output. This function writes the output in, below, and to the right of your starting point. The formula text will remain in the cell where you typed it. If you place your cursor in that cell, the formula can be changed.

Video Explanation

Purpose

Removes duplicate rows in a specified range.

Syntax

=UNIQUE(range)

  • range – The rows that are to be included in the function

Examples

Tip: The contents of every cell in a row have to match all of the contents in another row in order for the row to be considered a duplicate.

Example 1

A simple one-column list with two duplicates.

Shows the starting data and the formula typed into the last cell.
A
1 Albert
2 Jed
3 Martha
4 June Bug
5 Flipper
6 Jed
7 Albert
8 =UNIQUE(A1:A7,a1:a4<4)
9
10
11
12
The first seven rows are the original data and the last five rows are the dynamic results of the function. The function is still in cell A8 and you can see it if you select that cell.
A
1 Albert
2 Jed
3 Martha
4 June Bug
5 Flipper
6 Jed
7 Albert
8 Albert
9 Jed
10 Martha
11 June Bug
12 Flipper

Example 2

Two-column list with two duplicates in first column, one pair of duplicates in the second column. Although Jed is a duplicate, Schmed is not. Both columns need to be duplicates.

Shows the starting data and the formula typed into the last cell
A B
1 Albert Schmalbert
2 Jed Lead
3 Martha Schmartha
4 June Bug Schune Bug
5 Flipper Skipper
6 Jed Schmed
7 Albert Schmalbert
8 =UNIQUE(a1:b7)
9
10
11
12
13
14
The first seven rows are the original data and the last six rows are the dynamic results of the function. The function is still in cell A8 and you can see it if you select that cell.
A B
1 Albert Schmalbert
2 Jed Lead
3 Martha Schmartha
4 June Bug Schune Bug
5 Flipper Skipper
6 Jed Schmed
7 Albert Schmalbert
8 Albert Schmalbert
9 Jed Lead
10 Martha Schmartha
11 June Bug Schune Bug
12 Flipper Skipper
13 Jed Schmed

 Live examples in Sheets

Go to this spreadsheeet for several examples of the UNIQUE function that you can study and use anywhere you would like.

Filter Functions in Google Sheets – FILTER, SORT, SORTN and UNIQUE

Sheets offers functions for sorting and filtering data that are typically only found in the menu options of other spreadsheet programs, or not found at all in the cases of SORTN and UNIQUE. On the desktop version of Sheets, the SORT and FILTER functions are available through the menus as shown in the pictures below, while the tablet version has a ‘Create a filter’ option in the more (three circles in a vertical line) menu on the right hand side of the screen.

Filter button on the desktop toolbar

Filter button on the desktop toolbar

SORT options in the desktop menus

Sort options in the desktop menus

FILTER option in the tablet app menu

Filter option on tablet app

The UNIQUE function, which does not exist as a function in Excel, removes duplicate rows of data in a spreadsheet. Another Sheets-only function, SORTN, returns n number of items from a range of data.

All filter functions

The ability to use these as functions is what sets Sheets apart from the competition.

Sort Options

SORT function

FILTER Options

FILTER function

Screen capture of the unique function

UNIQUE function

Screen capture of the SORTN function

SORTN function

Videos for each filter function