FILTER Specific Columns in Excel (Not the Whole Row)

To filter specific columns in Excel, rather than whole rows, you control what goes into FILTER or what comes out of it. FILTER always returns every column of its first argument, so you either give it only the columns you need, pick columns from the result with CHOOSECOLS, or run a second FILTER across the columns. This guide shows all three on one small table, plus how to sort the result and fix common errors.

Why FILTER returns every column#

FILTER has three arguments: the array to return, the include condition that decides which rows to keep, and an optional value for when nothing matches:

=FILTER(array, include, [if_empty])

The condition only chooses rows. The columns come straight from the array. So =FILTER(A2:D8,C2:C8="East") returns all four columns for each East row, even if you only want the names and sales. The FILTER function guide and the article on the Excel FILTER function cover the basics.

All the examples use this staff table:

FILTER needs Excel 2021 or Microsoft 365. The result spills: it fills as many cells below and to the right as it needs.

Filter side-by-side columns: just shrink the array#

If the columns you want are next to each other, give FILTER only those columns. The condition range can be any column, even one you don't return:

=FILTER(A2:B8,C2:C8="East")

This returns the Name and Team for the three East rows: Ana / Sales, Chloe / Sales, Eva / Support. The array and the condition must cover the same rows (2 to 8 here), or FILTER returns #VALUE!.

Filter specific columns with CHOOSECOLS#

When the columns aren't side by side, wrap FILTER in CHOOSECOLS. CHOOSECOLS returns the columns you list by number, counted from the left of the array:

=CHOOSECOLS(FILTER(A2:D8,C2:C8="East"),1,4)

CHOOSECOLS can also reorder columns: =CHOOSECOLS(FILTER(A2:D8,C2:C8="East"),4,1) puts Sales first. Negative numbers count from the right, so -1 is always the last column. CHOOSECOLS needs Microsoft 365 or Excel 2024; it isn't in Excel 2021.

Another Microsoft 365 option is HSTACK, which places arrays side by side: =HSTACK(FILTER(A2:A8,C2:C8="East"),FILTER(D2:D8,C2:C8="East")). It gives the same result, but you repeat the condition once for every column, so CHOOSECOLS is shorter and easier to change.

You don't always need the columns on the sheet. To total the East sales, filter just the Sales column and wrap it in SUM: =SUM(FILTER(D2:D8,C2:C8="East")) returns 12,700.

Filter non-adjacent columns with a nested FILTER#

In Excel 2021, which has FILTER but not CHOOSECOLS, filter the result a second time. FILTER can work across columns too: give it a row of 1s and 0s, one per column, where 1 means keep:

=FILTER(FILTER(A2:D8,C2:C8="East"),{1,0,0,1})

1. The inner FILTER keeps the East rows, all four columns.
2. {1,0,0,1} is an array constant, a list typed straight into the formula. Commas make it a horizontal row, one value per column: keep Name, drop Team, drop Region, keep Sales.
3. The outer FILTER keeps the columns marked 1.

The result is the same two-column table as CHOOSECOLS gives. Two rules: the constant must have exactly as many values as the array has columns (four here), and the columns always come out in their original order. In some regional settings, Excel uses a different separator inside braces, such as a backslash or full stop, instead of the comma.

To choose columns by header name instead of position, compare the header row: =FILTER(FILTER(A2:D8,C2:C8="East"),(A1:D1="Name")+(A1:D1="Sales")). The + acts as OR, so each column whose header is Name or Sales gets a 1. This version keeps working if someone inserts a column inside the table, because it reads the headers instead of trusting the positions.

Sort the result and add headers#

FILTER results can feed straight into SORT. To list the East staff by sales, highest first:

=SORT(CHOOSECOLS(FILTER(A2:D8,C2:C8="East"),1,4),2,-1)

The 2 sorts by the second column of the result (Sales), and -1 means descending. The SORT and FILTER formula guide shows more combinations.

FILTER doesn't return headers. Type them in the row above the formula, or copy them from row 1. If you need rows that meet two conditions, such as East and Sales team, multiply the conditions inside the include argument; see FILTER with multiple criteria.

When you only need one value per match rather than a list, XLOOKUP may be simpler. The comparisons XLOOKUP vs FILTER and XLOOKUP for multiple columns explain when to use each.

Common problems when filtering specific columns#

• #SPILL!: something is in the way of the result. Clear the cells below and to the right of the formula.
• #CALC!: no rows matched. Add the third argument to show a message: =FILTER(A2:B8,C2:C8="South","No matches").
• #VALUE!: a size mismatch. The condition range must have as many rows as the array, and the 1s and 0s must match the number of columns.
• #NAME?: your Excel version doesn't have the function. CHOOSECOLS needs Microsoft 365 or Excel 2024; FILTER needs Excel 2021 or later.
• Wrong columns after inserting a column. CHOOSECOLS and the {1,0,0,1} constant use positions. Use the header-name version above if the table layout changes.

Practise it#

Pulling a few columns from a big table is a common task in data analyst tests. Practise the FILTER function in a live spreadsheet: you type the formula and it checks it instantly. Then try SORT, UNIQUE and other spill formulas in the modern Excel functions lesson, or start with the exercise on this page.