How to autofilter rows based on cell value in Excel?
Normally, the Filter function in Excel can help us to filter any data as we need, but, sometimes, I would like to auto filter cells based on a manual cell input which means when I enter a criteria in a cell, the data can be filtered automatically at once. Are there any good ideas to deal with this job in Excel?
Supposing, I have the following range of data, now, when I enter the criteria in cell E1 and E2, I want the data will be filtered automatically as below screenshot shown:
1. Go the worksheet that you want to auto filter the date based on cell value you entered.
2. Right click the sheet tab, and select View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window, see screenshot:
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 the above code, A1:C20 is your data range that you want to filter, E2 is the target value that you want to filter based on, and E1:E2 is your criteria cell will be filtered based on. You can change them to your need.
3. Now, when you entering the criteria in cell E1 and E2 and press Enter key, your data will be filtered by the cell values automatically.
Filter data by multiple criteria or other specific condition, such as by text length, by case sensitive
Filter data by multiple criteria or other specific condition, such as by text length, by case sensitive, etc.
Kutools for Excel’s Super Filter feature is a powerful utility, you can apply this feature to finish the following operations:
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 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!