Google Sheets Filter and Sort Formulas With Examples

In Google Sheets, use FILTER to return rows that meet a condition and wrap it in SORT to order those results: with data in A2:D6 and status in column C, enter =SORT(FILTER(A2:D6,C2:C6="Open"),4,FALSE) in an empty cell. It returns the Open rows sorted by the fourth column, sales, from highest to lowest.

The basic FILTER and SORT formulas

FILTER selects matching rows without changing the source data. Its basic pattern is FILTER(range, condition1, [condition2, ...]). The range is the data to return, and each condition checks the corresponding rows. SORT orders a range by one or more columns. In a combined formula, FILTER runs first and SORT orders the rows it returns.

For the opening example, imagine A1:D6 has headers Item, Region, Status, and Sales. Rows 2 through 6 contain Ruler / North / Open / 120; Notebook / West / Closed / 250; Folder / North / Open / 80; Marker / East / Open / 300; and Stapler / West / Open / 180. With those values in A2:D6, this formula returns Marker, Stapler, Ruler, and Folder, in that order:

With the sample records in A2:D6 and each status in C2:C6, enter =SORT(FILTER(A2:D6,C2:C6="Open"),4,FALSE). The result is Marker (300), Stapler (180), Ruler (120), and Folder (80). The number 4 means the fourth column of the filtered range, and FALSE requests descending order.

Put the formula in a blank area, not inside the source data. The result expands into neighboring cells, so those cells must also be clear. The formula does not rearrange or delete the original records.

How the sort arguments work

In SORT(range, sort_column, is_ascending, ...), the sort-column number refers to a column within the range being sorted, not necessarily the worksheet’s column letter. If the range is A2:D6, 1 means Item, 2 means Region, 3 means Status, and 4 means Sales. TRUE sorts ascending; FALSE sorts descending. For text, ascending is generally alphabetical; for numbers, it is smallest to largest; for dates, earliest to latest.

To sort first by Region alphabetically and then by Sales from largest to smallest, use multiple sort-column and direction pairs. With the same sample records in A2:D6, enter =SORT(A2:D6,2,TRUE,4,FALSE). The rows are grouped by region, and sales determine the order within each region. A multi-column sort can make a report easier to scan without changing how the source table is arranged.

When sorting filtered results, the column numbers still refer to the filtered output’s columns. If FILTER returns all four columns from A:D, 4 sorts by Sales. If it returns only A and D, then 2—not 4—refers to Sales in that two-column result.

Filter with more than one condition

To require multiple conditions, supply each condition as another FILTER argument. The rows must satisfy all supplied conditions. For instance, this keeps records that are both Open and worth at least 150. With records in A2:D6, status in C2:C6, and sales in D2:D6, enter =FILTER(A2:D6,C2:C6="Open",D2:D6>=150). The result is Marker and Stapler, because their sales are 300 and 180; Ruler and Folder do not meet the sales threshold.

For an OR condition, combine comparisons with addition. With the same sample table and regions in B2:B6, enter =FILTER(A2:D6,(B2:B6="North")+(B2:B6="West")). This returns rows from either North or West, including the Closed Notebook row because the only condition here is its region. The parentheses make the comparisons clear, and the plus sign combines their TRUE/FALSE results.

Pair either type of filter with SORT when you want a particular order. For example, after filtering for North or West, you can wrap the FILTER expression in SORT and specify the desired output column and direction. Keep the ranges aligned: the data range and every condition range should cover the same number of rows.

Make the result update as records are added

A bounded range such as A2:D500 is easy to understand and limits how much of the sheet the formula considers. If new records are regularly added below the current data, an open-ended range can include them automatically. With headers in row 1, data in columns A:D, and status in column C, enter =SORT(FILTER(A2:D,C2:C="Open"),4,FALSE). As Open records are added below row 6, they can appear in the result without editing the formula.

Open-ended ranges also include many blank rows, so they may be less efficient in a very large or formula-heavy sheet. Conditions such as C2:C="Open" exclude blank status cells from this example, but the sheet still has to evaluate a broad range. Choose a sensible upper limit, or use an open-ended range when automatic growth matters more than restricting the calculation.

These formulas recalculate when source values change. They are useful for live summaries, but a result that changes automatically may not be appropriate when you need a frozen snapshot. To preserve a particular result, copy it and use Edit > Paste special > Values only in the destination area. Check that the copied output fits before pasting so it does not overwrite unrelated information.

Choose a formula or a filter view

Formula-based filtering is a good fit when you want a separate report that updates automatically, can be placed on another tab, or feeds another formula or chart. It keeps the original table intact and makes the conditions visible in the formula. The trade-off is that the result is a calculated output: you generally edit the source records, not the returned cells.

For interactive exploration of the original table, use Google Sheets’ menus instead. Select the data, then choose Data > Create a filter to show filter controls on the header row. You can filter or sort the displayed records without writing a formula. For a saved view that does not impose the same view on everyone working in the sheet, use Data > Filter views > Create new filter view. Menu-based sorting is also available through Data > Sort range.

Use FILTER and SORT formulas for a live, reusable output or a report on another sheet.
Use a standard filter when you want to inspect or temporarily narrow the source table.
Use a filter view when you want a saved way to examine data without rebuilding a formula.

Common errors and how to fix them

No matches: FILTER returns #N/A when no rows meet its conditions. To display a message instead, with records in A2:D6 and statuses in C2:C6, enter =IFNA(FILTER(A2:D6,C2:C6="Pending"),"No matching rows"). It returns “No matching rows” if none of the five sample records has Pending status. IFNA handles this no-match error; it is not a fix for every possible formula problem.
Array could not expand: A value, another formula, or a merged cell may occupy part of the output area. Clear the cells where results need to appear, or move the formula to a wider empty area.
Range-size mismatch: Each condition range must line up with the rows in the filtered range. If the data is A2:D100, a condition such as C2:C100 aligns; C2:C50 does not.
Wrong sort column: SORT counts columns from the left edge of its input range. For a range beginning at B, column 1 means B, not worksheet column A.
Unexpected order: Numbers or dates stored as text may sort like text rather than as numeric values or dates. Check the source data’s values and formatting. Formatting alone may not convert text into a true number or date.
Formula parse error: Google Sheets’ argument separator can depend on the spreadsheet’s locale. If a comma-separated formula is rejected, check the locale under File > Settings and use the separator expected by that sheet. The formulas here use commas.

Useful variations and practical limits

You can use FILTER without SORT when the source order is already useful, or SORT without FILTER when you want every row ordered. You can also filter on a column you do not return: for example, return just item names while checking status in column C. The condition still needs to cover the same rows as the returned range.

One common misconception is that the combined formula sorts the original table. It does not: it creates a sorted result elsewhere. If a collaborator edits the source, the formula output may change as well. Another is that a filter formula automatically includes the header row. Usually it should not; start the formula at row 2 and keep the headings above the result or add suitable headings separately.

Google Sheets has its own formula behavior and interface, even though Excel for Microsoft 365 also offers functions named FILTER and SORT. Do not assume every spreadsheet feature, separator setting, or menu path transfers exactly between the two programs. If the sheet will be exported or shared with Excel users, check the formulas and resulting data in the destination application.

Frequently Asked Questions

Can I combine FILTER and SORT in one Google Sheets formula?
Yes. Put FILTER inside SORT, then set the sort-column number and direction. The combined formula returns only matching rows and orders that result.

How do I sort filtered results from largest to smallest?
Use FALSE for the sort direction in SORT. Make sure the sort-column number points to the numeric column in the range returned by FILTER.

Why does FILTER show #N/A?
It usually means no rows matched the conditions. If that is an expected possibility, wrap FILTER in IFNA and provide a message or other fallback result.

Can I filter by two conditions at once?
Yes. Add another condition argument to FILTER to require both conditions. To match either of two alternatives in the same column, combine the comparisons with addition.

Will the formula change my original data?
No. FILTER and SORT return a calculated array in the cells where you enter the formula. To rearrange the source table itself, use the sheet’s sorting controls instead.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.