How to add headers to a FILTER result in Excel
Formula (Excel and Google Sheets)
=VSTACK(A1:C1,FILTER(A2:C10,B2:B10=F1))Stack the header row on top with VSTACK: =VSTACK(A1:C1,FILTER(A2:C10,B2:B10=F1)).
- Result on the example
- a 5-row list that spills from E3 in
E3 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E3. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER returns only data rows. VSTACK puts the header row from A1:C1 on top of them, so the result is a complete table from one formula. Because the header comes from the source, renaming a column there renames it in the result too.
=VSTACK(A1:C1,FILTER(A2:C10,B2:B10=F1))Result{5 rows × 3 cols} "Item", "Category", "In stock", "Copy paper", …
A1:C11 row × 3 cols: "Item", "Category", "In stock"A range: 3 cells in one row.
A2:C109 rows × 3 cols: "Copy paper", "Paper", 42, "Stapler", …A range: 9 rows × 3 columns.
B2:B109 rows × 1 col: "Paper", "Desk", "Paper", "Printing", …A range: 9 cells in one column.
F1"Paper"The value in F1.
B2:B10=F1{9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
FILTER(A2:C10,B2:B10=F1){4 rows × 3 cols} "Copy paper", "Paper", 42, "Sticky notes", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:C109 rows × 3 cols: "Copy paper", "Paper", 42, "Stapler", … - condition1
B2:B10=F1{9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …
- range
VSTACK(A1:C1,FILTER(A2:C10,B2:B10=F1)){5 rows × 3 cols} "Item", "Category", "In stock", "Copy paper", …VSTACK: Stacks ranges on top of each other.- array1
A1:C11 row × 3 cols: "Item", "Category", "In stock" - array2
FILTER(A2:C10,B2:B10=F1){4 rows × 3 cols} "Copy paper", "Paper", 42, "Sticky notes", …
- array1
In Google Sheets
The same formula works in Google Sheets: =VSTACK(A1:C1,FILTER(A2:C10,B2:B10=F1)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 5 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =VSTACK({"Item","Stock"},FILTER(CHOOSECOLS(A2:C10,1,3),B2:B10=F1)) | spills 5 × 2Different result | Type your own headers in an array constant when the result has different columns. Two columns, headed Item and Stock. |
| =QUERY(A1:C10,"select * where B = '"&F1&"'",1)Google Sheets | spills 5 × 3 | In Google Sheets, QUERY with 1 header row returns the header with the results. |
Common mistakes
- The header must have as many columns as the FILTER result. A shorter header gets padded with #N/A.
- Without a fallback, no matches makes the whole stack an error. Give FILTER a third argument:
FILTER(A2:C10,B2:B10=F1,"None").
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.
Add headers to a FILTER result
E3, return the header row from A1:C1 followed by every row whose category is the one in F1.Questions
Can I sort the rows and keep the header on top?
Sort inside the VSTACK, not outside it: =VSTACK(A1:C1,SORT(FILTER(A2:C10,B2:B10=F1),3,-1)).
How do I add a total row at the bottom?
Stack one more row: =LET(f,FILTER(A2:C10,B2:B10=F1),VSTACK(A1:C1,f,HSTACK("Total","",SUM(CHOOSECOLS(f,3))))).
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.