Data Import & Export

How to Fix Excel Filter Hiding Rows That Should Match

Steve K Oct 7, 2026 873 views

Question details

The user is experiencing an issue where filtering an Excel table by specific criteria, such as a company name, incorrectly hides rows that actually match the filter.

How to Fix Excel Filter Hiding Rows That Should Match

Product: Excel
Device & OS: not provided
Scenario: Filtering tabular data to view specific records based on text values.
Observed behavior: Rows containing the matching text disappear when the filter is applied, but reappear when the filter is cleared.

Before you start

Before troubleshooting, navigate to the Data tab and click 'Clear' to remove all active filters, ensuring you can see the entire dataset before attempting to clean the values.

Solution 1Recommended

Clean Hidden Characters and Extra Spaces

Use this method to standardize your text data, as invisible spaces or non-printing characters often prevent Excel from recognizing matching text.

Data imported from external systems often contains leading spaces, trailing spaces, or non-printable ASCII characters that are invisible to the naked eye but cause filtering functions to fail.

Insert a helper column

Right-click the column letter next to the data you want to filter (e.g., Company Name) and select 'Insert' to create a new blank column.

Apply the TRIM and CLEAN formula

In the first data cell of the new column, enter the formula =TRIM(CLEAN(A2)) (assuming A2 contains your target text) and press Enter.

Fill the formula down

Double-click the small green square at the bottom-right corner of the cell to drag the fill handle and apply the formula to the rest of the column.

Paste as values

Select the newly cleaned column, press Ctrl+C to copy it, then right-click the original data column and select 'Paste as Values' to permanently replace the problematic text. You can now delete the helper column.

Clean Hidden Characters and Extra Spaces

Formula Explanation: The TRIM function removes leading and trailing spaces, while the CLEAN function eliminates non-printable characters that often corrupt imported data.

Solution 2

Reapply the Filter Range to the Entire Table

Use this step if new rows were recently added to the bottom of your dataset, as they might fall outside the active filter range.

Advanced Spreadsheet Tool

Easily Clean and Filter Data with WPS Spreadsheet

WPS Office provides a powerful, highly compatible Spreadsheet application that makes handling large datasets, cleaning text formats, and applying accurate filters completely effortless.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your messy or corrupted Excel file.
2. Standardize the text: Use the =TRIM(CLEAN()) formula in a helper column to remove any hidden characters imported from other databases.
3. Reapply data filters: Navigate to the Data tab and click 'Filter' to apply precise filtering criteria without losing matching rows.

Fully compatible with Microsoft Excel (.xlsx, .xls) formatsBuilt-in TRIM and CLEAN functions for seamless text formattingIntuitive Data tab with advanced, error-free filtering capabilitiesLightweight, fast, and completely free for everyday office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why do some numbers not show up when I use an Excel filter?

Numbers might be incorrectly formatted as text. Select the column, click the error indicator (yellow warning icon) that appears, and choose 'Convert to Number'. Afterward, clear and reapply your filter.

Can merged cells affect how my filters work?

Yes, merged cells can cause filters to behave unpredictably, often hiding rows that belong to the merged group. It is highly recommended to unmerge your cells and fill the empty rows with the appropriate repeated data for accurate filtering.

How can I quickly clear all active filters in my worksheet?

You can quickly clear all active filters and reveal all hidden rows by navigating to the Data tab and clicking 'Clear' in the Sort & Filter group, or by using the keyboard shortcut Alt + A + C.

Why is the filter missing some new rows I just added?

If you added rows below the existing table without using the official Excel Table feature (Ctrl+T), the filter range does not automatically expand. You must turn the filter off and back on again to include the new rows.

Excel & Data Problems