Sort by Multiple Columns in Excel (Formula and Menu)

To sort by multiple columns in Excel, you have two choices. The Sort dialog (Data > Sort) lets you add levels, such as "Region A to Z, then Sales largest first", and reorders the data in place. A formula with SORTBY or SORT does the same sort in a new spot and keeps it up to date. This guide shows both on one small table, so you can pick the right one for a report or a job test.

What does sorting by multiple columns mean?#

Sorting by multiple columns means one column decides the main order, and the next column only decides the order of rows that tie. Think of a phone book: sorted by last name, then by first name for people who share a last name.

All the examples use this sales list in A1:C9:

The goal: group the rows by Region (A to Z), and inside each region put the biggest sales first. The result should be:

How to sort by multiple columns with the Sort dialog#

The Sort dialog works in every version of Excel, on Windows and Mac. Use it when you want to reorder the data itself.

1. Click one cell inside the data. Don't select a single column, or Excel may sort only that column.
2. Go to Data > Sort. On Windows, the ribbon keys are Alt, A, S, S.
3. Make sure My data has headers is ticked, so row 1 stays on top.
4. In Sort by, choose Region. Keep Sort On as Cell Values and Order as A to Z.
5. Click Add Level. In Then by, choose Sales and set Order to Largest to Smallest.
6. Click OK.

The rows now match the result table above. You can add up to 64 levels. Use Delete Level to remove one, Copy Level to duplicate it, and the up and down arrows to change which level runs first. The top level always wins.

If your data has a filter on it, the same dialog opens from the filter arrow menus too. Our beginner's guide to sorting data covers single-column sorts and the warning boxes Excel shows.

How to sort by multiple columns with SORTBY#

SORTBY sorts a range by one or more other ranges, and you list them in order of importance. The syntax is:

=SORTBY(array, by_array1, sort_order1, by_array2, sort_order2, ...)

Type this in E2 and press Enter:

=SORTBY(A2:C9,A2:A9,1,C2:C9,-1)

The formula spills: it fills E2:G9 with the sorted table, starting with East, Femi, 3700. If you change Gus's sales to 6000, he jumps to the top of North right away. The cells to the right and below E2 must be empty, or you get a #SPILL! error.

SORTBY is handy because the sort columns don't have to be part of the result. =SORTBY(B2:B9,A2:A9,1,C2:C9,-1) returns only the rep names, in the same order.

How to sort by multiple columns with SORT#

SORT can also use more than one column. Put the column numbers in curly brackets as the second argument, and the orders in curly brackets as the third:

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

{1,3} means "sort by column 1 of the range, then column 3". {1,-1} means "column 1 ascending, column 3 descending". The result is the same as the SORTBY formula above.

SORT is shorter when the sort columns are inside the range you return. SORTBY is clearer to read and doesn't break if you insert a column, because it points at real ranges instead of column numbers. See SORT vs SORTBY for a full comparison, and the SORT function guide for every argument.

Sort button or formula: which should you use?#

Use the Sort button when you want to tidy the data once. Use a formula when the data changes and the sorted view must stay correct.

SORT and SORTBY need Excel 2021, Excel 2024 or Microsoft 365. In older versions they show #NAME?. The article on the Sort button vs the SORT function goes deeper into this choice. For orders that aren't alphabetical, read how to do a custom sort.

Common mistakes when sorting by several columns#

Most multi-column sorting problems come from the level order or the selection. Check these:

• Levels in the wrong order. If Sales is the top level, the whole list sorts by sales and the regions get mixed. Move Region to the top with the arrow buttons.
• Only one column moved. If you selected column C and chose Continue with the current selection, the sales values moved but the names didn't. Press Ctrl+Z (Mac: ⌘Z) at once to undo.
• Headers sorted into the data. Tick My data has headers, or the word "Region" ends up between the rows.
• Numbers stored as text. "900" stored as text sorts as text, so it can land after "4200". Convert the column to numbers first.
• Wrong sign in SORTBY. Use 1 or -1. A 0 or a 2 returns a #VALUE! error.
• Blank rows. A blank row splits the data, and the Sort dialog may only see the part above it. Remove blank rows or select the whole range first.

If you need to filter and sort together, wrap a FILTER inside SORTBY, or turn on filter buttons with Ctrl+Shift+L (Mac: ⌘⇧F) and sort from the arrows.

Practise it#

Multi-level sorts are a common task in Excel job tests, and they're easy to get backwards. Practise the SORT function in a live spreadsheet: you type the formula and it checks it instantly. Then practise filtering and sorting a table with the filter buttons. Start with the exercise on this page: one SORTBY formula, two sort levels.