Others

Use Excel Advanced Filter with a Table Header

Maira MehtabMaira Mehtab Nov 20, 2026 869 views

Question details

The user needs Excel Advanced Filter to include the actual table header when filtering data.

Use Excel Advanced Filter with a Table Header
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.
Before you start

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.

Solution 1Recommended

Include the header row in the Excel List range

Use this method when Advanced Filter treats the first data row as a header.

1
Open Advanced Filter

Click a cell in the table, then select Data > Advanced in the Sort & Filter group.

2
Correct the List range

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.

3
Select the criteria

In Criteria range, select the separate cells containing a matching column header and the condition below it.

4
Apply the filter

Choose Filter the list, in-place or Copy to another location. If copying, specify a destination in Copy to, then click OK.

Include the header row in the Excel List range
Check the range after the table grows: If you enter a fixed cell range, update its last row when new data is added before running Advanced Filter again.
Filter data in WPS Spreadsheet

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. 1. Open the workbook: Open WPS Spreadsheet and use File > Open to select your Excel workbook.
  2. 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. 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. 4. Apply the filter: Choose whether to filter in place or copy the results to another location, then click OK.
Filter a data range using a separate criteria rangeOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.