AI Excel Generator
← ALL GUIDES

How to Combine FILTER and SORT in Excel

Updated November 4, 2026 · AI Excel Generator

Combine Excel’s FILTER and SORT functions to return only rows that meet a condition and arrange those rows in a chosen order.

A single formula can keep the results current as source data changes, and a few range choices determine whether it works as expected.

Basic FILTER and SORT formula

FILTER selects matching rows from a range, while SORT orders the rows it returns.

Nest FILTER inside SORT to sort only the filtered results:

=SORT(FILTER(array,include),sort_index,sort_order)

In this pattern, array is the data to return, include is a TRUE/FALSE test for each row, sort_index identifies the column to sort by within the returned array, and sort_order is 1 for ascending or -1 for descending.

Suppose A2:C6 contains this table, with headers in row 1:

  • A: Product
  • B: Category
  • C: Price

To return products in the “Office” category, sorted from lowest to highest price, enter this formula in an empty cell:

=SORT(FILTER(A2:C6,B2:B6="Office","No matches"),3,1)

The include test, B2:B6="Office", checks each category.

FILTER returns the matching rows from A2:C6, and SORT orders that result by its third column, Price.

If no row matches, the optional if_empty argument displays “No matches.”

Sort by a different column

The sort index refers to a column’s position in the array passed to SORT, not its worksheet column letter.

For A2:C6, column A is index 1, B is 2, and C is 3.

To sort matching Office products alphabetically by name, use:

=SORT(FILTER(A2:C6,B2:B6="Office","No matches"),1,1)

To show the most expensive matching products first, keep the price index and use descending order:

=SORT(FILTER(A2:C6,B2:B6="Office","No matches"),3,-1)

When you filter a non-contiguous or rearranged array, count columns from the left edge of that array.

For example, if the array is C2:A6, its first column is the worksheet’s C column.

Filter with more than one condition

FILTER’s include argument can combine tests.

Multiplication acts like AND: both conditions must be true.

For example, return Office products that cost less than $50, sorted by price ascending:

=SORT(FILTER(A2:C6,(B2:B6="Office")*(C2:C6<50),"No matches"),3,1)

In HTML, the less-than operator is written as &lt; so the browser displays it as <.

In Excel, enter the formula with the ordinary < comparison operator.

To match either of two categories, use addition as OR:

=SORT(FILTER(A2:C6,(B2:B6="Office")+(B2:B6="Storage"),"No matches"),1,1)

Each condition needs to produce a TRUE/FALSE result for every row in the source data.

The condition ranges must have the same number of rows as the array being filtered.

Sort by multiple columns with SORTBY

SORT can order by one column of the filtered result.

Use SORTBY when you need a primary sort and a tie-breaker.

For example, sort Office products by category and then by price descending:

=IFERROR(SORTBY(FILTER(A2:C6,B2:B6="Office"),FILTER(B2:B6,B2:B6="Office"),1,FILTER(C2:C6,B2:B6="Office"),-1),"No matches")

Each SORTBY sort array must have the same height as the filtered result.

For a larger table, use a structured reference or consistently sized ranges to reduce the chance that the sort arrays and filtered rows become misaligned.

Handle no matches and blank rows

FILTER’s third argument, if_empty, handles the case where nothing meets the condition.

Include it inside FILTER, as in the examples above.

Without it, Excel returns a #CALC! error when there are no matches.

To exclude blank product names while filtering for Office, add a not-blank test:

=SORT(FILTER(A2:C100,(B2:B100="Office")*(A2:A100<>""),"No matches"),3,1)

Excel’s not-equal operator is <>.

In the formula, compare the product-name cells with an empty string to omit rows that would otherwise appear as blank entries.

Dynamic array behavior and compatibility

FILTER and SORT return dynamic arrays.

Enter the formula in one cell and Excel spills the results into neighboring cells; do not copy it down like a traditional row formula.

Keep the spill area clear.

If a cell in that area is occupied, Excel may show #SPILL!.

These functions are available in Microsoft 365 and Excel 2021 or later, including Excel for the web.

They are not available as native worksheet functions in older perpetual versions such as Excel 2019 and Excel 2016.

In those versions, use alternatives such as AutoFilter with a separate sort, or a helper-column approach; the exact option depends on whether the result must update automatically.

In Google Sheets, the corresponding functions are also named FILTER and SORT, but syntax details can differ.

Check the spreadsheet’s locale, since some locales use semicolons rather than commas between function arguments.

Troubleshoot common formula errors

  • #SPILL!: Clear cells in the expected output area, including cells that look empty but contain spaces or formulas.
  • #CALC!: Add FILTER’s if_empty argument or verify that the condition actually matches data.
  • Incorrect sort order: Check that sort_index counts columns in the filtered array, and use 1 for ascending or -1 for descending.
  • Range size error: Make sure the array and each include condition cover the same rows.
  • Numbers sort like text: Convert text-formatted numbers to numeric values; otherwise, sorting may follow text order instead of numeric order.
  • Unexpected matches: Check for leading or trailing spaces in category values, and confirm that the comparison text matches the source data.

For an expanding dataset, an Excel Table can make references easier to maintain.

If the table is named Inventory and has columns Product, Category, and Price, a table-based formula can be written as:

=SORT(FILTER(Inventory,Inventory[Category]="Office","No matches"),3,1)

Structured references expand as table rows are added.

The sort index still refers to the position of the column in the returned array, so verify the table’s column order if you later rearrange its fields.