Data Import & Export

Fix Excel Advanced Filter Returning Only Header Row

WPS Editor Oct 9, 2026 870 views

Question details

The user is attempting to run an Advanced Filter using criteria from another sheet, but the filter fails to display matching records.

Fix Excel Advanced Filter Returning Only the Header Row

Product: Excel
Device & OS: Mac
Scenario: Applying an Advanced Filter using a criteria range located on a different worksheet to extract specific matching records.
Observed behavior: The Advanced Filter returns only the header row, hiding all actual data rows because the criteria contain hidden tab characters.

Before you start

Ensure that the column headers in your criteria range exactly match the headers in your dataset, and check for any accidental leading spaces or invisible characters.

Solution 1Recommended

Use the CLEAN Function to Remove Hidden Characters

Remove non-printable characters like hidden tabs from your criteria range using a helper column, allowing the Advanced Filter to recognize the matches.

Hidden characters, such as tabs or line breaks, often end up in cells when copying data from external sources or other software. Because the filter looks for exact matches, these invisible characters prevent the filter from working correctly.

Create a Helper Column

Insert a new, temporary column next to your existing criteria data on the criteria sheet.

Apply the CLEAN Formula

In the first cell of the helper column, type =CLEAN(A2) (assuming A2 contains your first criteria value) and press Enter. Drag the fill handle down to apply it to the rest of the criteria cells.

Paste as Values

Select and copy the new cleaned values. Right-click the original criteria cells, select 'Paste Special', and choose 'Values' to overwrite the corrupted text.

Re-run the Advanced Filter

Delete the helper column. Go to Data > Advanced Filter, select your List range and your newly cleaned Criteria range, and click OK.

Use the CLEAN Function to Remove Hidden Characters

Combine with TRIM: If the CLEAN function alone doesn't fix it, the issue might be trailing spaces. You can use =TRIM(CLEAN(A2)) to remove both invisible characters and extra spaces at once.

Solution 2

Use Go To Special for Marking Visible Cells

If you are trying to isolate or mark specific rows after applying a partial filter, you can use the Go To Special feature.

Effortlessly Manage and Filter Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing tools, including an intuitive Advanced Filter and robust text-cleaning functions, to seamlessly manage complex datasets without missing records.

1. Open Your Dataset: Launch WPS Office and open your spreadsheet. Ensure your data has clear, unique headers.
2. Prepare Clean Criteria: Set up your criteria range above or beside your main data. Use the built-in =CLEAN() or =TRIM() functions if you suspect hidden characters.
3. Apply Advanced Filter: Navigate to the 'Data' tab on the ribbon and click 'Advanced'. Select your list range and your clean criteria range, then click OK to accurately filter your data.

Fully compatible with Microsoft Excel (.xlsx) formatsBuilt-in text functions like CLEAN and TRIM to easily fix corrupted dataAdvanced Filter supports complex criteria and cross-sheet extractionFree, lightweight, and fast installation on both Windows and Mac

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Advanced Filter hide all rows except the header?

This usually happens when the criteria provided do not exactly match the data in the list. Common culprits include extra spaces, hidden tab characters, or mismatched column headers between the criteria and the source dataset.

How do I remove invisible spaces from my filter criteria?

You can use the =TRIM() function in a helper column to remove unwanted spaces from your text. Copy the resulting text and use 'Paste Special > Values' to replace your original criteria.

Can I use an Advanced Filter to copy results to another sheet?

Yes. To copy filtered results to another sheet, you must start the Advanced Filter command while you are actively on the destination sheet, and then highlight the list range from your source sheet.

Does WPS Spreadsheet support Advanced Filter criteria from another workbook?

Yes, WPS Spreadsheet fully supports referencing criteria or extracting source data from different worksheets or other open workbooks when using the Advanced Filter tool.

Excel & Data Problems