Sort by Multiple Columns with a Formula

Excel 365Excel 2021+

To sort a table by more than one column with a live formula — region A–Z, then sales high–low within each region — use SORTBY. It returns a spilled, self-updating sorted copy without touching your source data.

Quick formula: to sort A2:C100 by column A ascending, then column C descending:

=SORTBY(A2:C100, A2:A100, 1, C2:C100, -1)

List each sort key and its direction (1 = ascending, -1 = descending) in priority order.

Functions used (tap for the full reference guide):

SORT SORTBY

The example

Sorted by Region (A–Z), then Sales (high–low) within each region.

	A	B
1	Region	Sales
2	East	$170
3	East	$90
4	West	$200
5	West	$120

The formula

Region ascending, then Sales descending:

=SORTBY(A2:B5, A2:A5, 1, B2:B5, -1) // East before West; within each, biggest first

How it works

SORTBY sorts one array by one or more other arrays:

The first argument is the data to return (A2:B5).
Then come key/direction pairs: A2:A5, 1 sorts by region ascending first.
The next pair, B2:B5, -1, breaks ties by sales descending — so within East and within West, the biggest sale is on top.
The result spills as a sorted copy; the original data is untouched and the sort updates live.

SORT vs SORTBY. SORT sorts by column position within the data; SORTBY sorts by separate key arrays (which can even be columns not shown in the output). SORTBY is the flexible one for multi-key sorts.

Try it: interactive demo

Live demo

Choose the secondary sort direction for Sales within each region.

Within region, sort sales

Variations

SORT by column position

Sort the whole table by its 3rd column descending:

=SORT(A2:C100, 3, -1)

Sort by a custom order

Sort by a helper key that encodes your order (e.g. MATCH against a priority list).

Filter then sort

Combine with FILTER:

=SORTBY(FILTER(A2:C100, C2:C100>0), B2:B100, -1)

Pitfalls & errors

Key arrays must match the data height. Each sort key range needs the same number of rows as the data, or SORTBY returns #VALUE!.

SORTBY needs Excel 365/2021. Older versions show #NAME? — use the Data → Sort dialog (with multiple levels) there.

It returns a copy. The spilled result is separate from the source; edits to the source reflow it, but you can’t edit the sorted spill directly.

Practice workbook

📊

Download the free Sort by Multiple Columns with a Formula practice workbook

A table with live SORTBY multi-key sorting, the SORT and FILTER+SORT variants (results shown), plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I sort by multiple columns with a formula in Excel?

Use SORTBY with key/direction pairs: =SORTBY(data, key1, 1, key2, -1). The first key sorts primarily and later keys break ties. 1 is ascending, -1 descending. Requires Excel 365/2021.

What's the difference between SORT and SORTBY?

SORT sorts by a column position inside the data. SORTBY sorts by separate key arrays, which can include columns not shown in the output, making it ideal for multi-key sorts.

How do I filter and sort at the same time?

Nest them: =SORTBY(FILTER(data, condition), key, -1) filters first, then sorts the result.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: SORT · SORTBY