Build Live Filtered Lists in Excel (FILTER + SORT)

If you’ve ever spent time manually filtering a data table, copying the results, and pasting them somewhere else every single month, this tutorial is for you. The FILTER function eliminates that entire copy-paste routine by creating a dynamic report that updates automatically whenever the source data changes. We’ll also layer in the SORT function so our results come back in exactly the order we want. Three exercises, one powerful reporting setup.

Exercise 1: Basic FILTER with a Single Condition

Consider the following worksheet. We have a source data table named Table1 containing journal entry transactions, with columns for Date, JE #, Dept, Account, Vendor, Amount, and Status. Above the table, there is an Output area where we want our filtered report to land.

The old manual approach involves picking a department, filtering the table, copying the visible rows, pasting them into the output area, and then clearing the filter. Every month. Not a problem for a one-time project, but for a recurring workbook, we want to eliminate those manual steps entirely. That is exactly what FILTER lets us do.

In case we’re new to the FILTER function, it returns a filtered subset of a range based on a condition we supply. The function arguments are:

• array: the range or table to return rows from
• include: a boolean array the same height as array that tells FILTER which rows to keep
• if_empty: the value to return when no rows match the condition (optional)

We could write the following formula into cell B8 to pull back only the rows where the Dept column matches whatever department name we type into cell C7:

=FILTER(Table1,Table1[Dept]=C7)

We hit Enter, and bam:

The results spill automatically into the output area, showing every Ops transaction. Next period, we paste in updated data and this report refreshes on its own. Even better, we can change the department name in cell C7 and the report instantly reflects the new department.

That is how we can replace manual filter-copy-paste with a single formula. Mission accomplished for Exercise 1.

Exercise 2: AND / OR Logic with Multiple Conditions

Now that we have the basics working, it is time to make things more interesting. Real-world reporting often needs multiple conditions. Exercise 2 introduces Dept1, Dept2, and Status input cells so we can filter by combinations of departments and statuses.

The key rule to remember is simple. For AND logic, use the multiplication operator (*). For OR logic, use the addition operator (+). Let’s walk through each scenario.

AND Logic: Both Conditions Must Be True

To filter for rows where Dept equals the value in C11 AND Status equals the value in C12, we wrap both expressions inside the include argument and multiply them together:

=FILTER(Table1,((Table1[Dept]=C11)*(Table1[Status]=C12)))

Hit Enter, and bam:

Only two rows come back because only two transactions satisfy BOTH conditions at the same time. If we change C12 to Posted, the report updates instantly to show Ops Posted transactions instead.

OR Logic: Either Condition Can Be True

To include rows from either the Sales OR the Ops department, we add the two department conditions together instead of multiplying them:

=FILTER(Table1,((Table1[Dept]=C11)+(Table1[Dept]=C10)))

We hit Enter, and bam:

Rows from both departments spill into the output. The addition operator works here because a value of 1 (TRUE) in either position is enough to pass the row through.

Combining AND and OR Logic

We can combine both operators in a single formula. To return rows where Dept is Sales OR Ops AND Status is Open, we group the OR portion in parentheses first, then multiply by the AND condition:

=FILTER(Table1,((Table1[Dept]=C11)+(Table1[Dept]=C10))*(Table1[Status]=C12))

The parentheses control the order of operations, just like in regular math. The OR part evaluates first, and then the AND part narrows the result further.

And since all the conditions point to input cells, changing any of those cells instantly refreshes the report. That is AND and OR logic inside FILTER, and it is EASY to write AND maintain.

Exercise 3: Sorting the Filtered Results with SORT

With our multi-condition filtering complete, we can move on to sorting the output. Exercise 3 shows how to build an open-items report sorted by Amount, both ascending and descending.

First, let’s get a plain FILTER working. We want all rows where Status equals Open. So, we use the following formula:

=FILTER(Table1,Table1[Status]="Open")

We get the open transactions, but they appear in the original data source order. So, how can we sort them by Amount? Well … we just wrap the SORT function around the entire FILTER formula.

Before diving in, a quick look at the SORT function. It returns a sorted version of an array or range. The function arguments are:

• array: the range or array to sort
• sort_index: the column number to sort by, where 1 is the first column of the array (optional)
• sort_order: 1 for ascending (default) or -1 for descending (optional)
• by_col: TRUE to sort by column instead of by row (optional)

Amount is the 6th column in our table, so we pass 6 as the sort_index. Here’s how we can combine them:

=SORT(FILTER(Table1,Table1[Status]="Open"),6)

That gives us the open transactions sorted from smallest to largest amount. To flip it to descending, we just add -1 as the sort_order argument:

=SORT(FILTER(Table1,Table1[Status]="Open"),6,-1)

And that is how we can use FILTER and SORT together to build a fully dynamic, automatically sorted report from a source data table. Mission accomplished!

Summary

So those are three practical ways to use the FILTER function, along with SORT, to replace the manual filter-copy-paste workflow. Exercise 1 showed a single-condition filter driven by an input cell. Exercise 2 layered in AND logic (multiplication operator) and OR logic (addition operator), including combinations of both. Exercise 3 wrapped the whole thing in SORT to control the output order with just a couple of extra arguments. Together, these functions turn a recurring manual task into a one-formula reporting solution that refreshes automatically.

If you have any suggestions, improvements, alternatives, or questions, please share by posting a comment below … thanks!

Sample File

FAQs

Does the FILTER function work in all versions of Excel?

No. FILTER is available in Excel 365 and Excel 2021 and newer. It is not available in Excel 2019, Excel 2016, or earlier versions. If you need similar functionality in an older version, you would need to use a combination of INDEX, MATCH, and helper columns, or use a PivotTable instead.

What happens if no rows match the FILTER condition?

By default, FILTER returns an error error when no rows match. To avoid that, we use the optional third argument, if_empty. For example, =FILTER(Table1, Table1[Dept]=C7, “No results”) will display the text “No results” instead of an error when nothing matches.

Why do we use * for AND logic instead of the AND function?

The AND function returns a single TRUE or FALSE, which does not work for array operations inside FILTER. Multiplying two boolean arrays together produces a new array where a row is 1 (TRUE) only when both conditions are TRUE, which is exactly what FILTER needs to evaluate each row individually.

Why do we use + for OR logic instead of the OR function?

Same reason as above. The OR function does not work on arrays the way FILTER needs. Adding two boolean arrays produces values of 0, 1, or 2. Any non-zero value is treated as TRUE by FILTER, so a row passes through if either condition is satisfied.

Can we filter by more than two conditions?

Absolutely. We just keep chaining conditions using * for AND or + for OR. For example, to require three departments we would write (Table1[Dept]=C10)+(Table1[Dept]=C11)+(Table1[Dept]=C12) inside the include argument. Parentheses help keep the logic organized when mixing AND and OR together.

Does FILTER return just the matching rows, or does it return a spill range?

FILTER returns a spill range, meaning it automatically fills as many rows and columns as the result requires. We only type the formula in one cell and the rest spills out automatically. If the result grows or shrinks (because the source data changes), the spill range adjusts on its own.

What does the sort_index argument in SORT refer to?

The sort_index refers to the column position within the array being sorted, not the column letter in the worksheet. So if FILTER returns all seven columns of Table1 and we want to sort by the sixth column (Amount), we pass 6 as the sort_index regardless of which column letter Amount lives in on the sheet.

Can we sort by multiple columns at once?

Yes. We can pass arrays to the sort_index and sort_order arguments. For example, =SORT(array, {3,6}, {1,-1}) would sort first by the third column ascending, then by the sixth column descending. This gives us a multi-level sort in a single formula.

Can the FILTER formula reference a cell on a different worksheet?

Yes. FILTER works across sheets just like any other Excel formula. We can have the source table on one sheet, the input cells on another, and the output formula on a third. Just reference each range with its sheet name, for example Sheet1!C7 for the criteria cell.

Is there a way to return only specific columns from the filtered results?

Yes. Instead of passing the entire table as the array argument, we can use CHOOSECOLS to select only the columns we want. For example, =FILTER(CHOOSECOLS(Table1,1,2,6), Table1[Dept]=C7) would return only the Date, JE #, and Amount columns for the matching rows.