Filter transactions in Excel with FILTER and SORT

Many questions about a ledger start with "show me": every bill from one vendor, every utilities charge above a limit, every delivery cost in the middle two weeks of the month. A filter answers them by hiding everything else. This page shows how to do it with a single formula whose result fills the cells below it by itself, and how to sort that result so the largest items come first.

Two ways to filter a ledger

A spreadsheet gives you two kinds of filter. The first is the filter button on a header row: you click a column, tick the values you want, and the rows you don't want are hidden. It changes what you see, not the data, and it is handy for a quick look.

The second is a formula. FILTER reads a range, keeps only the rows where a condition is TRUE, and writes those rows somewhere else on the sheet. The original ledger stays exactly as it was. Because it is a formula, the result updates when the ledger or the condition cell changes.

This lesson practices the formula version. Its shape is short:

=FILTER(array,include)

• array is what you want back, usually the whole ledger: dates, vendors, accounts and amounts.
• include is a test with one TRUE or FALSE answer per row, such as "the vendor equals the name in H1".

The two ranges must cover the same rows. If the ledger is rows 4 to 23, the test must also run from row 4 to row 23, or the formula can't line them up.

What "spill" means

FILTER is a dynamic-array function. You type it in one cell, and its result spills: it fills as many rows and columns as the answer needs. Five matching lines with four columns each fill a block five rows deep and four columns wide, starting at the cell that holds the formula.

Two rules follow. You only write the formula in the top-left cell; the other cells show results but hold no formula. And the block must be empty. If any cell in the way already has something in it, the formula can't spill and shows #SPILL! instead. The fix is to clear the cell that is in the way, not to edit the formula.

The MO-220 objective this covers

These exercises are MO-220 practice for "Prepare a trial balance", under "Organize transactions", which covers sorting and filtering. MO-220 task instructions describe the purpose, for example "show every ledger line for this vendor", rather than naming the function. So the skill is reading a request and recognizing that it calls for a filter, and then a sort. The exercises here are worded the same way. Accounting Exercises is not affiliated with Microsoft or Certiport.

The exercises run in a spreadsheet in your browser, using the same formulas you would write in Microsoft® Excel® workbooks. Each one is checked on the values in the result block, and the checker also confirms that the formula in G4 uses FILTER, so copying the lines by hand won't pass.

Worked example: every line for one vendor

Maple Lane Catering keeps its November 2026 purchases ledger in rows 4 to 23: date in column A, vendor in B, account in C and amount in D. The vendor to pull is typed in H1: Cobalt Freight. Headers for the result are already in G3:J3, and the task is to write one formula in G4 that returns every Cobalt Freight line, in ledger order.

Here is the part of the sheet you start from:

Step 1: decide what comes back

The task wants the date, vendor, account and amount of each line, so the array is the whole ledger, A4:D23. Leave the header row out. The headers are already typed above the result.

Step 2: write the test

The question for each row is "is the vendor Cobalt Freight?" The vendor is in column B and the name is in H1, so the test is B4:B23=H1. It produces twenty TRUE or FALSE answers, one per ledger row. Pointing at H1, instead of typing the name in quotes, means you can change the vendor later and the report follows.

Step 3: put it together in G4

=FILTER(A4:D23,B4:B23=H1)

Press Enter and the result spills into G4:J8:

The five lines come from ledger rows 4, 7, 13, 14 and 23, in the same order they appear in the ledger. Together they come to 5,475.90, which is what Maple Lane spent with Cobalt Freight in November.

One detail about the date column: a spreadsheet stores a date as a serial number and only shows it as a date when the cell is formatted that way. The result cells in this exercise are already formatted as dates. In your own workbook, if the first column of a filter result shows numbers such as 46327, give those cells a date format.

Two conditions at once

The second exercise uses Copperleaf Studio's December ledger, with the same layout. Now the request has two parts: lines for the account in H1 (Utilities Expense) whose amount is at least the minimum in H2 (1,500.00). Both must be true.

Write each condition as its own comparison in brackets and multiply them:

=FILTER(A4:D23,(C4:C23=H1)*(D4:D23>=H2))

Multiplying works because a spreadsheet treats TRUE as 1 and FALSE as 0. A row scores 1 only when both comparisons are TRUE, since 1 × 1 = 1 and anything times 0 is 0. FILTER keeps the rows that score 1. Adding the conditions instead would mean "either one", which is a different question.

Copperleaf has seven Utilities Expense lines. Four are 1,500.00 or more: 3,341.18 and 4,211.52 from Cobalt Freight, then 1,500.00 and 3,915.52 from Delta Office Supply. They spill into G4:J7 and total 12,968.22. The 1,500.00 line sits exactly on the minimum, and it belongs in the result because "at least" includes the limit.

A date range, sorted largest first

The third exercise, Harbor Bakery's January ledger, adds a date range and a sort. The request is the Delivery Expense lines (the account in J1) dated from 1/6/2026 (H1) to 1/15/2026 (H2), both days included, with the largest amount first.

That is three conditions, multiplied together, inside FILTER. Then SORT wraps the whole result:

=SORT(FILTER(A4:D23,(A4:A23>=H1)*(A4:A23<=H2)*(C4:C23=J1)),4,-1)

Read it from the inside out. FILTER returns the matching lines in ledger order. SORT then reorders them. Its second argument, 4, says to sort by the fourth column of the filtered result, which is Amount. Its third, -1, means descending: largest first. A 1 there would mean ascending.

Harbor Bakery has eight Delivery Expense lines in January, and six fall inside the range. Sorted, they spill into G4:J9:

Column I, Delivery Expense on every row, is left out above. The lines on 1/2/2026 and 1/17/2026 are Delivery Expense too, but they fall outside the range. The six lines in the range total 6,981.43.

Common mistakes

Testing the wrong column

Writing C4:C23=H1 compares the vendor name with the Account column. No account is called Cobalt Freight, so nothing matches and the formula has no lines to return. When a filter comes back empty and you're sure the value exists, check which column the test looks at.

Returning only the amounts

=FILTER(D4:D23,B4:B23=H1) keeps the right rows but only one column. The five Cobalt Freight amounts land in G4:G8, and the date, vendor and account are missing. The first argument decides which columns come back, so give it the whole ledger, A4:D23.

Adding conditions when both must hold

With a plus sign, (C4:C23=H1)+(D4:D23>=H2), a row is kept when either test is TRUE. For Copperleaf that returns 14 lines: every Utilities Expense line, including the 92.33 one, plus every other line of 1,500.00 or more. Multiply when the request says "and"; add only when it says "or".

Using > when the request says "at least"

D4:D23>H2 drops Delta Office Supply's 1,500.00 line, leaving three lines that total 11,468.22 instead of four. "At least" and "minimum" both include the limit, so use >=.

Forgetting to sort

Without SORT, Harbor Bakery's six lines are all correct but appear in ledger order, starting with 1,868.92 on 1/6/2026. The request asked for the largest first, so the order is part of the answer. Wrap the filter in SORT(…,4,-1).

Leaving out the last day of the range

Writing A4:A23<H2 for the end date stops at 1/14/2026. That drops Acme Paper Co.'s 2,154.40 line from 1/15/2026, the largest one in the result, and the total falls to 4,827.03. "From H1 to H2, both days included" needs >= at the start and <= at the end.

Checking your work

• Count the lines. The result block should be as deep as the number of matching ledger lines. A quick header filter on the ledger, or a COUNTIFS with the same conditions, gives you a number to compare.
• Compare totals. The sum of the filtered amounts should equal a SUMIFS with the same conditions. For Maple Lane, both give 5,475.90 for Cobalt Freight. The SUMIFS totals by account page covers those functions.
• Look at the edges. Make sure any line exactly on a limit or boundary date is in the result.

Filtered lists feed the next steps of the close. Once you can pull the lines you need, the trial balance in Excel page shows how to turn ledger totals into balances that agree. If a filter shows the same bill twice, the find duplicate transactions page shows how to flag repeats. The free exercise below gives you a new ledger and vendor each time.