How To Do Instant Filtered Search As You Type In A Cell In Excel?

Excel is a strong spreadsheet programme that provides a plethora of functions to assist you in effectively organising and analysing your data. The ability to filter data is one of these capabilities, which allows you to easily identify specific information inside enormous databases. The tutorial below will concentrate on a specific filtering technique known as quick filtered search. Unlike traditional filtering methods that require you to manually enter search criteria and apply filters, instant filtered search filters data as you type in a cell, offering real−time results that match your search term.

Do Instant Filtered Search As You Type In A Cell

Here we will first create a table then insert a active x control text box and finally add the VBA code to box to complete the task. So let us see a simple process to know how you can do instant filtered search as you type in a cell in Excel.

Step 1

Consider an Excel sheet where you have a list of data.

First, select the range of cells, then click on insert, select table, and click OK.

Select cells > Insert > Table > Ok.

Step 2

Then click on table, then click on design, name the table "Name," and click enter.

Table > Design > Name > Enter.

Step 3

Then insert an ActiveX control text box. To insert the textbox, click on developer and "insert," then click on text box and draw.

Developer > Insert > Textbox > Draw.

Step 4

Then right−click on the text box, select properties, enter Linked cell as any cell of table, and close the properties.

Right click > Properties > Linked cell > Close.

Step 5

Then right−click on the box again and select "View Code," and replace the below code into the text box.

Code

Private Sub TextBox1_Change()
Dim xStr, xName As String
Dim xWS As Worksheet
Dim xRg As Range
   On Error GoTo Err01
   Application.ScreenUpdating = False
   xName = "Name"
   xStr = TextBox1.Text
   Set xWS = ActiveSheet
   Set xRg = xWS.ListObjects(xName).Range
   If xStr <> "" Then
      xRg.AutoFilter field:=1, Criteria1:="*" & xStr & "*", Operator:=xlFilterValues
   Else
      xRg.AutoFilter field:=1, Operator:=xlFilterValues
   End If
Err01:
Application.ScreenUpdating = True
End Sub

Step 6

Then click Alt+Q and exit the design mode to complete the task.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can do an instant filtered search as you type in a cell in Excel to highlight a particular set of data.