How to use the FILTER function
✓ Verified in LibreOffice 25.8.7.3Return every row that matches a condition — a live, self-updating subset of your data.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =FILTER(A2:B10,C2:C10>100) | FILTER(range to return, condition, [if empty]). Needs Excel 2021+/365; the result spills. |
| Google Sheets | =FILTER(A2:B10,C2:C10>100) | Identical. |
| LibreOffice Calc | =FILTER(A2:B10,C2:C10>100) | Needs LibreOffice 24.8+. |
How it works
FILTER keeps only the rows where the condition is true and spills them into place: the values with B over 100 are apple (150) and cherry (200), so those two are returned. The condition is an array of TRUE/FALSE the same height as the data. For multiple conditions, multiply them for AND (C2:C10>100)*(A2:A10="East") or add them for OR; give a third argument to control what shows when nothing matches (else it returns #CALC!). Because it's live, the result updates as the data changes — wrap it in SORT or UNIQUE to order or de-duplicate the matches. FILTER needs a modern version (Excel 2021+/365, Google Sheets, LibreOffice 24.8+); older ones use AutoFilter or array formulas.
Verified, not just documented
We ran =FILTER(A2:A4,B2:B4>100) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned apple, cherry — exactly the expected result. Every formula here is confirmed by actually executing it.