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:

=INDEX(Table1[Products], MATCH(0, COUNTIF($B$20:B20, Table1[Products])+(SUBTOTAL(3, OFFSET(Table1[Products], MATCH(ROW(Table1[Products]), ROW(Table1[Products]))-1, 0, 1))=0), 0))

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.
Click Ok.

The array formula is instantly refreshed.

Download excel 2007 file *.xlsx

Excel table Filter unique distinct values.xlsx