Did you know that Excel’s Advanced Filter tool includes an option to reference a custom criteria range? After ignoring that option for years, I finally decided to play around with it a bit — and it’s actually pretty incredible.

In this tip I’ll show you how to use the Advanced Filter criteria range to define complex combinations of filtering rules that would be nearly impossible to replicate otherwise.

COMMON USE CASES:

• Applying custom or complex filtering rules that would be impossible with other tools
• Displaying the criteria range to provide transparency into the filters that have been applied