Excel - does "advanced filter" work with tables?

Question

I've been trying to use Advanced Filter on an Excel Table but am getting strange results. Here's a simple example. 

After executing the Advanced Filter, it only selects row 6.

What appears to be happening is that although Excel tells me Table 5 is A5:F25, the Advanced Filter interprets Table5 as A6:F25, i.e. it ignores the header row, so it (i.e. Advanced Filter) "Thinks" line 6 is the header.

Can anyone help on this? Or should I just always use a range rather than a table when using Advanced  Filter? 

THanks for your help!

Answer 1

Hi oldhasbeen,

I've been trying to use Advanced Filter on an Excel Table but am getting strange results. Here's a simple example. 

After executing the Advanced Filter, it only selects row 6.

What appears to be happening is that although Excel tells me Table 5 is A5:F25, the Advanced Filter interprets Table5 as A6:F25, i.e. it ignores the header row, so it (i.e. Advanced Filter) "Thinks" line 6 is the header.

Can anyone help on this? Or should I just always use a range rather than a table when using Advanced  Filter? 

Your problem, which is readily reproducible, is due to the fact that the assignment of the Excel table name Table5 to the Advanced Filter, passes the table devoid of its header row, as you have noted, and as may be clearly confirmed from the Advanced Filter entry box in your second screenshot. Therefore, try replacing your List Range $A$6:$F$25 with $A$5:$F$25 and all should be well.

===

Regards,

Norman

Answer 2

Hi oldhasbeen,

As a workaround, consider creating defined names for the Excel tables, inclusive of the header row - in this case say Table5A:

===

Regards,

Norman

Answer 3

Thanks for the reply, but you've rather missed my point.

Your solution is to forget about treating the data as a table, and just treat it as a range. This, however, loses one the great advantages of Excel Tables, i.e. they are dynamic, so that if I add an extra row to the table, Excel "knows" that extra row is part of the table.

My guess is that Advanced Filter simply doesn't work with Excel Tables

Answer 4

Excel 2010/2013/2016 Power Query (aka Get & Transform)

Along with Cell References, VBA macros and Formulas,

Advanced Filter is Old. Has Been superseded by Power Query.

http://www.mediafire.com/file/836i3zywkb63fhn/02_14_11.xlsx

http://www.mediafire.com/file/58op51lcakia358/02_14_11.pdf