How to Add a Search Bar in Excel for Your Worksheet

How-to basics · Updated November 20, 2026

To add a live search bar in Excel for Microsoft 365 on Windows, enter a search term in a cell and use a FILTER formula to show matching rows beside your data. If you only need occasional searches, turn on the built-in column filters instead; their dropdowns include a search box.

What You’ll Need

  • An Excel workbook with a list of records arranged in columns, with one header row and no blank rows inside the list.
  • For the live search-box method, Excel for Microsoft 365 or Excel 2021, which includes the FILTER function. The steps below search across four columns; you can adapt the formula if your list has a different layout.
  • A blank area on the worksheet where Excel can display matching rows. FILTER needs room to spill its results into neighboring cells.

Step-by-Step

  1. Arrange your data in a simple list. Put one type of information in each column, such as Product, Category, Supplier, and Notes. Use one header row, for example A1:D1, and place records beneath it in A2:D100. Avoid merged cells and blank rows within the records. The live-search example assumes that column A is filled for every record.
  2. Choose where the search box and results will go. Keep your source list in A:D. Type Search in E1 and leave F1 blank for the search term. Put the result headers in F3:I3, using the same four headings as A1:D1. The results will begin below those headings, so check that F4:I102 and the area beneath are empty. If your data is elsewhere, you can still use this arrangement; you will need to adjust the formula ranges.
  3. Format the search input so it is easy to spot. Select F1 and apply a fill color or border from Home > Fill Color or Home > Borders. You can also add an instruction such as Type to search in E2. These are optional visual cues: F1 is an ordinary worksheet cell, not a special Excel control.
  4. Enter the live-search formula. With records in A2:D100, the search term in F1, and result headings in F3:I3, enter this formula in F4:
    =FILTER(A2:D100,IF($F$1="",A2:A100<>"",ISNUMBER(SEARCH($F$1,A2:A100))+ISNUMBER(SEARCH($F$1,B2:B100))+ISNUMBER(SEARCH($F$1,C2:C100))+ISNUMBER(SEARCH($F$1,D2:D100))),"No matches")
    Press Enter. When F1 is empty, the formula displays rows with a value in column A. When you type a term, it displays rows where that term occurs in any of the four columns. SEARCH is not case-sensitive, so a search for blue also finds Blue.
  5. Test it with a term you know is present. Type a word or part of a word that appears in a record into F1. Excel should return each matching row under the result headers. Try a term that appears in a different column too, such as a supplier name, to confirm that the formula searches across all four columns.
  6. Try a term with no matches, then clear the box. A term with no match should display No matches. Delete the contents of F1 to show the full list again. To remove the search term quickly, click F1 and press Delete on Windows; on Mac, press Delete or Backspace depending on your keyboard.
  7. Adjust the formula if your list is longer or wider. The example stops at row 100 and searches columns A through D. If you have records through row 500, change every ending row in the formula to 500. If you add a fifth field in column E, add another SEARCH test for E2:E500 and expand the returned range from A2:D500 to A2:E500. Keep the ranges the same height.
  8. Keep the formula outside the source list. FILTER returns a changing array of results, so place it in a clear area rather than inside the source data. Do not type into the cells where the results need to spill. If your source grows often, reserve a generous range and update the formula when you exceed its last row.
  9. For occasional searches, use Excel’s built-in filter search instead. Click a cell in the list, then choose Data > Filter. Open the dropdown arrow in a column heading and type in its Search box. Select the matching values and click OK. This filters the existing list rather than creating a separate live-results area. To remove the filter, choose Data > Clear, or open the column dropdown and clear its selection.
  10. Save the workbook and check how it will be used. The FILTER formula recalculates when you change F1, and the workbook can be saved normally. If a recipient uses an Excel version that does not support FILTER, they may see a compatibility error instead of the live results; use the built-in filter method for older versions.

Common Mistakes

Putting the formula inside the source data: The formula needs to return results into adjacent cells. If the output overlaps the source list, the layout can become confusing or Excel can report a spill problem. Keep the search area separate, with enough empty columns and rows for the results.

Leaving something in the spill area: A stray space, old value, or merged cell where results should appear can block the formula. Clear the output area, including cells below and to the right of F4, then check the formula cell again.

Changing only part of the formula’s ranges: Each source range in the formula must cover the same rows. If one range ends at row 100 and another at row 500, Excel can return an error. When extending the list, update the returned range and every search range consistently.

Expecting the formula to search a different column: The example searches A:D. It will not find a term that appears only in column E unless you add a test for that column and expand the result range. Check the actual source columns before editing the formula.

Confusing a filter with a search bar: Data > Filter adds dropdowns to column headers; its search box appears only when a dropdown is open. The F1 method is a persistent input cell that controls a separate results area. Choose based on whether you need quick filtering or a visible, reusable search box.

Tips

Convert the source list to an Excel table by selecting it and choosing Insert > Table, or pressing Ctrl+T on Windows. Tables make headers and records easier to manage, and their built-in filter dropdowns are useful for routine filtering. The formula above uses fixed cell ranges, however, so converting the list to a table does not automatically change those ranges. Expand them when you add records beyond the last included row.

Make the input cell obvious without making it look like a button that performs an action by itself. A clear label, a contrasting fill, and a short instruction such as Enter a product or supplier help other people understand how to use it. You can freeze the source headers with View > Freeze Panes if the list is long, though this does not change the search formula.

SEARCH finds partial text, not just whole-cell matches. For instance, typing bolt can return a row containing Stainless bolt kit. It ignores capitalization, which is convenient for names and descriptions. If you need exact matches, or want the search to be case-sensitive, this example needs a different formula design.

If you want a simple way to limit the results to one field, remove the other SEARCH tests and keep only the test for that column. For example, to search only product names in A2:A100 while still returning all four columns, keep A2:D100 as the FILTER output range and use ISNUMBER(SEARCH($F$1,A2:A100)) as the matching condition. Keep the blank-input behavior if you want the full list to reappear when F1 is empty.

On Mac, the formula and worksheet layout work the same way in Microsoft 365 or Excel 2021. The shortcut to create a table may differ by keyboard and Excel settings; use Insert > Table if Ctrl+T is not the right shortcut on your Mac. Excel for the web also supports FILTER in current Microsoft 365 accounts, though ribbon placement and keyboard shortcuts can differ slightly from the Windows desktop app.

Troubleshooting

You see #SPILL!: Select the formula cell. Excel may outline the cells blocking the returned results. Clear those cells, unmerge any cells in the output area, and make sure the output does not run into another table or worksheet content. The formula must be entered in a normal worksheet range, not as a calculated column inside an Excel table.

You see #NAME? or the formula is not recognized: Your Excel version may not include FILTER. The function is available in Microsoft 365 and Excel 2021 or later. If you have an older version, use Data > Filter and search from a column dropdown, or open Find with Ctrl+F on Windows (Command+F on Mac) for a one-off search.

The formula returns No matches even though you expect a result: Check that the value is within the ranges in the formula and that F1 does not contain an unintended extra space. Search checks all four fields in A:D, but not other columns. Also check for errors in the source cells, since errors in a searched range can interfere with the calculation.

Some records disappear when F1 is blank: The blank-input condition uses A2:A100<>"", so it includes only rows where column A has a value. Make sure every real record has a value in column A and that the formula’s row limit includes all your records. If column A can legitimately be empty, use a column that is always populated as the test column and update that part of the formula.

The built-in dropdown has no Search box: Confirm that filtering is enabled with Data > Filter and that you opened a column’s dropdown arrow, rather than just selecting the header. The dropdown searches values in that column only. For a persistent input cell that checks several fields, use the FILTER method instead.

The results do not update after adding new rows: The example only checks through row 100. Change all of its ending row numbers to include the new records. If you edit the formula, check that every range begins and ends on the same rows, then press Enter.

Frequently Asked Questions

Can I add a search bar without using a formula?
Yes. Choose Data > Filter, open a column’s dropdown, and use its Search box. This is quick and does not require a formula, but the search box is inside the dropdown and filters that column’s values rather than showing a separate live-results list.

Does the live search find partial words?
Yes. The formula uses SEARCH, so typing part of a name or description can return a match. It is case-insensitive and checks the four source columns included in the example.

Can I search more than one column?
Yes. The example already checks columns A, B, C, and D. To include another column, add a matching SEARCH test for it to the formula’s condition and expand the returned range to include that column.

Will this work in older versions of Excel?
The live formula requires FILTER, which is available in Microsoft 365 and Excel 2021 or later. For older Excel versions, use the built-in Data > Filter dropdown search or Ctrl+F on Windows; on Mac, use Command+F for Find.

Can I use the search box on a protected worksheet?
Only if the worksheet’s protection settings allow the user to select and edit the input cell. Before protecting the sheet, unlock F1 through Format Cells > Protection, then choose Review > Protect Sheet and allow selection of unlocked cells. Protecting a sheet does not make the results formula itself editable.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.