Lookup & reference
FILTER function
Rows or columns that meet a condition. FILTER works in Sheets with the usual arguments and results, on cells and on live data from the tools you connect.
Syntax
=FILTER(array, include, [if_empty]) | Argument | Use |
|---|---|
array | required |
include | required |
if_empty | optional |
Example
Spill the paid orders into the cells below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Customer | Region | Status | Amount | Date |
| 2 | #1001 | Acme Ltd | North | Paid | 1200 | 2026-01-14 |
| 3 | #1002 | Blue Fox | South | Open | 450 | 2026-01-20 |
| 4 | #1003 | Acme Ltd | North | Paid | 980 | 2026-02-03 |
| 5 | #1004 | Cedar & Co | East | Overdue | 2300 | 2026-02-11 |
| 6 | #1005 | Blue Fox | South | Paid | 610 | 2026-03-02 |
| 7 | #1006 | Delta BV | West | Open | 1750 | 2026-03-09 |
| 8 | #1001 | Acme Ltd | ||||
| 9 | #1003 | Acme Ltd | ||||
| 10 | #1005 | Blue Fox |
Result computed by the Sheets formula engine; it spills into the cells below.
On live data
In Sheets, FILTER sits next to live data from a connected tool: =data.orders.sum(total) totals a table, =list.orders(customer) spills a column's distinct values, and a linked table column such as table.orders[Total] is a range any function accepts.
Try it in a free calculator
Box and whisker plot makerPaste your numbers to draw a box and whisker plot with the minimum, quartiles, median, maximum, IQR and outliers, each worked out by a spreadsheet formula you can copy..Find duplicates in a listPaste a list to flag every duplicate, count the distinct values and list the ones that repeat, with the COUNTIF and UNIQUE formulas that do it in any spreadsheet..
More lookup & reference functions
INDEXValue at a row/column of a rangeMATCHPosition of a value in a rangeSORTSort the rows or columns of an arrayUNIQUEDistinct rows or columns of an arrayVLOOKUPLook up down the first columnXLOOKUPLook up a value, return the matching itemXMATCHPosition of an item in a row or columnADDRESSCell address as textAREASNumber of areas in a referenceCHOOSEPick the Nth valueCHOOSECOLSSelected columns of an arrayCHOOSEROWSSelected rows of an array
Spreadsheet formulas on live business data
Sheets evaluates 491 spreadsheet functions, on cells and on data read live from your tools.