FILTER Function in Excel
Quick answer
The FILTER function in Excel returns every row from a range that meets the conditions you set, and the results spill into the cells below. Reach for it when you want a live, formula-driven list, like all invoices from one supplier, that updates the moment your data changes.
=FILTER(array, include, [if_empty])Works in: Excel 365 (Windows and Mac), Excel 2024, Excel 2021, Excel for the web, and the Excel mobile apps. Not available in Excel 2019 or 2016. Google Sheets has FILTER too, with slightly different syntax (see the FAQ).
Syntax
=FILTER(array, include, [if_empty])| Argument | Required? | What it does |
|---|---|---|
array | Yes | The range you want rows returned from, e.g. A2:G11. Include every column you want to see in the results. |
include | Yes | The test that decides which rows come back, e.g. C2:C11="Ridgeline Roasters". It must be the same height as array, and it returns TRUE or FALSE for each row. |
if_empty | No | What to show when nothing matches, e.g. "No invoices". Default: FILTER returns a #CALC! error. |
How FILTER works
Think of FILTER as AutoFilter written as a formula. You point it at your data, give it a test, and it hands back only the rows that pass. The difference is that it never goes stale: change a supplier name or add an invoice inside the range, and the result redraws itself.
The include argument is the heart of it. C2:C11="Ridgeline Roasters" produces a column of TRUE and FALSE values, one per row, and FILTER keeps the TRUE rows. For more than one condition, multiply the tests together for AND logic, or add them for OR logic. Each test goes in its own brackets.
You type the formula in one cell and the results spill down and across into as many cells as they need. Leave that space empty, or you’ll get a #SPILL! error instead of your list.
Examples
These examples use the Invoices sheet from the Bluegum Café workbook, with ten supplier bills from July to October 2025 in A1:G11 (headers in row 1). Results go to the right of the data, from column I, so there’s room for them to spill.
| Invoice No | Date | Supplier | Category | Amount ex GST | GST | Total |
|---|---|---|---|---|---|---|
| INV-1001 | 03/07/2025 | Ridgeline Roasters | Coffee beans | $480.00 | $48.00 | $528.00 |
| INV-1002 | 04/07/2025 | Coastal Dairy Co | Milk | $215.50 | $21.55 | $237.05 |
| INV-1003 | 07/07/2025 | Otway Bakehouse | Bread | $312.40 | $31.24 | $343.64 |
| INV-1004 | 10/07/2025 | Ridgeline Roasters | Coffee beans | $480.00 | $48.00 | $528.00 |
| INV-1005 | 11/07/2025 | Coastal Dairy Co | Milk | $198.20 | $19.82 | $218.02 |
| INV-1006 | 01/08/2025 | Otway Bakehouse | Bread | $298.60 | $29.86 | $328.46 |
| INV-1007 | 15/08/2025 | Ridgeline Roasters | Coffee beans | $520.00 | $52.00 | $572.00 |
| INV-1008 | 12/09/2025 | Coastal Dairy Co | Milk | $224.80 | $22.48 | $247.28 |
| INV-1009 | 06/10/2025 | Ridgeline Roasters | Coffee beans | $520.00 | $52.00 | $572.00 |
| INV-1010 | 14/10/2025 | Otway Bakehouse | Bread | $305.10 | $30.51 | $335.61 |
Basic: List every invoice from one supplier
On the Invoices sheet, type Ridgeline Roasters in J2, then enter the formula in I5.
=FILTER(A2:G11, C2:C11=J2, "No invoices")The result spills into I5:O8 and returns four invoices: INV-1001, INV-1004, INV-1007 and INV-1009. Change J2 to Otway Bakehouse and the list swaps to that supplier’s three bills.
Real-world: Pull the September quarter invoices for your BAS
On the Invoices sheet, type the quarter’s start date 01/07/2025 in J2 and end date 30/09/2025 in J3, then enter the formula in I5.
=FILTER(A2:G11, (B2:B11>=J2)*(B2:B11<=J3), "No invoices this quarter")The result is eight invoices, INV-1001 to INV-1008, spilling into I5:O12. INV-1009 and INV-1010 drop out because they fall in October. Need the GST figure for the BAS? =SUM(FILTER(F2:F11, (B2:B11>=J2)*(B2:B11<=J3))) returns $272.95.
Advanced: Two suppliers, biggest bill first
On the Invoices sheet, enter the formula in I2. It uses OR logic (the + sign) to keep both suppliers, then SORT orders the result by the Total column, largest first.
=SORT(FILTER(A2:G11, (C2:C11="Coastal Dairy Co")+(C2:C11="Otway Bakehouse"), "No invoices"), 7, -1)The result spills into I2:O7 and returns six invoices, starting with INV-1003 at $343.64 and ending with INV-1005 at $218.02. The 7 tells SORT to use the seventh column of the filtered result (Total), and -1 means descending.
Guru Tip
Turn your data into an Excel table before you write the formula. Click anywhere in the invoices, press Ctrl+T, and name it tblInvoices on the Table Design tab. Then write =FILTER(tblInvoices, tblInvoices[Supplier]=J2, "No invoices"). When INV-1011 lands at the bottom of the list, the table grows and FILTER picks it up. A fixed range like A2:G11 will quietly miss it, and I’ve seen that exact gap turn up in a BAS reconciliation.
Common errors
#CALC!: nothing matched
Cause: No rows passed the include test and there’s no if_empty value, so Excel has nothing to return. Fix: Add a third argument, e.g. "No invoices", or "" to show a blank cell.
#SPILL!: something is in the way
Cause: The results need more room than is free. A value, a stray space or a merged cell is sitting in the spill range. Fix: Click the formula cell, check the dashed border Excel draws around the spill area, and clear anything inside it.
#VALUE!: ranges are different sizes
Cause: The include range doesn’t have the same number of rows as array, e.g. A2:G11 with C2:C12. Fix: Make every range start and end on the same rows, or use a table so the sizes always match.
#NAME?: FILTER isn’t available
Cause: The file was opened in Excel 2019, 2016 or earlier, which don’t have FILTER. Fix: Open the file in Excel 365, 2021, 2024 or Excel for the web. In older versions, use AutoFilter or an Advanced Filter instead.
Grab the template: FILTER
Get the Bluegum Café invoice sheet with all three FILTER formulas built in, ready to swap in your own suppliers.