How to Filter an Excel List Without the FILTER Function in Office 2010
Question details
The user needs to dynamically separate objects marked as Keep and Remove into different lists without using the unavailable FILTER function.
- Product
- Microsoft Excel 2010
- Device & OS
- not provided
- Scenario
- Attempting to create dynamic filtered lists using formulas in an older version of Excel.
- Observed behavior
- The FILTER function is unavailable in Excel 2010, and modern formula alternatives result in #VALUE! or #NAME? errors.
Ensure your data is organized in clean columns with clear status labels like Keep or Remove. If you are using named ranges (like 'objects' and 'state'), verify they are defined correctly in the Name Manager before entering the complex array formula.
Use an Array Formula with INDEX and SMALL
Create a dynamic filtered list in legacy Excel versions by combining INDEX, SMALL, IF, and ROWS functions to replicate the behavior of the modern FILTER function.
Since the FILTER function was introduced in Microsoft 365, older versions like Excel 2010 require a workaround using an array formula. This formula evaluates the entire range, identifies the row numbers that meet your criteria, and packs them into a continuous list.
Select the first cell of your destination range where you want the 'Keep' list to start. Type the formula: =IFERROR(INDEX(objects,SMALL(IF(state="Keep",ROW(objects)-MIN(ROW(objects))+1),ROWS($A$1:A1))),"")
Instead of pressing Enter, press Ctrl + Shift + Enter. Excel will automatically enclose the formula in curly braces {} to indicate it is an array formula.
Click the small square at the bottom-right corner of the cell (fill handle) and drag it down to populate the rest of the list. The IFERROR function ensures blank cells appear when no more matches are found.
Use the Standard AutoFilter Feature
If you do not strictly require a dynamic formula-based list, you can use Excel's built-in AutoFilter to hide unwanted rows in place.
Effortlessly Filter Data with Modern Functions in WPS Office
WPS Spreadsheet provides robust support for modern dynamic array functions, including the FILTER function. You can easily create dynamic lists without relying on complex, legacy Ctrl+Shift+Enter array formulas.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing Excel file.
- 2. Enter the FILTER function: Select an empty cell where you want the new list and type: =FILTER(objects, state="Keep")
- 3. Press Enter to spill results: Press Enter. The results will dynamically spill into the adjacent cells automatically, creating your separate list instantly.
Frequently Asked Questions
Why do I get a #NAME? error when trying to use the FILTER function in Excel 2010?
The FILTER function is a dynamic array function introduced in Microsoft 365 and Excel 2021. Older versions like Excel 2010 do not recognize the function name, which causes the #NAME? error.
What does Ctrl+Shift+Enter do in older Excel versions?
It instructs Excel to evaluate the formula as an array formula. This allows functions like IF to evaluate multiple cells at once (an array) instead of just a single cell, which is necessary for the INDEX and SMALL workaround to function correctly.
How do I filter out the 'Remove' items instead of 'Keep'?
Simply modify the logical condition inside your array formula. Change the text from state="Keep" to state="Remove". The rest of the array formula remains exactly the same.
Will the INDEX/SMALL array formula slow down my Excel workbook?
Yes, if used on very large datasets (thousands of rows), complex array formulas can significantly slow down Excel's calculation speed. In those cases, using AutoFilter, VBA, or upgrading to software that supports the modern FILTER function is recommended.
