How to return only certain columns with FILTER in Excel
Formula (Excel and Google Sheets)
=CHOOSECOLS(FILTER(A2:E11,D2:D11=H1),3,5)Wrap FILTER in CHOOSECOLS: =CHOOSECOLS(FILTER(A2:E11,D2:D11=H1),3,5) returns just the customer and amount columns of the matching rows.
- Result on the example
- a 3-row list that spills from G3 in
G3 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER returns whole rows of A2:E11. CHOOSECOLS then keeps the columns you name by their position in that result: 3 is the customer and 5 is the amount. The columns don't have to be next to each other, and you can list them in any order.
=CHOOSECOLS(FILTER(A2:E11,D2:D11=H1),3,5)Result{3 rows × 2 cols} "Northwind", 1840, "Fabrikam", 395, …
A2:E1110 rows × 5 cols: "SO-1041", 2/23/2026, "Northwind", "West", …A range: 10 rows × 5 columns.
D2:D1110 rows × 1 col: "West", "East", "West", "North", …A range: 10 cells in one column.
H1"West"The value in H1.
D2:D11=H1{10 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
FILTER(A2:E11,D2:D11=H1){3 rows × 5 cols} "SO-1041", 46076, "Northwind", "West", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:E1110 rows × 5 cols: "SO-1041", 2/23/2026, "Northwind", "West", … - condition1
D2:D11=H1{10 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …
- range
CHOOSECOLS(FILTER(A2:E11,D2:D11=H1),3,5){3 rows × 2 cols} "Northwind", 1840, "Fabrikam", 395, …CHOOSECOLS: The columns you name from an array, in the order you name them.- array
FILTER(A2:E11,D2:D11=H1){3 rows × 5 cols} "SO-1041", 46076, "Northwind", "West", … - col_num1
3 - col_num2
5
- array
In Google Sheets
The same formula works in Google Sheets: =CHOOSECOLS(FILTER(A2:E11,D2:D11=H1),3,5). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 3 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(HSTACK(C2:C11,E2:E11),D2:D11=H1) | spills 3 × 2 | Stack the columns you want first, then filter. Handy when the columns come from different places. |
| =QUERY(A1:E11,"select C, E where D = '"&H1&"'",1)Google Sheets | spills 4 × 2Different result | In Google Sheets, QUERY picks the columns and the rows in one step, and adds the header row. Includes the header row (Customer, Amount) on top. |
Common mistakes
- The numbers in CHOOSECOLS count from the left of the FILTER result, not from column A of the sheet. If the table starts in column B, column B is 1.
- CHOOSECOLS needs Excel 2024 or Microsoft 365. In Excel 2021, filter each column on its own:
=FILTER(C2:C11,D2:D11=H1).
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Return only certain columns with FILTER
G3, return only the customer and the amount (columns C and E) of the orders from the region in H1.Questions
How do I change the order of the columns?
List them in the order you want: =CHOOSECOLS(FILTER(A2:E11,D2:D11=H1),5,3) puts the amount first.
How do I drop one column instead of picking several?
Use negative numbers to count from the right, or DROP: =DROP(FILTER(A2:E11,D2:D11=H1),,1) removes the first column.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.