Excel's SORT function is used to dynamically sort data, and supports sorting by columns or rows. The syntax is =SORT (array, [sorting sequence], [sorting method], [row-by-row]). For example, =SORT(A2:C10, 2, -1) means sorting in descending order of the second column; supports multiple column sorting, such as =SORT(A2:C10, {1,3}, {1,-1}) means ascending order in the first column and then descending order in the third column; it can be used in combination with functions such as FILTER, UNIQUE, etc., such as =SORT(FILTER(A2:C10, C2:C10>1000), 3, -1); when using it, please note that the version supports dynamic arrays, the sorting sequence range is correct, and the result area has no content to avoid overflow errors.

Excel's SORT function is a very practical tool that allows you to sort data without changing the original data. It is especially suitable for new versions of Excel with powerful dynamic arrays (such as Microsoft 365 or Excel 2021). Let's take a look at how to use this function well.

Basic syntax and usage

The structure of the SORT function is very simple:

 =SORT(array, [sorting sequence], [sorting method], [rowwise or not])

• Array : The range of data you want to sort, such as A2:C10.
• Sort column : Which column do you want to sort by, representing which column (or row, if arranged by row) is numbered.
• Sort by : ascending order (default is 1), write -1 in descending order.
• Whether row by row : The default is to sort by column. If you want to sort by row, set to TRUE or 1.

For example:

 =SORT(A2:C10, 2, -1)

This means that the A2:C10 area is sorted in descending order by column 2 (i.e. column B).

How to sort multiple columns?

If you need to sort by one column first and then sort by another column, you need to use the "sorting sequence" and "sorting method" in the form of an array.

For example, first ascending order by department (column 1), then descending order by salary (column 3):

 =SORT(A2:C10, {1,3}, {1,-1})

There are two key points to pay attention to:

• The sorting sequence and sorting method should be enclosed in curly braces {} to indicate multiple conditions.
• The order of sorting sequences determines priority, with the previous one taking precedence over the next one.

Use it more powerfully in combination with other functions

SORT is often used with functions such as FILTER and UNIQUE , which can achieve more complex data processing.

For example, find all records with sales amounts greater than 1000 and sort them from high to low:

 =SORT(FILTER(A2:C10, C2:C10>1000), 3, -1)

Or, get the unique value and sort:

 =SORT(UNIQUE(A2:A10))

This way you can quickly build a dynamically updated filter sorting report.

Notes and FAQs

Although SORT is convenient, there are several details that are prone to errors:

• It only returns the sorted results and does not modify the original data.
• If your Excel does not support dynamic array formulas, an error will be reported.
• The sorting column must be within the specified array range, otherwise an error will be prompted.
• If there is content in the result area, an "overflow" error will pop up.

Solution:

• Make sure you are using a version that supports dynamic arrays.
• Leave enough blank space for result output before sorting.
• IFERROR can be nested to avoid the error display being too abrupt.

In general, mastering SORT can make your data collation more efficient. Combining other functions can almost replace many operations that were previously required to be completed with VBA. Basically, that's all, it doesn't seem difficult, but paying attention to details when using them can avoid getting stuck.

The above is the detailed content of How to use the SORT function in Excel. For more information, please follow other related articles on the PHP Chinese website!