Excel SORT function

Summary

The Excel SORT function is used to sort the contents of a range or array either in ascending or descending order. It offers the flexibility to sort by multiple columns. SORT returns a dynamic array of results, which automatically updates if values in the source data change. This function is ideal for organizing data for analysis or presentation in a structured and orderly manner.
Syntax
				
					=SORT(array, [sort_index], [sort_order], [by_col])
				
			
  • array: The range or array to sort.
  • [sort_index]: [Optional] The column index by which to sort. Default is 1 (the first column).
  • [sort_order]: [Optional] Sort order: 1 for ascending (default), -1 for descending.
  • [by_col]: [Optional] TRUE to sort by column, FALSE (default) to sort by row.
Return value
A sorted array based on the specified criteria.

How to use

Use SORT by specifying the array or range you want to sort. Optionally, define which column to sort by (sort_index), the order of the sort (sort_order), and whether to sort by column or row (by_col).

Examples

Simple SORT
Sorting a Range in Ascending Order: To sort a range by the first column in ascending order:
				
					=SORT(A2:B10)
				
			
This formula sorts the range A2:B10 based on the values in the first column (column A) in ascending order.
SORT in Descending Order
Descending Order Sort by Specific Column: To sort a range by a specific column in descending order:
				
					=SORT(A2:B10, 2, -1)
				
			
This sorts the range A2:B10 based on the values in the second column (column B) in descending order.
Horizontal SORT
Sorting Horizontally by a Specific Row: To sort a range horizontally based on values in a specific row:
				
					=SORT(A1:E3, 1, 1, TRUE)
				
			
This formula sorts the range A1:E3 horizontally (by columns) based on the values in the first row.
SORT with Custom Sort Order
Custom Sort Order with Multiple Columns: To sort a range first by one column in ascending order and then by another column in descending order:
				
					=SORT(A2:C10, {1, 2}, {1, -1})
				
			
This sorts the range A2:C10 first by column A in ascending order, then by column B in descending order.
SORT for Data Reorganization
Reorganizing Data with SORT: To sort data alphabetically and then by numerical value:
				
					=SORT(A2:B10, {1, 2}, {1, 1})
				
			
This formula sorts the range A2:B10 first alphabetically based on column A and then numerically based on column B.

Additional Notes

  • SORT is highly effective for quickly organizing data sets.
  • The function is dynamic, meaning changes in the source data or sort criteria will automatically update the results.
  • If the specified sort_index is out of range, SORT returns a #VALUE! error.
  • SORT is a simpler alternative to SORTBY when sorting needs are straightforward and based on existing data.

Related Functions

Excel FILTER function

The Excel FILTER function dynamically isolates data in an array based on criteria, essential for targeted analysis and reporting.

Excel SORTBY function

The Excel SORTBY function sorts a range or array based on the values in one or more corresponding arrays, allowing for multi-criteria sorting.

Excel UNIQUE function

The Excel UNIQUE function extracts unique values from an array or range, simplifying data cleaning and consolidation efforts.

Excel RANDARRAY function

The Excel RANDARRAY function creates an array of random numbers, suited for advanced simulations and data analysis tasks.

Excel SEQUENCE function

The Excel SEQUENCE function generates a sequence of numbers, ideal for creating serial numbers and automated lists.

Content Navigation