How to create a search box in Excel without Code | Smart filter using formula

📝 Sample Data

✅ Step-by-Step: Create a Search Box in Excel

📁 Step 1: Open the Excel File

Open your Excel sheet that contains your data.

🧩 Step 2: Enable the Developer Tab

If you don’t see the Developer tab:

1. Go to File > Options
2. Click Customize Ribbon
3. Check the box for Developer and click OK

Now you’ll see a Developer tab in the Excel ribbon.

🔹 Step 3: Add a Search Input Cell

Let’s assume:

• You enter your search text in cell D6
• Your data starts from cell B9 (with names in Column B)

🔹 Step 4: Apply Conditional Formatting with Formula

1. Select the data range where you want to apply search highlighting (e.g., B9:F12)
2. Go to Home > Conditional Formatting > New Rule
3. Choose “Use a formula to determine which cells to format”
4. Enter this formula:

=AND(SEARCH($D$6,B9),$D$6<>"")

💡 This formula highlights cells that contain the value typed in D6.

5. Click Format > Fill > Choose a color
6. Click OK to apply the rule.

🔎 How It Works

• SEARCH($D$6, B9) checks if the cell in column B contains the keyword typed in D6.
• AND(... , $D$6<>"") ensures that it only applies if the search cell is not empty.

✨ Result

Now, whenever you type something like "Priya" or "Developer" in cell D6, the matching rows will be highlighted automatically, helping you quickly locate the data!

💡 Extra Tips

• You can expand the formula to highlight entire rows or add filters for more control.
• Make your header bold and freeze it for easier navigation.

Example: