'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Filter Cells Containing #NAME? Error in Excel
Question details
The user is trying to filter a dataset to show only the cells containing the #NAME? error, but selecting the error from the standard AutoFilter checkbox fails to return the expected matching cells.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Filtering a spreadsheet column to isolate and correct #NAME? formula errors.
- Observed behavior
- Selecting the #NAME? value from the filter list does not display the matching cells because Excel interprets the question mark (?) in the error text as a single-character wildcard.
Ensure that your worksheet has filters enabled via the Data tab and that you have not accidentally hidden the rows containing the formula errors manually.
Use the Equals Filter to Find #NAME?
Bypass Excel's wildcard interpretation by using the specific Equals filter criteria.
By default, Excel's AutoFilter treats the question mark (?) in #NAME? as a wildcard character representing any single character. Selecting it directly from the list confuses the filter. Using the custom Equals filter forces Excel to treat the text literally.
Click the AutoFilter drop-down arrow on the column header that contains the formula errors.
Hover your mouse over 'Number Filters' (or 'Text Filters', depending on the column's primary data type) to expand the secondary menu.
Click on 'Equals...' from the sub-menu to open the Custom AutoFilter dialog box.
In the criteria box next to 'equals', type #NAME? exactly as it appears in your spreadsheet.
Click 'OK' to apply the filter. Your worksheet will now successfully display only the rows containing the #NAME? error.
Use a Tilde (~) to Escape the Question Mark Wildcard
Use Excel's escape character to search for a literal question mark directly in the filter search bar.
Try WPS Office for Seamless Spreadsheet Management
If you frequently encounter frustrating quirks with Microsoft Excel's filtering and wildcard rules, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet tool that makes data management and error troubleshooting straightforward and intuitive.
Frequently Asked Questions
Why does Excel treat the question mark as a wildcard?
In Excel, the question mark (?) is a built-in wildcard character used in searches and filters to represent any single unknown character. This is highly useful for partial matches but causes unintended issues when searching for literal question marks, such as those found in the #NAME? error.
What does the #NAME? error mean in Excel?
The #NAME? error occurs when Excel does not recognize text in a formula. It usually happens due to a misspelled function name, missing quotation marks around text strings, or referencing a named range that does not exist in the workbook.
Can I use the tilde (~) to escape wildcards in other Excel features?
Yes, you can use the tilde (~) to escape wildcard characters like asterisks (*) and question marks (?) in standard Find and Replace (Ctrl+F / Ctrl+H) dialogs, as well as in specific formulas like COUNTIF, SUMIF, or VLOOKUP.
How do I clear the filter to see all my data again?
To remove the filter and reveal hidden rows, click the funnel icon with a red 'X' on the column header, or go to the Data tab on the ribbon and click the 'Clear' button located in the Sort & Filter group.
