How to sort by multiple columns in Excel

The Sort dialog: one level per column

The task list should read team by team, with each team's tasks in due-date order. That is two sort keys: Team first, then Due. The A to Z and Z to A buttons only sort by one column, so you need the full Sort dialog, which lets you stack up to 64 levels.

Order matters. The top level is the main grouping; each level below only breaks ties left by the levels above. After this sort Finance comes first, starting with Close October books (6 Nov), then IT, then Sales. Swap the two levels and you get a list in date order where the teams are mixed together, which answers a different question.

1. Click any single cell inside the data. Do not select one column, or Excel will ask whether to expand the selection.
2. Go to Data > Sort.
3. Check that My data has headers is ticked, so row 1 stays on top.
4. Under Sort by choose Team, Sort On Cell Values, Order A to Z.
5. Click Add Level. Under Then by choose Due, Order Oldest to Newest.
6. Click OK.

Why sorting one column is not enough

Sorting by Team alone groups the rows but says nothing about the order inside each group. The formula below is the formula version of clicking Sort A to Z on the Team column. Each team is together, but here within Finance the Q4 forecast (due 20 Nov) sits above Close October books (due 6 Nov).

People often try to fix this by sorting Team, then sorting Due. The second sort reorders the whole list by date and breaks up the teams again. Use levels in one sort instead.

Add a third level to break ties

Two levels are not always enough. IT has two tasks due on 9 Nov, and Finance has two due on 20 Nov. With only Team and Due as keys, Excel has no rule for which of each pair comes first, so the order of those rows is not something you chose.

Count the ties before you decide how many levels you need. This COUNTIFS checks how many rows share row 3's team and due date. Fill it down: any result above 1 is a tie. Across the whole list, =SUMPRODUCT(--(COUNTIFS(B2:B10,B2:B10,D2:D10,D2:D10)>1)) returns 4, meaning four rows are in a tie. Add a third level, such as Priority or Hours, to settle them.

Sort High, Medium, Low in that order

Sorting Priority A to Z gives High, Low, Medium, because it is alphabetical. For an order that is not alphabetical, use a custom list in the Order box. Excel remembers the list, so you only create it once per computer.

If you would rather not create a custom list, add a helper column with a rank number and sort by that column instead. MATCH finds each priority's position in a fixed list, so High is 1, Medium 2 and Low 3. Fill it down, sort by it smallest to largest, and the helper works in every Excel version and survives being emailed to someone else.

1. In the Sort dialog, add a level for Priority.
2. In its Order box choose Custom List.
3. Under List entries type High, Medium, Low, each on its own line, then click Add and OK.
4. Move the Priority level to where it belongs with the up and down arrows, then click OK.

A formula that stays sorted: SORTBY and SORT (Excel 365 / 2021)

The Sort dialog rearranges the data once. If new tasks keep arriving and you want a view that re-sorts itself, use a formula on another sheet or to the right of the data. =SORTBY(A2:E10,B2:B10,1,D2:D10,1) sorts the rows by Team A to Z, then by Due oldest first. Each extra key is another pair of range and order, where 1 is ascending and -1 descending.

SORT does the same with arrays: =SORT(A2:E10,{2,4},{1,1}) sorts by the 2nd column, then the 4th. SORTBY is usually easier to read and can sort by a column you do not return, and it handles a custom order too: =SORTBY(A2:E10,B2:B10,1,MATCH(C2:C10,{"High","Medium","Low"},0),1,D2:D10,1) sorts by team, then priority, then due date. These array forms are valid in Excel 365 and 2021, but the Cellwise practice engine cannot run them, so try them in Excel. In Excel 2019 and earlier, neither function exists; use the Sort dialog.

Keep the header row out of the sort

If you include row 1 in a SORT formula, or untick My data has headers in the dialog, the header is sorted like any other row. Here "Team" sorts after "Sales", so the header row lands at the bottom of the result.

In a formula, start the range at row 2 and type the headers above it. In the dialog, keep My data has headers ticked; Excel guesses this setting from your data, so check it every time, especially when the header and the data are both text.