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.

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") & "*"
ListObjects("SalesRepsVBA").Range.AutoFilter Field:=1, Criteria1:="*" & Range("B1") & "*"
End If
End Sub

Posted by Blue Pecan Computer Training