Filter as You Type Search Box

These tutorials will enable you to create a search box in Excel that filters a table as you type.  Each letter that you type in the search box will automatically be applied to the filter.

 

LIVE ONLINE TRAINING COURSES WITH A LIVE TUTOR - AVAILABLE WORLDWIDE

Private or public courses available:
ONLINE EXCEL TRAINING (LIVE TUTOR): See Our Training Dates - Excel Fundamentals, Intermediate, Advanced and VBA Courses. 

ONLINE MS ACCESS TRAINING (LIVE TUTOR): See Our Training Dates
ONLINE MS PROJECT TRAINING (LIVE TUTOR): See Our Training Dates
ONLINE POWERPOINT TRAINING (LIVE TUTOR): See Our Training Dates


VIDEO TRAINING - LEARN AT YOUR OWN PACE

DEEP DIVE INTO LOOKUP FUNCTIONS - XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, IFERROR, ISNA, IFNA, LEFT, RIGHT, MID, FIND, SEARCH, LEN, SUBSTITUTE, REPLACE, TRIM, CLEAN & INDIRECTClick here to enrol

DEEP DIVE INTO DATE FUNCTIONS - TODAY, DATEDIF, WORKDAY, NETWORKDAYS, EOMONTH, EDATE, DATE, DATEVALUE, DAY, MONTH, YEAR, WEEKNUM, ISOWEEKNUM, WEEKDAY & YEARFRAC.
Click here to enrol

THE MS ACCESS FOR BEGINNERS' COURSE - Click here to enrol


FREE STUFF
70+ MUST KNOW EXCEL SHORTCUT KEYS: Download the pdf
UNDERSTAND & FIX EXCEL ERRORS: Download the pdf 

 

 

The first tutorial uses the FILTER function which is available in Excel 365.  If you don’t have Excel 365, the second tutorial will show you how to achieve the same result using Visual Basic for Applications (VBA).

Both tutorials are in video format with the relevant formulas and code documented with them.

Using the FILTER function


Formula used in video:

=FILTER(SalesReps,LEFT(SalesReps[Sales Rep],LEN(E1))=E1,"No names found")

Download the featured file here.

For more information on the FILTER function and the new functions available in Excel 365 in general, please see our New Dynamic Array functions page.

Using VBA


VBA code featured in the video:

Unfortunately I can’t upload the macro enabled file in WordPress.

Private Sub optBeginsWith_Click()
Call txtSearchBox_Change
End Sub

Private Sub optContains_Click()
Call txtSearchBox_Change
End Sub

Private Sub txtSearchBox_Change()
If optBeginsWith Then
ListObjects("SalesRepsVBA").Range.AutoFilter Field:=1, Criteria1:=Range("B1") & "*"
Else
ListObjects("SalesRepsVBA").Range.AutoFilter Field:=1, Criteria1:="*" & Range("B1") & "*"
End If
End Sub

Posted by Blue Pecan Computer Training