This blog post demonstrates how to filter unique distinct values from an excel table dynamically. When you change or add a filter to an excel table, the array formula extracts unique distinct values instantly.
Example, to the left is a picture of a table in excel 2007. Column A contains random dates and column B contains random text strings.
Array formula in cell B21:
How to create an array formula
Copy (Ctrl + c) and paste (Ctrl + v) array formula into a cell.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
How to copy array formula
Copy (ctrl + c) cell B21.
Paste (ctrl + v) to cell range B22:B28.
Filter excel table
Click on black arrow near header "Dates".
Deselect year 2011.
The array formula is instantly refreshed.
Download excel 2007 file *.xlsx