Sort and Filter Together With One Formula

A sort and filter formula in Excel puts FILTER inside SORT, so one cell returns only the rows you want, already in order. =SORT(FILTER(A2:D8,B2:B8="North"),4,-1) lists the North sales from largest to smallest, and it updates by itself when the data changes. This guide is for anyone who builds reports or dashboards in Excel 2021 or Microsoft 365 and is tired of clicking the filter and sort buttons again every week.

The sort and filter formula pattern#

The pattern has two layers. FILTER chooses the rows, and SORT puts them in order:

=SORT(FILTER(array, include, [if_empty]), [sort_index], [sort_order], [by_col])

Both functions are dynamic array functions: the result spills, which means it fills as many cells as it needs, starting from the cell with the formula. They need Excel 2021 or Microsoft 365. The FILTER function guide and the SORT function guide cover each argument in detail.

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

Filter one region and sort by sales#

Click F2 and type:

=SORT(FILTER(A2:D8,B2:B8="North"),4,-1)

FILTER keeps the four North rows. SORT then orders them by the 4th column, Sales, with -1 for largest first. The result fills F2:I5:

The headers are not part of the result. Type or copy them into row 1 above the formula. If you change Chen's sales to 5,000, he moves to the top straight away. For more on the inner part, see the complete guide to the FILTER function.

Why FILTER goes inside SORT#

You could also write SORT inside FILTER, but filtering first is usually better. There are two reasons:

1. SORT has less work to do. It only orders the rows that passed the filter.
2. The column number is simpler. sort_index counts the columns of whatever SORT receives. Here FILTER returns all four columns, so Sales is column 4. If FILTER returned only columns B to D, Sales would be column 3.

That second point is the most common mistake. The number is a position in the result, not a column letter on the sheet.

Add more conditions: AND and OR#

FILTER handles several conditions with simple maths on the TRUE/FALSE tests. Put each test in brackets.

AND (both must be true): multiply. North laptops, largest first:

=SORT(FILTER(A2:D8,(B2:B8="North")*(C2:C8="Laptop")),4,-1)

The result is Ana (4,200) then Ella (3,900).

OR (either can be true): add. North or South, largest first:

=SORT(FILTER(A2:D8,(B2:B8="North")+(B2:B8="South")),4,-1)

This returns six rows, starting with Femi (4,700) and Ana (4,200). The guide to FILTER with multiple criteria covers longer combinations, and you can also filter by part of a word, as shown in FILTER rows that contain text.

To make the formula easy to reuse, point the condition at a cell. If G1 holds a region name, B2:B8=G1 filters by whatever you type there.

Sort by two columns#

Give SORT a list of columns and a list of orders, in curly brackets. This takes the laptop rows, then sorts by Region A to Z, then by Sales largest first within each region:

=SORT(FILTER(A2:D8,C2:C8="Laptop"),{2,4},{1,-1})

Ana comes before Ella because they are both North, and her sales are higher.

To sort by a column you don't want to show, use SORTBY instead of SORT. SORTBY sorts by a separate range of the same height. In Microsoft 365, you can also trim the result to certain columns with CHOOSECOLS: =CHOOSECOLS(SORT(FILTER(A2:D8,B2:B8="North"),4,-1),1,4) returns only Rep and Sales.

Return the top N matching rows#

A sorted, filtered list makes a natural top N report. In Microsoft 365, wrap it in TAKE to keep only the first rows:

=TAKE(SORT(FILTER(A2:D8,C2:C8="Laptop"),4,-1),2)

This returns the top two laptop sales: Dev (5,100) and Femi (4,700). TAKE is newer than SORT and FILTER, so it isn't in Excel 2021. There, use INDEX(...,SEQUENCE(2),SEQUENCE(1,4)) around the same formula to pick the first two rows.

To add up the filtered values instead of listing them, see how to filter and sum with one formula. To keep rows within a date range, see filtering by date.

Common errors and how to fix them#

Most problems with a sort and filter formula come from five causes:

If you add new rows below the data, a fixed range like A2:D8 won't include them. Turn the data into an Excel table with Ctrl+T (Mac: ⌘T) and use the table's column names, so the ranges grow by themselves.

Practise it#

This formula is a favourite in modern Excel tests because it shows you understand spilled results. Try the exercise on this page first. Then practise SORT in a live spreadsheet: you type the formula and it checks it instantly. Follow it with the FILTER function lesson to build conditions with AND and OR.