Dynamic Arrays
Filter horizontally to exclude or keep specific columns (FILTER on headers)
Create a derived table that omits columns with specified header names using a horizontal FILTER mask.
Little-known superpower: FILTER works horizontally across columns too!
A one-row include mask matching the table width selects columns rather than rows. This example retains Rep, Region and Deals and omits headers named Salary or InternalCost.
The source values remain in the workbook. A filtered output is not access control or secure redaction: sharing the source workbook can still expose those values. Use synthetic practice data and share only an appropriately reviewed exported view when needed.
The mask depends on accurate header labels and aligned ranges. New columns with unrecognized sensitive names are retained; inserted or appended columns still require a range and privacy review. Leave an empty destination area for the spilled table.
| Rep (A) | Region (B) | Salary (C - Excluded!) | Deals (D) | InternalCost (E - Excluded!) |
|---|---|---|---|---|
| Header Mask <> Sensitive | TRUE (1) | FALSE (0) | TRUE (1) | FALSE (0) |
| Ana / West / $120k / 8 / $40k | Kept (Col 1–2) | Dropped! | Kept (Becomes Col 3!) | Dropped! |
Exclude multiple confidential columns by header name
Multiplying two header inequality arrays acts as horizontal AND, retaining Rep, Region, and Deals (3 columns total).
=FILTER(A1:E3,(A1:E1<>"Salary")*(A1:E1<>"InternalCost"),"No retained columns")Inspect the 3rd retained column after stripping confidential fields
With Salary removed from Column C, Deals shifts cleanly into output Column 3.
=INDEX(FILTER(A1:E3, (A1:E1 <> "Salary") * (A1:E1 <> "InternalCost")), 2, 3)Try the example
Use the original values or change them to test your understanding.