How to autofilter rows based on cell value in Excel?
In Excel, the standard Filter function provides a quick way to filter data based on criteria you select manually. However, in some workflows, you might want your data to refresh automatically as soon as you input a value or condition in a specific cell. For example, if you want the dataset to be instantly filtered based on a keyword or parameter you enter in another cell, this goes beyond native Excel filtering capabilities. This kind of dynamic or “auto-filter” can help streamline your data review process, reduce time spent on manual filtering, and increase accuracy by tying filter conditions directly to cell values. If you're looking to implement such an auto-filtering mechanism based on cell input, several practical solutions are available to achieve this result.
Auto filter rows based on cell value with an Excel formula solution
Auto filter rows based on the cell value you entered with VBA code
Auto filter rows based on cell value with an Excel formula solution
If you prefer a non-macro approach or need to avoid using VBA due to security settings or collaboration concerns, you can create an "autofilter" effect using a combination of formulas and filtering features built into Excel. This solution works well for Microsoft 365, Microsoft 2019, and later versions that support dynamic array functions such as FILTER. It is especially suited for sharing with colleagues who may not have macros enabled, or for lightweight templates where install-free automation is necessary.
1. Select a blank cell where you want your filtered results to begin (for example, place your cursor in cell G2).
=FILTER(A2:C20, (A2:A20=E1) * (B2:B20=E2), "No match")2. After entering the formula in G2, press Enter. The filtered dataset will appear in a spill range starting from cell G2. As you change the values in E1 or E2, the filtered list refreshes instantly.
This formula example assumes your main data is in A2:C20 and the filter criteria are entered in E1 (matching column A) and E2 (matching column B). If you want to filter by just one column, you can simplify the logical condition accordingly. The "No match" message appears if no row matches the entered criteria.
Tip: The FILTER function is available in Microsoft 365 and some versions of Microsoft 19+. For older Excel versions, dynamic filtering via formulas is less straightforward, and you may want to explore helper columns combined with traditional AutoFilter or advanced filter features. Check your version to ensure compatibility.
When using this solution, the results occupy a new area on your worksheet, which helps preserve your raw data. However, the original dataset remains unaffected—filters apply only to the displayed results, not the source data display status.
Potential errors: If you see a #NAME? or #SPILL! error, check that you’re using a compatible Excel version and that there are no merged cells in the result range. Also, avoid placing other data in the formula spill area to prevent blockages.
Auto filter rows based on cell value you entered with VBA code
Suppose you are working with a dataset and want to filter the records automatically based on criteria you type into specific cells. For example, when you fill in your desired conditions in cell E1 and E2, the data on the worksheet gets filtered automatically to match those values, as illustrated below:
To automate filtering in this manner, you can set up a straightforward VBA solution. This method triggers every time you update the value in your designated filter cells. It is especially useful for dashboards, interactive reports, or templates in which users expect dynamic filtering from central parameter cells.
1. Go to the worksheet where you'd like to automate filtering rows based on inputs entered in cells.
2. Right-click on the sheet tab at the bottom of the Excel window and pick View Code from the context menu. In the Microsoft Visual Basic for Applications window that appears, paste the following VBA code into the large blank area (commonly referred to as the code window or worksheet module), as shown here:
VBA code: Auto filter data according to entered cell value
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
If Target.Address = Range("E2").Address Then
Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
End If
End Sub
Note: In this VBA code, A1:C20 refers to your data range to apply the filter. E2 is the cell where you type the filter value (your target criteria), and E1:E2 are the cells used as the filter conditions. You can modify these ranges as needed—make sure they accurately reflect where your data and filter criteria are located for your specific worksheet structure. Also, it's best to avoid merged cells and ensure your data range contains headers if needed for accurate filtering.
3. Now, enter or change your filter conditions in cell E1 and/or E2. Once you press Enter, the VBA code runs automatically and filters the designated data range to only show rows that match your input criteria.
If the filter doesn’t seem to work immediately after entering a value, ensure macro functionality is enabled in your workbook, and check that the ranges in the VBA code properly match your current worksheet layout. In multi-user scenarios, remind others that macros must be enabled for this feature to function properly.
This VBA method is particularly advantageous for dynamic parameter-driven dashboards, interactive templates for data entry, or any scenario in which manual re-application of filter criteria would be inefficient. However, do note that users may encounter issues if they disable macros, and VBA-based solutions are specific to the file in which the code is added.
If your scenario requires frequent criteria changes by multiple users who may have varying Excel security settings, or if you plan to share your file widely, you may want to consider alternative formula-based solutions or Excel add-ins.
Demo: Auto filter rows based on cell value you entered with VBA code
Filter data by multiple criteria or other specific condition, such as by text length, by case sensitive, etc. When you need to perform more advanced filtering operations—such as combining several filter criteria, filtering by the length of text, sensitivity to text case, or filtering based on specific periods—Excel’s built-in filters may fall short. In such scenarios, Kutools for Excel's Super Filter feature provides a practical way to manage complex filter requirements easily and efficiently. This feature helps you:
Kutools for Excel: Equipped with over 300 practical Excel add-ins, fully functional free trial for 30 days. Download and free trial Now! |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in