How to Put a Search Bar in an Excel Column and Filter

Formulas & functions · Updated November 20, 2026

To search a column quickly, select a cell in your data and choose Data > Filter; open that column’s header dropdown, type in its Search box, and select the matching values. For a search field that stays visible on the sheet and updates a results list as you type, use the FILTER formula described below. The first method is quickest; the second is better when you want a dedicated search box.

What You’ll Need

  • An Excel workbook with a list arranged in columns, with one header row and no merged cells in the data.
  • For the built-in method, Excel for Microsoft 365 on Windows, Excel for Mac, or Excel for the web. The commands are similar across these versions.
  • For a permanent search field, Excel for Microsoft 365 or Excel 2021, which support the FILTER function. The formula method also needs empty cells where the results can spill.

Step-by-Step

  1. Check and organize the list. Put each type of information in its own column, such as Name, Department, and City. Use one descriptive header row, followed by one record per row. Remove merged cells from the data area and avoid completely blank rows or columns inside the list; gaps can make Excel treat part of the range as a separate block. If you have a title above the list, that is fine as long as the header row remains clear.
  2. Turn on the column filters. Click any cell in the list, then select Data > Filter. Excel adds a dropdown arrow to each header cell. If Excel has selected the wrong range, undo or turn Filter off, select the full list including its headers, and select Data > Filter again. You can also make the range a table using Insert > Table, confirm the range, and check My table has headers. Tables include filter dropdowns automatically and usually expand when you add rows.
  3. Open the dropdown for the column you want to search. For example, to look up a department, click the arrow beside the Department header. The search box belongs to that column’s filter menu; it is not a separate text box sitting in the worksheet column. Each header has its own search and selection list, so choose the dropdown for the field you need.
  4. Enter a search term. Click the Search box in the dropdown and type all or part of a value. For example, typing sales can help locate entries such as Sales or Sales Operations. The menu narrows its list of available values as you type. Search is generally case-insensitive, so typing sales will also find values written as Sales.
  5. Apply the matching values. Review the filtered checklist, leave the values you want selected, and click OK. Excel hides rows that do not match the selected values; it does not delete them. Look for the filter icon in the column header as a reminder that a filter is active. To search another field as well, open that column’s dropdown and repeat the search. Filters on separate columns work together, so rows must meet the active conditions.
  6. Clear the filter when you are finished. Open the same header dropdown and choose Clear Filter From followed by the column name. To remove all active filters, use Data > Sort & Filter > Clear where that command is shown on the ribbon. The rows reappear, because filtering only changes which rows are displayed. Selecting Data > Filter again turns the dropdown arrows off; it is not necessary just to clear a search.
  7. Make a visible search field instead, if needed. Use this option when you want a search cell on the worksheet rather than opening a header menu each time. It creates a separate results list and requires Excel 2021 or Microsoft 365. For the example, put headers in A1:C1, records in A2:C100, and the values you want to search in column B. Put a prompt such as Search department in D2 and type the search term in E2. Put copies of the three headers in E4:G4, leaving the area below them empty for results.
  8. Enter the live results formula. With the layout just described—complete records in A2:C100, searchable department values in B2:B100, and the search text in E2—enter this formula in E5: =FILTER(A2:C100,IF(E2="",A2:A100<>"",ISNUMBER(SEARCH(E2,B2:B100))),"No matches"). It returns complete rows where the department contains the search text, without regard to capitalization. If E2 is empty, it returns rows whose first-column cell is not blank. For example, if B2 is Sales, B3 is Marketing, and B4 is Sales Operations, entering sales in E2 returns rows 2 and 4 from A:C, in that order. Excel recalculates the results when you change E2. Keep the cells under and beside E5 clear; the results need room to spill.
  9. Format and use the search area. Give D2 a clear label and format E2 so it looks like an input cell, for example with a border or fill color. Do not type into the cells occupied by the returned list. To show more records, adjust the formula’s ranges so the source, searchable column, and row counts line up. If the records are in an Excel table, structured references can be used, but the ordinary cell-range example is easier to check and adapt.

Common Mistakes

Expecting a permanent box in the worksheet. Data > Filter adds a search box inside each header’s dropdown menu. It does not insert a visible search bar into the column. Use the FILTER-formula method if a persistent input cell and separate matching-results area are what you mean by a search bar.

Selecting only one column. If the list has several fields, filtering a single isolated column can separate the displayed matches from the rest of each record. Select a cell in the complete data range, or convert the whole range into a table, so Excel filters entire rows together.

Typing into the formula’s results area. A FILTER formula returns a dynamic array. If any cell in the intended spill range contains content, Excel may show a #SPILL! error. Clear the obstructing cells rather than trying to copy the formula down one row at a time.

Using mismatched ranges. In the example, A2:C100 contains the records and B2:B100 supplies one search value per record. If one range ends on row 100 and another ends on a different row, the formula can return an error or incorrect results. Keep the first and last data rows aligned.

Tips

  • Use distinctive headers such as Customer or Order date, rather than leaving a header blank. Clear headers make the correct dropdown easier to identify.
  • For a quick one-off lookup, the built-in dropdown search is usually the simplest choice. It does not require a formula or a separate results area.
  • For a visible search field, add a short instruction beside the input cell, such as “Type part of a department name.” This helps another person understand how to use the sheet.
  • The example formula searches one chosen column but returns all three columns of each matching row. To search a different field, change the B2:B100 reference to that field’s range; keep its row boundaries aligned with A2:C100.
  • Use a table when the list will grow regularly. A table helps keep records together and applies filter controls to new rows. If you use the formula example with a fixed range, expand its row limits when new data goes beyond row 100.

Troubleshooting

The dropdown has no Search box: Check that you opened a filter dropdown on the header row, not a cell’s context menu. If filters are not enabled, click in the list and choose Data > Filter. Make sure the header row was included and that the range was recognized as a list. In a very short list, the search box may be less noticeable, so inspect the top of the dropdown.

Some expected values do not appear: Check the spelling and contents of the column, then clear any other active filters. A filter on another field may be hiding the row even when the searched value matches. Also check for leading or trailing spaces, inconsistent spellings, and blank cells. The dropdown search finds matching values in that column; it does not search every column in the worksheet at once.

The FILTER formula shows “No matches” unexpectedly: Confirm that E2 contains the search text and that the searchable values really are in B2:B100. Check that the references cover the same record rows and that a filter or hidden row is not being mistaken for a formula result. Try a shorter, distinctive part of a value. SEARCH finds text within a value, but it cannot find a term that is not present.

You see #SPILL!: Clear cells in the output area starting at E5, including any spaces or formulas that look blank, and try again. Also make sure the formula is not placed inside an Excel table: dynamic-array results need to spill into neighboring worksheet cells, and a table column does not provide that spill area.

The formula name is not recognized: FILTER is available in Microsoft 365 and Excel 2021 or later, but not in some older perpetual editions. If your version does not support it, use the built-in dropdown search, or open the workbook in a supported version. Excel for Mac and Excel for the web support FILTER in eligible versions too; the ribbon layout can differ slightly, but the formula syntax is the same.

Frequently Asked Questions

Can I put a search bar directly inside a column?
The built-in filter search is in that column’s header dropdown, not inside the cells. To keep a search input visible on the sheet, place a labeled input cell beside the data and use a FILTER formula to display matching rows elsewhere.

Can the built-in search find part of a word?
Yes. Type a portion of a listed value in the dropdown’s Search box, review the narrowed choices, and apply the filter. This is useful when you know only part of a name or category.

Does filtering delete or change any data?
No. A filter hides rows that do not meet the condition; it leaves the underlying records unchanged. Clear the filter to display them again. Take care not to select and delete rows if your goal is only to narrow the view.

Can one search box look through multiple columns?
The built-in dropdown search is specific to the column whose menu you opened. The formula shown here also searches one chosen column. Searching across several fields requires changing the formula’s criteria to combine those fields, so use the single-column setup when that is all you need.

Will the formula search update when I add records?
It updates when a value within its referenced ranges changes. The example ends at row 100, so records added below row 100 are not included until you extend the ranges. A table-based setup can be adapted to grow with the list, but the basic cell-range formula needs its boundaries maintained.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.