How to SORT an EXCEL matrix with Multiple Criteria

Clarification: How to initiate a dialogue to build the SORT

To sort an Excel range or table by multiple criteria, use either the built‑in Sort dialog or dynamic array functions like SORT/SORTBY.

1. Sort a range/table by multiple columns (UI)

1. Select a single cell in the range or table to sort.
2. On the Data tab, in the Sort & Filter group, select Sort.
3. In the Sort by dropdown, choose the first column to sort on.
4. In Sort On, choose Values, Cell Color, Font Color, or Cell Icon.
5. In Order, choose the order (A to Z / Z to A, or smallest to largest / largest to smallest).
6. Click Add Level to add another sort criterion.
   • For example: first sort by Department (A to Z), then by Last Name (A to Z).
   • The second level only applies when there are ties in the first level.
7. Repeat for as many levels as needed, then select OK.

You can also start from Home > Sort & Filter > Custom Sort and then add multiple levels in the same way.

2. Sort an array with the SORT function (formula)

Use SORT when the data is already in a single array and only one sort key is needed, or when sort index is sufficient:

=SORT(array, [sort_index], [sort_order], [by_col])

• array: range/array to sort (required).
• sort_index: row/column number in the array to sort by (optional; default is 1).
• sort_order: 1 for ascending (default), -1 for descending.
• by_col: FALSE or omitted to sort by rows; TRUE to sort by columns.

SORT returns a spilled array; Excel automatically sizes the output range.

3. Sort by multiple criteria with SORTBY (formula)

For multiple criteria in formulas, use SORTBY:

=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], …)

• array: the full range to return, sorted.
• by_array1: first key (e.g., a column of Department values).
• sort_order1: 1 for ascending, -1 for descending (optional; default 1).
• by_array2, sort_order2: second key and order, and so on.

Example: sort a table in A2:D100 first by Department (A2:A100 ascending), then by Salary (D2:D100 descending):

=SORTBY(A2:D100, A2:A100, 1, D2:D100, -1)

This returns the entire table sorted by multiple criteria. Because SORTBY works on ranges instead of column index numbers, it is more robust if columns are inserted or deleted.

References:

• Quick start: Sort data in an Excel worksheet
• Video: Sort table contents
• SORT function
• SORTBY function