--- title: "FILTER function in Excel: syntax and examples | PracticeExcel" description: "How to use FILTER in Excel 365 and Google Sheets: pull out the rows that match a condition with one formula." canonical_url: "https://practiceexcelonline.com/functions/filter" locale: en meta: "og:description": "How to use FILTER in Excel 365 and Google Sheets: pull out the rows that match a condition with one formula." "og:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RklMVEVSIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBGSUxURVIgaW4gRXhjZWwgMzY1IGFuZCBHb29nbGUgU2hlZXRzOiBwdWxsIG91dCB0aGUgcm93cyB0aGF0IG1hdGNoIGEgY29uZGl0aW9uIHdpdGggb25lIGZvcm11bGEu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~RklMVEVSKGFycmF5LCBpbmNsdWRlLCBbaWZfZW1wdHldKQ%2Cp_Ii9mdW5jdGlvbnMvZmlsdGVyIg%2Cs_11uUw-7D1OwxjyFr.png" "og:image:height": "600" "og:image:type": image/png "og:image:width": "1200" "og:title": "FILTER function in Excel: syntax and examples | PracticeExcel" "twitter:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RklMVEVSIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBGSUxURVIgaW4gRXhjZWwgMzY1IGFuZCBHb29nbGUgU2hlZXRzOiBwdWxsIG91dCB0aGUgcm93cyB0aGF0IG1hdGNoIGEgY29uZGl0aW9uIHdpdGggb25lIGZvcm11bGEu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~RklMVEVSKGFycmF5LCBpbmNsdWRlLCBbaWZfZW1wdHldKQ%2Cp_Ii9mdW5jdGlvbnMvZmlsdGVyIg%2Cs_11uUw-7D1OwxjyFr.png" --- > Index of every page on this site: [llms.txt](https://practiceexcelonline.com/llms.txt) # **The FILTER function** Returns the rows of a range that meet a condition, as a list that spills into the cells below. = FILTER( ## **Syntax** ``` FILTER(array, include, [if_empty]) ```
**array **
The range to filter.
**include **
A condition per row, such as A2:A6="South".
**if_empty **(optional)
What to return when no rows match, instead of #CALC!. Excel only: Google Sheets reads extra arguments as more conditions.
## **Examples** E1`=FILTER(1C2:C6,2A2:A6="South")` | | A | B | C | D | E | | --- | --- | --- | --- | --- | --- | | 1 | Region | Product | Sales | | **300** | | 2 | North | Shoes | 500 | | **180** | | 3 | South | Shoes | 300 | | | | 4 | North | Bags | 250 | | | | 5 | North | Shoes | 420 | | | | 6 | South | Bags | 180 | | | **In plain words:** Return 1the sales in C2:C6 on the rows where 2the region in A2:A6 is South. E1=FILTER(A2:A6,C2:C6>1000,"None") | | A | B | C | D | E | | --- | --- | --- | --- | --- | --- | | 1 | Region | Product | Sales | | **None** | | 2 | North | Shoes | 500 | | | | 3 | South | Shoes | 300 | | | | 4 | North | Bags | 250 | | | | 5 | North | Shoes | 420 | | | | 6 | South | Bags | 180 | | | ## **Practice FILTER** - [**1****Keep the rows for one region with FILTER**→](https://practiceexcelonline.com/practice/filter-function/filter-one-condition) - [**2****FILTER with two conditions**→](https://practiceexcelonline.com/practice/filter-function/filter-two-conditions) - [**3****A message when FILTER finds nothing**→](https://practiceexcelonline.com/practice/filter-function/filter-if-empty) - [**4****A list that depends on the first choice**→](https://practiceexcelonline.com/practice/data-validation/dependent-drop-down-list) ## **Read the guide** - [**Google Sheets vs Excel: what is the same, what is different, and how to switch**](https://practiceexcelonline.com/learn/google-sheets-vs-excel) - [**How to create a drop-down list in Excel, and make it update itself**](https://practiceexcelonline.com/learn/drop-down-list-excel) - [**How to merge cells in Excel, and what to use instead**](https://practiceexcelonline.com/learn/merge-cells-excel) ## **Questions**
### Does FILTER work the same in Google Sheets? Mostly. Google Sheets has no if\_empty argument: extra arguments are more conditions, and it returns #N/A when no rows match. Wrap it in IFNA to show your own text, such as =IFNA(FILTER(A2:A6,C2:C6>1000),"None").
## **Related functions** - [**UNIQUE**](https://practiceexcelonline.com/functions/unique) - [**XLOOKUP**](https://practiceexcelonline.com/functions/xlookup) - [**SUMIFS**](https://practiceexcelonline.com/functions/sumifs) ```json {"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.com/functions/filter#webpage","description":"How to use FILTER in Excel 365 and Google Sheets: pull out the rows that match a condition with one formula.","name":"FILTER function in Excel: syntax and examples","url":"https://practiceexcelonline.com/functions/filter","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/functions/filter#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"mainEntity":[{"@id":"https://practiceexcelonline.com/functions/filter#/schema/question/1"}],"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/functions/filter"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/functions/filter#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://practiceexcelonline.com/functions","position":2},{"@type":"ListItem","name":"FILTER function in Excel: syntax and examples","position":3}]},{"@id":"https://practiceexcelonline.com/functions/filter#article","description":"How to use FILTER in Excel 365 and Google Sheets: pull out the rows that match a condition with one formula.","headline":"FILTER function in Excel: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RklMVEVSIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBGSUxURVIgaW4gRXhjZWwgMzY1IGFuZCBHb29nbGUgU2hlZXRzOiBwdWxsIG91dCB0aGUgcm93cyB0aGF0IG1hdGNoIGEgY29uZGl0aW9uIHdpdGggb25lIGZvcm11bGEu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~RklMVEVSKGFycmF5LCBpbmNsdWRlLCBbaWZfZW1wdHldKQ%2Cp_Ii9mdW5jdGlvbnMvZmlsdGVyIg%2Cs_11uUw-7D1OwxjyFr.png","@type":["Article","TechArticle"],"author":{"@id":"https://practiceexcelonline.com/#identity"},"image":{"@id":"https://practiceexcelonline.com/#/schema/image/1"},"isPartOf":{"@id":"https://practiceexcelonline.com/functions/filter#webpage"},"mainEntityOfPage":{"@id":"https://practiceexcelonline.com/functions/filter#webpage"},"publisher":{"@id":"https://practiceexcelonline.com/#identity"}},{"@id":"https://practiceexcelonline.com/functions/filter#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"Does FILTER work the same in Google Sheets?","acceptedAnswer":{"@type":"Answer","text":"Mostly. Google Sheets has no if_empty argument: extra arguments are more conditions, and it returns #N/A when no rows match. Wrap it in IFNA to show your own text, such as =IFNA(FILTER(A2:A6,C2:C6>1000),\"None\")."}},{"@id":"https://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"},{"@id":"https://practiceexcelonline.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RklMVEVSIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBGSUxURVIgaW4gRXhjZWwgMzY1IGFuZCBHb29nbGUgU2hlZXRzOiBwdWxsIG91dCB0aGUgcm93cyB0aGF0IG1hdGNoIGEgY29uZGl0aW9uIHdpdGggb25lIGZvcm11bGEu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~RklMVEVSKGFycmF5LCBpbmNsdWRlLCBbaWZfZW1wdHldKQ%2Cp_Ii9mdW5jdGlvbnMvZmlsdGVyIg%2Cs_11uUw-7D1OwxjyFr.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RklMVEVSIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBGSUxURVIgaW4gRXhjZWwgMzY1IGFuZCBHb29nbGUgU2hlZXRzOiBwdWxsIG91dCB0aGUgcm93cyB0aGF0IG1hdGNoIGEgY29uZGl0aW9uIHdpdGggb25lIGZvcm11bGEu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~RklMVEVSKGFycmF5LCBpbmNsdWRlLCBbaWZfZW1wdHldKQ%2Cp_Ii9mdW5jdGlvbnMvZmlsdGVyIg%2Cs_11uUw-7D1OwxjyFr.png"}]} ``` ## Sitemap See the full [sitemap](https://practiceexcelonline.com/sitemap.md) for all pages.