Excel Dynamic Arrays: FILTER for Optional and Required Fields

/in /by

The new FILTER function is fantastic, but I wondered how can I make an optional field?

Sometimes we do searches and want to look at items between $10 and $100 for brand X.

We also want to look at everything between $10 and $100 regardless of the brand.

That’s what I wanted to replicate with the FILTER function: an optional filter. The straight-forward FILTER function forces us to filter for something whether we want to, or not.

My solution involves using an IF statement and smart use of the # nomenclature. Check it out!

#DynamicArrayFunctions #OptionalFilter

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2