How to create a search box in Excel 2022

Table of Contents Show

How to create a filtering search box for your Excel data
1. Convert your data range into a table
2. Add filtering controls
3. Test the filtered search box

In this step-by-step overview, learn how to add an advanced search box to your Excel workbook. You can insert a search box with no knowledge of VBA or coding. Imagine you have a cookie customer list, and you want to find the chocolate chip cookies, you can insert a search box to find that. We’ll run through how to make the search box return exact matches and more advanced scenarios like partial match search, multiple column match and highlighting the search term using conditional formatting in the results. This is a fully fledged search box. To follow along, you can access the spreadsheet in the description below.

If you want to quickly calculate how much revenue your service-based business made this month, you can easily whip out your smartphone calculator. But if you need to calculate, sort, and filter a lot of data, spreadsheets are your best bet. Filtering is specifically useful for narrowing down a large data set into manageable data points you can use to answer important questions about customers, employees, products, and more.

One of the most widely used spreadsheet solutions is Microsoft Excel. If you want to filter data in this program, you could use a basic filter that allows you to show and hide data. But you can do so much more with a filtering search box — which will show you data based on what you type.

Below we review the steps to create a filtering search box for your Excel data.

Here’s an overview of the main steps you need to take to create your search box:

Convert your data range into a table.
Add filtering controls.
Test the filtered search.

The instructions below are based on Excel for Microsoft 365, so the dialog and button names you see may vary.

1. Convert your data range into a table

First, turn your raw rows of data into a table object by following these steps:

Click anywhere in your data range.
Click the Insert tab and then click Table.
Confirm the range and check (or uncheck) the checkbox, depending on whether your table has headers.
Change the name of the table in the Table Design tab to something meaningful. (We used the name “MyTable” for the purpose of this walkthrough.)

2. Add filtering controls

After you create a table from your data, here’s how to create your filtering search box to filter based on data in the first column:

Ensure you have at least six empty rows available for the controls. You can place your filtering controls above your table (as we did) or in another worksheet entirely.
Save the spreadsheet as a macro-enabled workbook.
Click the Developer tab.
Click Insert and then click Text Box. Draw the text box in a cell above your table.
Right-click the text box and then click Properties.
Enter the cell number where you drew the box in the LinkedCell field, and then close the window. (We used “A3” for this walkthrough.)
Double-click the text box to open Visual Basic module. In the blank space between the “Private Sub” and “End Sub” strings, enter the macro text below — be sure to replace any references to “MyTable” and “A3” with your table name and text box cell, respectively.

‘Filter Name field in MyTable Table. Dim strFilter As String strFilter = “*” & [A3] & “*” Debug.Print strFilter ActiveSheet.ListObjects(“MyTable”).Range.AutoFilter _ Field:=1, _ Criteria1:=strFilter, _

Operator:=xlFilterValues

Save the module and close the window.
Click Insert and then click Command Button. Draw the button to the right of the text box.
Right-click the button and then click Properties.
Enter “Clear” in the Caption field, and then close the window.
Double-click the Clear button to open its module and then enter the below macro text in the blank space.

‘Clear MyTable custom filter.
[A3] = “”

Save the module and then close the window.
Deselect Design Mode by clicking it. This will enable you to use the controls you’ve created.

Now that you’ve created your controls, it’s time to test their functionality:

Enter any text in the search box. The table’s rows should filter as you type.
To ensure the filter operates correctly, enter the full word(s) in the search box for the record you want to find.
Clear the search box by clicking the Clear button.

If you experience any issues with your filtering search button, review the above steps to see if you missed anything. A common mistake is incorrectly entering the macro text for the text box and command button. Ensure each text string is an exact match for your chosen table name and text box cell.

This article is originally published on Jan 07, 2021, and updated on Dec 08, 2021.

The search box feature is a function on excel that is used to search anything on the workbook. This feature plays a huge role in the excel world as the user does not have to search a given word line after line. Apart from using the search function, one can create a search box that can serve the same function as excel's search function.

This article will explain how to create your search box using the following super easy steps.

1. Open a new or existing spreadsheet

Double click the Excel app icon, which resembles a green with a white "X" on it. And then, open either a new document or an existing document on your device to which you want to add the search box.

2. Click the conditional formatting button

On the Page ribbon, click on the home tab. Within the Home section, locate and click on the conditional formatting button. This button has a drop-down setting that has various features.

3. Click on the New Rule Button

On the drop-down setting, click the new rule button to open the formatting dialogue box.

4. In the dialogue box

After clicking the new rule button, a formatting dialogue box is displayed.

Firstly, on this dialogue box, select the type of rule. In our case, click on the "use a formula to determine which cells to format" option.

This is so because we will come up with a formula responsible for creating our search box. Then, enter the search box formula into the Format values where this is a true box.

The formula should indicate where the search box will be located on your workbook page. That is, you should indicate the column and row index in the formula. The formula entered is not case sensitive, and therefore any capitalization can be used.

You need to customize this formula=ISNUMBER(SEARCH($B$2,A5)) with your own values

Secondly, click the format button. This button is located on the rightmost side immediately after the box which has the search box formula. This opens the format cells dialogue box. On this dialogue box, specify the color, font, style, and the searched value border. Then, click "ok" to save changes.

Finally, click the "ok" button on the New Formatting Rule dialogue box. And by doing so, your search box will be ready for use.

When using the created search box, type the word you're searching within the search box and then press the Enter key. All matching words in the specified range are searched out, and they are highlighted immediately using the selected color and font.