Others

Why Excel Custom Filters Show Both Text and Number Options

Aamir Naveed Akram Oct 14, 2026 869 views

Question details

The user wants to understand why Excel displays comparison options for both text and numbers in the Custom AutoFilter menu and how to correct it.

Why Excel Custom Filters Show Both Text and Number Options

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering data in a column that contains a mix of numbers, text, or blank cells.
Observed behavior: Excel shows both text and number filtering options simultaneously, and simply changing the cell format to Text does not alter the filter menu behavior.

Before you start

Before attempting to change the filter options, evaluate your dataset to identify if the target column contains hidden blank cells, numbers stored as text, or inconsistent data types.

Solution 1Recommended

Standardize Column Data Types

Since showing mixed options is Excel's intended behavior for mixed data, standardizing your column ensures you only see the relevant Text or Number filters.

Excel is designed to show all available comparison options in the Custom AutoFilter menu when it detects a variety of data types within a single column. To obtain more predictable filtering results, you need to clean the underlying data so that all values share the exact same data type.

Select the target column

Click the column header letter to highlight the entire column containing the mixed data types.

Use Text to Columns

Navigate to the Data tab on the ribbon and click on 'Text to Columns'. Choose 'Delimited', click Next twice, and then select your desired uniform format (Text or General) to standardize the entries.

Clean blank and mixed entries

Find and remove any unintended blank cells or resolve entries that mix letters and numbers, ensuring uniformity across the dataset.

Reapply the AutoFilter

Turn the filter off and back on by clicking the Filter button in the Data tab. Click the column drop-down arrow to confirm that the appropriate Text Filters or Number Filters option now appears exclusively.

Intended Behavior: Formatting the column via the Home tab (e.g., changing it to 'Text') changes how data is displayed, but it does not change the underlying data type recognized by the AutoFilter engine.

Efficient Data Filtering

Organize and Filter Mixed Data Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful and intuitive filtering tools to help you manage mixed datasets effectively. Standardize your columns and apply accurate custom filters with ease to ensure precise data analysis.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your mixed data.
2. Highlight the data range: Select the column or specific range of cells you want to filter.
3. Enable AutoFilter: Navigate to the 'Data' tab and click on the 'AutoFilter' icon.
4. Access custom filters: Click the drop-down arrow in the column header to view available filtering options.
5. Apply text or number filters: Select the specific 'Text Filters' or 'Number Filters' based on your cleaned and standardized data.

Easily identify and filter mixed data types in complex datasetsFully compatible with Microsoft Excel (.xlsx) formatsIntuitive user interface for quick data cleaning and standardizationAdvanced AutoFilter options for efficient workflow management

microsoft office alternative - wps office

Frequently Asked Questions

Does formatting a column as Text fix the mixed filter options in Excel?

No, simply changing the cell format to Text from the formatting menu only changes the display format, not the underlying data value. You must convert the actual data using tools like 'Text to Columns' for the AutoFilter menu to update.

Why do blank cells cause both text and number filters to appear?

Blank cells add an inconsistent state to a column's dataset. Excel's AutoFilter engine detects this lack of uniformity and provides both text and number options to ensure you can filter any data type present in the column.

How can I convert numbers stored as text into actual numbers?

You can use the 'Error Checking' smart tag (the small yellow diamond warning) next to the cell and select 'Convert to Number', or multiply the entire column by 1 using the Paste Special feature.

Office Quick Solutions