---
title: "FILTER function in Excel: syntax and examples | ExcelSavvy"
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://excelsavvy.com/functions/filter"
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://excelsavvy.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 | ExcelSavvy"
  "twitter:image": "https://excelsavvy.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://excelsavvy.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(

## **How to use FILTER**

FILTER pulls out the rows you care about and leaves the rest behind. Think of a sales list on a clipboard where you cross out everything except one region: FILTER writes the remaining rows up as a new list.

You give it the range to return and a test for each row. `**=FILTER(C2:C6,A2:A6="South")**` returns the sales from the rows where the region is South. The test needs one answer per row, so both ranges must have the same number of rows. To get whole rows back, use the whole table as the first argument: `**=FILTER(A2:C6,A2:A6="South")**`. Combine conditions by multiplying for AND and adding for OR: `**=FILTER(A2:C6,(A2:A6="South")*(B2:B6="Bags"))**`.

The result spills into the cells below and to the right, and it updates when the data changes. If you only need a total, SUMIFS is simpler. For a single match, use XLOOKUP, and to remove duplicates from a list, use UNIQUE.

## **Syntax**

```
FILTER(array, include, [if_empty])
```

<dl><dt>**array **</dt>
<dd>The range to filter.</dd>
<dt>**include **</dt>
<dd>A condition per row, such as A2:A6="South".</dd>
<dt>**if_empty **(optional)</dt>
<dd>What to return when no rows match, instead of #CALC!. Excel only: Google Sheets reads extra arguments as more conditions.</dd></dl>

## **Examples**

E1`=FILTER(<sup>1</sup>C2:C6,<sup>2</sup>A2: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 <sup>1</sup>the sales in C2:C6 on the rows where <sup>2</sup>the 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 |  | |

## **Common FILTER mistakes**

- The list does not appear because other cells are in the way

  FILTER needs free cells below and to the right of the formula for its result. Clear whatever is in those cells and the list appears.
- FILTER returns an error about mismatched sizes

  The test range and the returned range have a different number of rows, such as A2:C6 together with A2:A5. Make both ranges cover the same rows.
- AND and OR do not work inside FILTER

  AND returns a single TRUE or FALSE for the whole range, not one answer per row. Multiply the tests for AND and add them for OR: `**=FILTER(A2:C6,(A2:A6="South")+(A2:A6="North"))**`.
- No rows match even though the text looks identical

  The cells probably have extra spaces, or the numbers are stored as text. Clean the values inside the test: `**=FILTER(C2:C6,TRIM(A2:A6)="South")**`.

## **Practice FILTER**

- [**1****Keep the rows for one region with FILTER**→](https://excelsavvy.com/practice/filter-function/filter-one-condition)
- [**2****FILTER with two conditions**→](https://excelsavvy.com/practice/filter-function/filter-two-conditions)
- [**3****A message when FILTER finds nothing**→](https://excelsavvy.com/practice/filter-function/filter-if-empty)
- [**4****A list that depends on the first choice**→](https://excelsavvy.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://excelsavvy.com/learn/google-sheets-vs-excel)
- [**How to create a drop-down list in Excel, and make it update itself**](https://excelsavvy.com/learn/drop-down-list-excel)
- [**How to merge cells in Excel, and what to use instead**](https://excelsavvy.com/learn/merge-cells-excel)

## **Questions**

<details>

<summary>

### Does FILTER work the same in Google Sheets?

</summary>

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&gt;1000),"None").

</details>

<details>

<summary>

### Why does FILTER give #SPILL!?

</summary>

Something is in the cells where the result needs to go. Clear the cells below and to the right of the formula and the list appears.

</details>

<details>

<summary>

### Which versions of Excel have FILTER?

</summary>

FILTER is in Excel 365, Excel 2021 and later, and Excel for the web. Excel 2019 and older do not have it.

</details>

<details>

<summary>

### How do I sort the result of FILTER?

</summary>

Put SORT around it, as in =SORT(FILTER(A2:C6,A2:A6="South"),3,-1), which sorts by the third column, largest first. In Google Sheets the last argument is TRUE or FALSE instead of 1 or -1.

</details>

<details>

<summary>

### Can FILTER return more than one match?

</summary>

Yes, that is the big difference from VLOOKUP, which stops at the first match. FILTER returns every row that meets the condition.

</details>

## **Related functions**

- [**UNIQUE**](https://excelsavvy.com/functions/unique)
- [**XLOOKUP**](https://excelsavvy.com/functions/xlookup)
- [**SUMIFS**](https://excelsavvy.com/functions/sumifs)

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/functions/filter#webpage","dateModified":"2026-11-16","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://excelsavvy.com/functions/filter","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/functions/filter#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/functions/filter#/schema/question/1"},{"@id":"https://excelsavvy.com/functions/filter#/schema/question/2"},{"@id":"https://excelsavvy.com/functions/filter#/schema/question/3"},{"@id":"https://excelsavvy.com/functions/filter#/schema/question/4"},{"@id":"https://excelsavvy.com/functions/filter#/schema/question/5"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/functions/filter"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/functions/filter#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://excelsavvy.com/functions","position":2},{"@type":"ListItem","name":"FILTER function in Excel: syntax and examples","position":3}]},{"@id":"https://excelsavvy.com/functions/filter#article","dateModified":"2026-11-16","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://excelsavvy.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://excelsavvy.com/#identity"},"image":{"@id":"https://excelsavvy.com/#/schema/image/1"},"isPartOf":{"@id":"https://excelsavvy.com/functions/filter#webpage"},"mainEntityOfPage":{"@id":"https://excelsavvy.com/functions/filter#webpage"},"publisher":{"@id":"https://excelsavvy.com/#identity"}},{"@id":"https://excelsavvy.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://excelsavvy.com/functions/filter#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"Why does FILTER give #SPILL!?","acceptedAnswer":{"@type":"Answer","text":"Something is in the cells where the result needs to go. Clear the cells below and to the right of the formula and the list appears."}},{"@id":"https://excelsavvy.com/functions/filter#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"Which versions of Excel have FILTER?","acceptedAnswer":{"@type":"Answer","text":"FILTER is in Excel 365, Excel 2021 and later, and Excel for the web. Excel 2019 and older do not have it."}},{"@id":"https://excelsavvy.com/functions/filter#/schema/question/4","@type":"Question","inLanguage":"en-US","name":"How do I sort the result of FILTER?","acceptedAnswer":{"@type":"Answer","text":"Put SORT around it, as in =SORT(FILTER(A2:C6,A2:A6=\"South\"),3,-1), which sorts by the third column, largest first. In Google Sheets the last argument is TRUE or FALSE instead of 1 or -1."}},{"@id":"https://excelsavvy.com/functions/filter#/schema/question/5","@type":"Question","inLanguage":"en-US","name":"Can FILTER return more than one match?","acceptedAnswer":{"@type":"Answer","text":"Yes, that is the big difference from VLOOKUP, which stops at the first match. FILTER returns every row that meets the condition."}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"},{"@id":"https://excelsavvy.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://excelsavvy.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://excelsavvy.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://excelsavvy.com/sitemap.md) for all pages.
