The SORT
function in Excel provides a straightforward way to organize and rearrange data according to your specific needs. This function allows you to quickly reorder data in ascending or descending order, based on one or more columns or rows.
This function is particularly useful when dealing with large datasets, as it simplifies data management by sorting it with defined parameters. Note that the SORT
function is available only in Excel 2021 and later versions.
Table of Contents
[Table of Contents]
Syntax
The SORT
function uses a specific syntax with four arguments to rearrange a given dataset according to specified criteria. The syntax is as follows:
=SORT(array,[sort_index],[sort_order],[by_col])
Arguments:
Details of each argument and their roles:
-
array
– This is a mandatory argument that specifies the range of cells or array you want to sort. The array value can be a static array, a cell reference, or the result returned by another function. -
sort_index
– This optional argument indicates the column number or row number to use as the basis for sorting. If you leave it empty, it defaults to 1, and theSORT
function sorts by the first column or row, depending on theby_col
argument. -
sort_order
– This is also an optional argument that determines the order in which the data is sorted. When left empty, the default value is 1, which rearranges the data in ascending order. When the value is set to -1, the data is sorted in descending order. -
by_col
– This is the final optional argument that accepts Boolean values (TRUE
orFALSE
). This argument determines whether to sort by columns or by rows. By default,by_col
is set toFALSE
, meaning sorting is done by rows. To rearrange the input data horizontally by columns, set theby_col
argument toTRUE
.
Important Characteristics
The most important feature of the SORT
function is its dynamic nature. Any changes to the input array are automatically reflected in the sorted output.
Here are some other important features of the SORT
function:
- The
SORT
function maintains the relationship between data in different columns or rows, ensuring that data remains correctly aligned after sorting. - If the
sort_index
argument is out of range, theSORT
function returns a#VALUE!
error. - The
sort_order
argument accepts only 1 or -1 as input values. If you enter any other value, the function returns a#VALUE!
error. - Sorting by row occurs only when the
by_col
argument is omitted or set toFALSE
.
Examples
The primary function of SORT
is to rearrange data based on columns or rows. Here are some practical examples using sample datasets to illustrate its functionality.
Combining SORT with INDEX Function
Suppose you want the top products based on quantity sold and want to email this report. You can use the SORT
function to reorder the data based on the values in the third column (column C) in descending order (-1). The SORT
function returns a sorted array.
Step 1: The formula to sort data based on quantity sold is:
=SORT(A2:D13,3,-1)
Step 2: Use the INDEX
function to retrieve the first 3 rows from the sorted dataset. Combine INDEX
and SORT
to achieve this.
=INDEX(SORT(A2:D13,3,-1), SEQUENCE(3), {2,3,4})
This extracts values from three rows (determined by SEQUENCE(3)
) and three columns (determined by {2, 3, 4}
) in the sorted range. You can also use this logic to extract a sorted value from a specific position.
Combining SORT with FILTER Function
In this scenario, imagine you have a large set of sales data that includes month-wise sales of a product, along with the sales quantity and revenue. Your goal is to sort the products based on their total revenue, while also having the flexibility to analyze different months.
Step 1: Use the FILTER
function to select data for a specific month. Assume that cell G1
is where the user can select the month.
=FILTER(A2:D13, A2:A13=G1)
This will filter the dataset based on the condition that the “Month” column (Column A) should be equal to the value in cell G1. It selects rows where the “Month” matches the value in G1 and returns the corresponding data from columns A to D.
Step 2: Now you can sort the data for the intended month based on the fourth column, which is “Total Revenue,” in descending order (-1). The complete formula will be:
=SORT(FILTER(A2:D13, A2:A13=G1), 4, -1)
Now you have the necessary month-wise data sorted in descending order based on revenue.
Dynamic Sorting
Suppose you have downloaded stock price data to analyze the daily prices of four stocks over a period. The dataset contains rows representing a different stock, with each entry showing the stock’s daily closing price.
Step 1: To reorder the dataset based on the Stock 2 prices in descending order, and sort by column, set the input value of by_col
to TRUE
. The basic formula would be:
=SORT(B2:F4,2,-1,TRUE)
Step 2: To ensure new data for future days are automatically included and sorted, convert the dataset into an Excel table. Select the dataset (A2:F4) and click on Format as Table
on the Home
tab. Let’s name the table stockprices
.
=SORT(stockprices,2,-1,TRUE)
Combining an Excel table with the SORT
function ensures that your table stays up to date as your dataset grows.
Multi-Level Sorting
Consider a scenario where you are managing a database of job applicants for a company’s recruitment process. The database contains information about applicants, including their names, years of experience, and the department they are applying for.
You want to sort the applicants first by their department in ascending order (alphabetical order), and then, within each department, sort them by years of experience in descending order (from most experienced to least experienced).
Step 1: To include more than one column in the sorting, use array constants for the input values of the sort_index
and sort_order
arguments.
=SORT(A2:C13, {3,2}, {1,-1})
The first value in the array is 3 because column C contains the department for which they are applying, which will be sorted first. The sorting order for column C is defined in the third argument’s array as 1, indicating ascending order. Next is column B (given as 2 in the first array) – the years of experience, which is to be sorted in descending order (indicated by -1 in the third argument).
Simple Use
Imagine you are a teacher at the end of the term. You have all the students’ grades in a dataset that includes their ID, names, and marks scored in three subjects. You now wish to do basic analysis such as finding out the top-performing student or the lowest marks secured in a subject.
Step 1: To rearrange the dataset by marks scored in Maths with the highest marks at the top, use the following formula.
=SORT(A1:E11,3,-1,FALSE)
This formula arranges the data in the array A1:E11
based on the marks obtained in Maths (column 3) in descending order.
Step 2: To find the lowest marks scored in Science, change the sort_order
argument to 1 to indicate ascending order. The formula used will be:
=SORT(A2:E11,4,1,FALSE)
As we wish to sort the dataset based on science marks (Column D).
The SORT
function in Excel is a powerful tool for data management, and you can improve your data manipulation skills by using it regularly.