Use Excel Advanced Filter with a Table Header
Question details
The user needs Excel Advanced Filter to include the actual table header when filtering data.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying Advanced Filter to a table whose data range does not include its header row.
- Observed behavior
- Advanced Filter may treat the first data row as the header, producing incomplete or unexpected results.
Identify the header row and the full range of data to filter. Create a separate criteria range with a column label that matches the table header and the desired condition beneath it.
Include the header row in the Excel List range
Use this method when Advanced Filter treats the first data row as a header.
Click a cell in the table, then select Data > Advanced in the Sort & Filter group.
In the Advanced Filter dialog, enter the full range including headers in List range. For a table in A5:F25, use $A$5:$F$25 instead of $A$6:$F$25.
In Criteria range, select the separate cells containing a matching column header and the condition below it.
Choose Filter the list, in-place or Copy to another location. If copying, specify a destination in Copy to, then click OK.

Use Advanced Filter with headers in WPS Spreadsheet
Open your workbook in WPS Spreadsheet and include the header row in the source range when applying Advanced Filter.
- 1. Open the workbook: Open WPS Spreadsheet and use File > Open to select your Excel workbook.
- 2. Prepare the criteria: In empty worksheet cells, enter a column header that matches the table and place the filter condition directly beneath it.
- 3. Set the filter ranges: Select Data > Advanced Filter and set the List range to include the header row and data, such as $A$5:$F$25. Select the criteria cells in Criteria range.
- 4. Apply the filter: Choose whether to filter in place or copy the results to another location, then click OK.

Frequently Asked Questions
Why does Advanced Filter skip my first data row?
The List range may start below the actual header. In Data > Advanced, change the range so it starts at the header row; for example, use $A$5:$F$25 rather than $A$6:$F$25.
What should I put in the criteria range?
Enter a column label matching the table header in a separate cell, with the condition directly below it. Select both cells in the Advanced Filter dialog's Criteria range box.
Can I copy filtered results to another location?
Yes. In Data > Advanced, select Copy to another location, enter a destination in Copy to, and click OK.
Will a fixed List range include new table rows automatically?
A fixed range such as $A$5:$F$25 does not expand automatically. Update the List range to include any new rows before applying Advanced Filter again.




