Excel Tutorials · Lesson 69
Ctrl+F cycles through matches one at a time and disappears the moment you close the dialog. A real search bar lives on the sheet, filters or highlights matches as you go, and can be reused by anyone who opens the file. This lesson covers turning on the Developer tab, wiring up the macro, and a no-code alternative for anyone on Excel 365.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
A search bar is a small piece of interface built directly into the sheet: a place to type a term, and a mechanism, usually a macro, that reacts by highlighting, jumping to, or filtering down to the rows that match. It replaces the built-in Ctrl+F dialog, which finds one match at a time and closes the moment you click away from it.
Building one requires the Developer tab, which is not on the ribbon by default because most people never touch macros or form controls. Turning it on takes one trip through Excel's options, and once it is on it stays on for every workbook going forward, not just this one.
From there, a search bar is really two pieces working together: a cell or Form Control text box holding whatever the user types, and a macro, formula, or filter that reacts to it. The macro route (what this lesson covers) works in every version of Excel and can do more, like jumping the screen to the matching row. The formula route, using FILTER, needs Excel 365 or 2021+ but requires no code at all.
The honest framing: a search bar is a convenience layer, not a replacement for good data structure. If a sheet is small, Ctrl+F is often genuinely faster. It earns its place once a sheet is large enough, or shared with people who aren't comfortable with Excel's native find dialog.
Step-by-step, matching the video above.
Go to File > Options > Customize Ribbon, check the Developer box in the right-hand list, and click OK. It is off by default, and every step after this needs it.
On the Developer tab, use Insert > Form Controls, or simply set aside a plain cell, to hold whatever the user types in.
Click Visual Basic on the Developer tab, or press Alt+F11, then right-click the workbook and insert a new module.
Use Range.AutoFilter to filter the table to matching rows, or a Cells.Find loop to jump the screen to the first match and highlight it.
Insert a Form Control button next to the search box, right-click it, choose Assign Macro, and select the macro you wrote.
Wrap the search in an error check so an unmatched term shows a clear message, like "No results found," instead of a runtime error or a blank screen.
Save the file as .xlsm. Saving as a regular .xlsx silently strips the macro out, and the search button stops doing anything.
| Piece | What it holds | Purpose |
|---|---|---|
| B1 | Search cell (user types here) | Input |
| Button | Runs the SearchTable macro | Trigger |
| A3:D200 | The table being searched | Data |
The macro reads B1, applies an AutoFilter to column A using "*" & Range("B1").Value & "*" as the criteria, and the wildcards on each side make it a partial, case-insensitive match. Clearing B1 and re-running the macro removes the filter and shows every row again.
A macro search bar and a formula search bar solve the same problem two different ways.
Works in every version of Excel back to 2007. AutoFilter narrows the visible rows to matches; Cells.Find instead jumps the screen and selects the first matching cell without hiding anything.
=FILTER(A3:D200,ISNUMBER(SEARCH(B1,A3:A200))) spills every matching row live as B1 changes. Requires Excel 365 or 2021+, but needs no Developer tab, no VBA, and survives being copied into any workbook.
SEARCH ignores case and supports wildcards, which is what you want for a search box. FIND is case-sensitive and has no wildcard support, useful only when exact casing matters.
Combine checks with OR: ISNUMBER(SEARCH(B1,A3:A200))+ISNUMBER(SEARCH(B1,B3:B200))>0 inside FILTER matches a term appearing in either column.
A second, one-line macro that runs Range("B1").ClearContents and removes any active AutoFilter resets the view instantly without touching the data underneath.
Everything in this lesson lives behind one setting.
The Developer tab exposes macros, form controls and add-ins, tools most spreadsheet users never touch, so Microsoft keeps it off the ribbon until you turn it on.
File > Options > Customize Ribbon, check the Developer box on the right, click OK. It is a one-time setting per computer, not per workbook.
Insert (for Form Control buttons and text boxes), Macros (to record or run one), and Visual Basic (to write or edit the code by hand).
Recipients see a yellow security bar and must click Enable Content before the search button will do anything. This is a Windows-level protection, not a bug in the file.
| Method | Needs Developer Tab | Works On |
|---|---|---|
| AutoFilter Macro | Yes | Excel 2007 and later |
| FILTER Formula | No | Excel 365, 2021+, Google Sheets |
Neither is strictly better. The macro version can do more (jump the screen, change colors, log searches), while the formula version is simpler to build, easier to share safely, and needs no macro security prompt at all.
Four things that go wrong on the first attempt.
Either the macro wasn't assigned to it (right-click > Assign Macro), or the file was saved as .xlsx and the macro was silently removed.
Missing wildcards. Wrap the criteria in "*" & value & "*" for AutoFilter, or use SEARCH rather than an exact equals comparison in a formula.
Add a check at the top of the macro that exits immediately, or shows every row, when the search cell is blank, instead of letting Find or AutoFilter run on nothing.
AutoFilter stays applied until something explicitly removes it. Build the Clear button early, not as an afterthought, or testing gets confusing fast.
Where a search bar earns its place over Ctrl+F.
Once a sheet runs into thousands of rows, a search box that filters down instantly beats scrolling or repeatedly opening Ctrl+F.
A visible search box and button is more discoverable than a keyboard shortcut for anyone unfamiliar with Excel, especially on a shared team file.
Sheets has no VBA, but the FILTER formula version works identically, since FILTER is native to Sheets and does not depend on Apps Script or macros.
A search bar for finding existing records pairs naturally with a data entry form for adding new ones, so a table can be searched and maintained from the same sheet.
Excel silently strips macros when you save as a regular workbook. If the button stops working after a save, this is the first thing to check.
Without * on each side of the search value, only an exact, whole-cell match is found, which feels broken to anyone typing a partial term.
Running Find or AutoFilter against a blank value either throws an error or filters everything out. Add a check that shows all rows when the box is empty.
A fixed range like A3:A200 stops matching new rows once the table grows past it. Reference an Excel Table instead so the range expands automatically.
Without a Clear button, an active AutoFilter stays applied indefinitely, and anyone reopening the file later sees a partial view with no obvious reason why.
Recipients who don't trust the file, or don't recognize the prompt, will leave macros disabled and the search bar simply won't respond. Say so plainly if you send the file out.
Finding something shouldn't require the Developer tab.
Free 14-day trial. No credit card required.
A text box on the sheet that jumps to, highlights, or filters matching rows as you type or click Search, instead of the pop-up Ctrl+F dialog that only cycles through matches one at a time.
Go to File > Options > Customize Ribbon, check the Developer box in the right-hand list, then click OK. The Developer tab now appears on the ribbon with Insert, Macros and Visual Basic.
The Developer tab is where you insert the Form Control text box and button, and where you open the VBA editor to write the macro that actually performs the search.
Yes. Any workbook containing a macro must be saved as an Excel Macro-Enabled Workbook (.xlsm), or the macro is stripped out when you save as a regular .xlsx.
Yes, in Excel 365 or Excel 2021 and later, a single FILTER formula referencing a search cell shows only the matching rows live, with no macro required.
Wrap each column in its own SEARCH check and combine them with OR inside the FILTER formula, or loop through each column in the macro version.
Not by default. Both the SEARCH function and Excel's Find method ignore case, which is almost always what you want in a search bar. Use FIND instead of SEARCH if you need case sensitivity.
Yes, by default. Wildcards are implied, so typing part of a name or word matches any cell containing that text anywhere within it.
A well-built search bar should show a message like No Results Found instead of an error or a blank screen, handled with an IFERROR wrapper or an On Error line in the macro.
Yes. Assign a second macro to a button that clears the search cell and re-shows all rows, or clears the FILTER formula's input cell.
Yes. Google Sheets has no VBA, but the same FILTER-based, no-code version works identically, since FILTER exists natively in Sheets.
Their macro security settings likely blocked it. Macros are disabled by default on files from outside sources, so recipients need to click Enable Content in the yellow security bar.
Every business starts with a spreadsheet. Updoot is where you scale past it, with data already connected to where it belongs.
Start Your Free Trial →