How to Use Excel's SORT Function

Microsoft
1

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 the SORT function sorts by the first column or row, depending on the by_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 or FALSE). This argument determines whether to sort by columns or by rows. By default, by_col is set to FALSE, meaning sorting is done by rows. To rearrange the input data horizontally by columns, set the by_col argument to TRUE.

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, the SORT 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 to FALSE.

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.