Excel FILTER Function for Bank Transactions

TL;DR FILTER returns every row of a range that meets a condition, and the results update automatically when the data changes. For bank transactions, the patterns you'll use most are: one condition (=FILTER(Tx, Tx[Category]="Software")), several conditions that must all be true (multiply them with *), conditions where any can be true (add them with +), text that contains a word (ISNUMBER(SEARCH("amazon", Tx[Description]))), and date ranges. Wrap the result in SORT to order it, in SUM or ROWS to total or count it, and always give FILTER a third argument such as "No matches" so an empty result doesn't show an error. FILTER is available in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web; Google Sheets has its own FILTER with slightly different syntax.

Excel's AutoFilter, the little drop-down arrows at the top of each column, is fine for a quick look. But it changes the view of your data rather than creating a result you can reuse, and it doesn't update by itself. When you want a live list of "all Amazon purchases over $50 this quarter", or a sheet that shows every transaction matching whatever you type into a search box, the FILTER function is the better tool.

This guide shows how to use FILTER on bank statement data: setting up the data, the basic syntax, the condition patterns you'll actually need, sorting and totalling results, a worked example with a search box, common errors, and the differences in Google Sheets.

Start with clean data in a table

FILTER works best on data that's already tidy: one row per transaction, consistent columns, real dates and real numbers. A layout like this is ideal:

If you're starting from PDF statements, convert them first. SheetStatement turns statement PDFs into one row per transaction with a signed amount and checks each statement's opening and closing balance. The free plan covers 3 pages a month; Starter ($9), Pro ($19) and Business ($39) monthly plans handle more.

Then make the data an Excel table: click inside it and press Ctrl+T (Cmd+T on a Mac). Give it a short name, such as Tx, in the Table Design tab. From then on you can refer to columns as Tx[Date], Tx[Amount] and so on, and anything you add to the table is picked up automatically by formulas.

Two quick checks before you start:

• Dates are real dates. If some dates are text, conditions on dates will silently miss them. See our guide to fixing bank statement dates in Excel.
• Amounts are numbers. If SUM(Tx[Amount]) returns zero or a suspiciously small figure, some amounts are text. Our guide to cleaning bank statement data covers the fixes.

FILTER vs AutoFilter vs Advanced Filter

Excel has three ways to filter, and each suits a different job.

AutoFilter (Data > Filter, or Ctrl+Shift+L) adds drop-down arrows to your headers. It hides rows that don't match, in place. It's quick for a one-off look, but the result isn't a separate list, it doesn't update when new data arrives unless you reapply it, and it's easy to forget a filter is on and draw the wrong conclusion from a total.

Advanced Filter can copy matching rows to another location using a criteria range. It works in older versions of Excel, but it's a one-time action: when the data changes, you run it again.

The FILTER function creates a separate, live result that updates automatically. It leaves your source data untouched, can sit on its own sheet, and can feed other formulas. The trade-off is that you need a version of Excel that supports dynamic arrays.

For bank data, a sensible split is: AutoFilter for quick checks while you're cleaning or categorising, and FILTER for anything you'll look at again, such as review lists, supplier lookups and tax pulls.

The basic syntax

=FILTER(array, include, [if_empty])

• array is what you want returned, usually the whole table (Tx) or a few columns.
• include is a condition that produces TRUE or FALSE for each row.
• if_empty (optional) is what to show when nothing matches.

Type the formula in one cell on an empty part of a sheet. The results "spill" into the cells below and to the right. You don't copy the formula down.

A simple example, all transactions categorised as Software:

=FILTER(Tx, Tx[Category]="Software", "No matches")

The result is a live list. Change a category on the data sheet and the filtered list changes with it.

Condition patterns for bank data

Money in or money out

=FILTER(Tx, Tx[Amount]<0, "No matches")

Returns every payment out, assuming money out is negative. Use >0 for deposits.

Amount thresholds

All payments of more than $500:

=FILTER(Tx, Tx[Amount]<=-500, "No matches")

With negative outflows, "more than $500 out" means less than or equal to −500. If that's confusing, use the absolute value:

=FILTER(Tx, ABS(Tx[Amount])>=500, "No matches")

Several conditions, all true (AND)

Multiply the conditions. Each is TRUE (1) or FALSE (0), so the product is 1 only when all are true:

=FILTER(Tx, (Tx[Category]="Travel") * (Tx[Amount]<-100), "No matches")

Travel transactions over $100.

Several conditions, any true (OR)

Add the conditions. The sum is greater than zero if any is true:

=FILTER(Tx, (Tx[Category]="Travel") + (Tx[Category]="Meals"), "No matches")

Date ranges

Put the start and end dates in cells, say H1 and H2, so you can change them without editing the formula:

=FILTER(Tx, (Tx[Date]>=H1) * (Tx[Date]<=H2), "No matches")

For a whole month, you can also compare the month and year:

=FILTER(Tx, TEXT(Tx[Date],"yyyy-mm")="2026-09", "No matches")

Text that contains a word

Bank descriptions rarely match exactly. SEARCH finds text anywhere in a cell, ignoring case, and returns a number if found or an error if not. ISNUMBER turns that into TRUE or FALSE:

=FILTER(Tx, ISNUMBER(SEARCH("amazon", Tx[Description])), "No matches")

Combine with other conditions in the usual way:

=FILTER(Tx, ISNUMBER(SEARCH("amazon", Tx[Description])) * (Tx[Amount]<-50), "No matches")

Use FIND instead of SEARCH if you need a case-sensitive match.

Text that doesn't contain a word

=FILTER(Tx, NOT(ISNUMBER(SEARCH("transfer", Tx[Description]))), "No matches")

Handy for excluding internal transfers from a spending list.

Blank categories

Find everything you haven't categorised yet:

=FILTER(Tx, Tx[Category]="", "All categorised")

This is one of the most useful formulas in a bookkeeping workbook: a live to-do list that shrinks as you work.

Returning only some columns

You don't always want every column. Two ways to choose:

Filter a narrower range. If Date, Description and Amount are adjacent, filter just those:

=FILTER(Tx[[Date]:[Amount]], Tx[Category]="Software", "No matches")

Use CHOOSECOLS (available in current Microsoft 365 versions) to pick columns in any order:

=CHOOSECOLS(FILTER(Tx, Tx[Category]="Software", "No matches"), 1, 3, 2)

That returns Date, Amount and Description, in that order.

Sorting the results

Wrap FILTER in SORT. The second argument is the column number within the result, the third is 1 for ascending or −1 for descending:

=SORT(FILTER(Tx, Tx[Amount]<0, "No matches"), 3, 1)

That lists payments out sorted by amount, largest outflow first (because the most negative number comes first in ascending order).

To show only the top ten, wrap the whole thing in TAKE (current Microsoft 365):

=TAKE(SORT(FILTER(Tx, Tx[Amount]<0), 3, 1), 10)

Totalling and counting filtered results

Because FILTER returns an array, you can feed it straight into other functions.

Total of all Software spending:

=SUM(FILTER(Tx[Amount], Tx[Category]="Software", 0))

Note the 0 as the if_empty value, so the sum is zero rather than an error when there are no matches.

Number of overdraft fees:

=ROWS(FILTER(Tx, ISNUMBER(SEARCH("overdraft", Tx[Description]))))

If there are no matches, FILTER without an if_empty argument returns an error, so for counting, wrap it: =IFERROR(ROWS(FILTER(...)), 0).

For simple totals by a single category or month, SUMIFS is still shorter and faster; our guide to SUMIFS by month for bank transactions covers it. FILTER comes into its own when the condition is more complex, like text matching combined with amounts and dates.

A worked example: a transaction search sheet

Suppose you have a year of business checking transactions in a table called Tx. You want a sheet where you type a word, choose a date range and a minimum amount, and see every matching transaction plus a total. Figures below are invented for illustration.

Inputs on a sheet called Search:

The formula in A7:

=LET(
  words, B1,
  hit, IF(words="", TRUE, ISNUMBER(SEARCH(words, Tx[Description]))),
  inRange, (Tx[Date]>=B2) * (Tx[Date]<=B3),
  bigEnough, ABS(Tx[Amount])>=B4,
  SORT(FILTER(Tx, hit * inRange * bigEnough, "No matches"), 1, 1)
)

LET names each part of the condition, which makes the formula much easier to read and change. The IF(words="", TRUE, …) part means an empty search box shows everything in the date range.

The result spills from A7:

…and so on through September.

A total and a count above the results, in B5 and B6:

=SUM(IFERROR(CHOOSECOLS(A7#, 3), 0))
=IFERROR(ROWS(A7#), 0)

A7# refers to the whole spilled result, however many rows it has. If the search returns "No matches", the total is zero.

Now you can answer questions like "how much did we spend with this supplier this year?" or "show me every payment over $1,000 in Q2" in a few seconds, without touching the data.

Building a month-end review sheet

One of the best uses of FILTER is a single sheet you open at month end to review the month's bank activity. Put the month you're reviewing in a cell, say B1, as the first day of the month, and build each block from it.

Large transactions, sorted by size:

=SORT(FILTER(Tx, (Tx[Date]>=B1) * (Tx[Date]<=EOMONTH(B1,0)) * (ABS(Tx[Amount])>=1000), "None"), 3, 1)

Uncategorised transactions for the month:

=FILTER(Tx, (Tx[Date]>=B1) * (Tx[Date]<=EOMONTH(B1,0)) * (Tx[Category]=""), "All categorised")

Fees and charges, found by keyword:

=FILTER(Tx, (Tx[Date]>=B1) * (Tx[Date]<=EOMONTH(B1,0)) * (ISNUMBER(SEARCH("fee", Tx[Description])) + ISNUMBER(SEARCH("charge", Tx[Description]))), "None")

Note the brackets: the two SEARCH tests are added (OR) and then multiplied by the date conditions (AND).

New payees: descriptions this month that never appeared before. This one needs a helper column in the table, FirstSeen, with =MINIFS([Date], [Description], [@Description]). Then:

=FILTER(Tx, (Tx[FirstSeen]>=B1) * (Tx[FirstSeen]<=EOMONTH(B1,0)), "None")

New payees are worth a glance every month: they're where unexpected subscriptions, errors and occasionally fraud show up first.

Change B1 to the next month and every block updates. Combined with a balance check against the statement, this gives you a repeatable review that takes a few minutes. Our month-end close checklist shows where it fits in the wider routine.

Common errors

#SPILL! The result needs empty cells to spill into, and something is in the way. Clear the cells below and to the right of the formula. A spilled formula also can't be placed inside an Excel table; put it on a normal range.

#CALC! Nothing matched and you didn't supply an if_empty argument. Add one, such as "No matches".

#VALUE! Usually the include argument is a different size from the array, for example filtering Tx[[Date]:[Amount]] with a condition built from a column in a different table. Make sure the condition has one value per row of the array.

Dates missing from the result. Some dates are text. Fix them in the source data.

Text matches too much. SEARCH("pay", …) also matches "PayPal", "payroll" and "repayment". Use a more specific word, or combine with other conditions.

Slow workbook. Many FILTER formulas over very large tables can slow recalculation. Point them at a table rather than whole columns, and consider a pivot table or Power Query for heavy summaries. Our guide to pivot tables for bank statements covers that approach.

Tips for reliable FILTER formulas

A few habits keep FILTER formulas accurate as your workbook grows:

• Keep inputs in cells, not inside formulas. Dates, thresholds and search words typed into labelled cells are easier to change and much harder to get wrong than values buried in a long formula.
• Use LET for anything with more than two conditions. Naming each condition makes the formula readable months later, when you've forgotten why it was built that way.
• Always handle the empty case. Give every FILTER an if_empty value, and wrap totals and counts so they return zero rather than an error.
• Point at tables, not whole columns. Tx[Amount] grows with the data and keeps calculation fast; C:C makes Excel check a million rows.
• Leave room to spill. Put each FILTER at the top of an empty area, with nothing below it, so a longer result next month doesn't collide with something else.
• Check one result by hand. When you first build a filter, compare its total with a quick SUMIFS or an AutoFilter view of the same rows. If they agree, you can trust the formula from then on.

FILTER in Google Sheets

Google Sheets has a FILTER function too, with two differences:

• AND conditions are separate arguments: =FILTER(A2:E, D2:D="Travel", C2:C<-100). You can still multiply conditions inside one argument if you prefer.
• There's no if_empty argument. Wrap the formula in IFERROR instead: =IFERROR(FILTER(A2:E, D2:D="Travel"), "No matches").

Text matching works the same way with SEARCH or REGEXMATCH, which is very handy for bank descriptions: =FILTER(A2:E, REGEXMATCH(LOWER(B2:B), "amazon|amzn")). Google Sheets also has a QUERY function that can filter, group and total in one formula; see our guide to Google Sheets QUERY for bank transactions.

Where FILTER fits in a bookkeeping workbook

A few uses that pay off quickly:

• An "uncategorised" list that shows every transaction still missing a category.
• A transfers check that lists everything containing "transfer" so you can make sure each one is tagged correctly.
• A supplier lookup to answer "what did we pay X this year?"
• A large transactions review for month-end: everything over a threshold, sorted by size.
• A tax-time pull of all transactions in deductible categories for the tax year.

Combine these with categorising formulas from our guide to Excel formulas for categorising transactions, and a workbook built from converted statements becomes a quick, searchable record of your finances.

Get your statements into Excel

Upload bank statement PDFs to SheetStatement, confirm each statement balances, and export to Excel or CSV, ready for FILTER and the rest of your formulas. The free plan includes 3 pages a month.

FAQ

Which versions of Excel have the FILTER function?

FILTER is available in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Older versions such as Excel 2016 and 2019 don't have it; use AutoFilter or Advanced Filter instead.

How do I filter bank transactions by more than one condition?

Multiply conditions for AND, for example (Category="Travel")*(Amount<-100), and add them for OR, for example (Category="Travel")+(Category="Meals").

How do I filter transactions whose description contains a word?

Use ISNUMBER(SEARCH("word", Tx[Description])) as the condition. SEARCH ignores case; use FIND for a case-sensitive match.

Why does my FILTER formula show #CALC!?

Nothing matched and there's no if_empty argument. Add a third argument such as "No matches".

Why does my FILTER formula show #SPILL!?

There's something in the cells where the results need to go, or the formula is inside an Excel table. Clear the space or move the formula.

How do I total the results of a FILTER?

Wrap it in SUM, filtering just the Amount column, and use 0 as the if_empty value: =SUM(FILTER(Tx[Amount], condition, 0)).

Is FILTER better than SUMIFS?

For simple totals by category or month, SUMIFS is shorter. FILTER is better when you want to see the matching rows or use complex conditions like text matching.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39.