Excel Dynamic Search Box Tutorial | Find Anything

Excel Dynamic Search Box Tutorial | Find Anything | Multi-Column Search

Рет қаралды 32,963

Күн бұрын

Learn the first steps to optimize any spreadsheet: www.excelpowerup.com/training
Take your Excel spreadsheets to the next level: www.excelpowerup.com/course
Get personalized professional support: www.excelpowerup.com/support
If your data is large and unmanageable, a search box may be just the thing you need to make your spreadsheet more user-friendly! I call these kinds of fun and useful upgrades, “Power Ups”! In this video, I’ll show you step-by-step how to create a magic search box in Excel using the FILTER, ISNUMBER, and SEARCH functions. You’ll also learn about a few key tweaks that can take this feature to the next level.
Website: www.excelpowerup.com
Instagram: / rebekahoster
LinkedIn: / rebekah-oster
00:00 Introduction
00:54 Design Search Box
03:46 Insert ActiveX Text Box
06:00 Create Search Results View
06:50 The Formula
09:19 Power Up 1: Gridlines
10:46 Power Up 2: Multi-Column Search
12:01 Note: Partial Matches
12:26 Power Up 3: Exclude Some Columns

Пікірлер: 165

@mardjes 4 ай бұрын

Hello, I'm from Brazil. This video helped me to study English and Excel. Thanks.

@cruzianfibergeek 4 ай бұрын

I have seen quite a few of these videos on adding search boxes this is the first one that i was able to follow without tuning out. You covered exactly what I need done which is to search a table but return only some of the columns.

@RebekahOster 2 ай бұрын

Wow what a compliment!! Thank you for watching 😀

@user-mb9mo8ku8w 4 ай бұрын

Beautifully presented - precise, clear, concise! Very much appreciate your direct and fast implementation - so much easier than dozens of other videos! And a very useful, simplified method. Will definitely be looking forward to all your posts! Thank You!

@RebekahOster 3 ай бұрын

Thanks so much! Stay tuned 🙂

@nonoobott8602 4 ай бұрын

This is so insightful and you were very explicit. Learned a lot. Thanks so much for sharing.

@RebekahOster 2 ай бұрын

Thank you for your feedback! Have you seen my free training? Excelpowerup.com/training ❤️

@sabinezinkpielage1742 4 ай бұрын

Thank you Rebekah, great example and video 😘

@RebekahOster 2 ай бұрын

You are so welcome! I’d love to hear if you tried it out 😁

@realmanta6706 3 ай бұрын

I have watched several videos on this topic and all presented error in implementation, until this one. Great job! Thank you for sharing.

@RebekahOster 3 ай бұрын

I really appreciate that!

@stephieYT 22 күн бұрын

This was so easy to follow!! You have a great pace and way of explaining!

@RebekahOster 21 күн бұрын

Thank you so much! Have you signed up for my masterclass? www.excelpowerup.com/training 🎉

@deliasantos3353 4 ай бұрын

Rebekah Oster- you are an amazing teacher .. you made this so easy to understand. I just did this for a spreadsheet at work and it came out great.. Thank you, looking forward to your other teachings

@RebekahOster 2 ай бұрын

That's so wonderful!!

@RichardJones73 4 ай бұрын

Nice clear tutorial. I done a similar search like this (but not with a fancy searchbox) and used the column headers in the search page to tell the choosecols function what to use,using Match function to get the column number.

@RebekahOster 3 ай бұрын

Wow that's a really cool approach!! Very dynamic 😀 And it would be easy for other users to change.

@M.Asif66 4 ай бұрын

You made SEARCH filtering with selective columns so easy, thanks for transferring knowledge.

@phandongexcel 4 ай бұрын

I like this video ❤❤❤❤

@RebekahOster 2 ай бұрын

I appreciate it!!

@wichstad Ай бұрын

Adding a lineshift in the formula was helpful, a new trick i didn't know about. Thanks for a great tutorial

@RebekahOster Ай бұрын

I’m so glad it was helpful! My goal is to share the most useful methods to make your spreadsheets shine 🎉. I have a free training here: www.excelpowerup.com/training

@veruscrt 3 ай бұрын

I love your tutorials so much that I've liked and added a follow.

@RebekahOster 3 ай бұрын

Wow that means so much! Thank you!!

@daphnetarango775 4 ай бұрын

Wow. I learned so much watching this. Thank you!

@RebekahOster 2 ай бұрын

Yay! 🎉

@sealhouselimited1285 Ай бұрын

I literally have spent 3 hours looking other search box tutorials and each failed and took forever, I came upon yours and in 14 minutes it was done and dusted perfectly. thank you for that, you are absolutely awesome.

@RebekahOster 21 күн бұрын

Wow great job!! I love finding the easiest and best ways to improve your spreadsheets! For more, check out my masterclass here: www.excelpowerup.com/training 🥳

@sahralsahri7180 9 күн бұрын

رائع جدا برنامج عظيم . Very nice project great .

@GarageCrafter Ай бұрын

Great tutorial. Many thanks.

@gregdiete1817 4 ай бұрын

Easy to follow, thank you and something I can use.

@RebekahOster 4 ай бұрын

That's wonderful!! What kind of spreadsheets are you working with?

@markdavies4255 2 күн бұрын

Thank you so much a great help 😊

@sizable21 4 ай бұрын

Really clear and will definitely use this

@RebekahOster 4 ай бұрын

Thanks so much! What kind of spreadsheet do you have in mind?

@DinoDelight 4 ай бұрын

Wow, you made that look so easy, thanks for the great tutorial

@RebekahOster 2 ай бұрын

I'm so glad! 😀

@muhammadasif4916 2 ай бұрын

Thank you so much, I watched lot of videos about his topic but you are the best best best and easiest to understand ......

@RebekahOster 2 ай бұрын

That is so kind! Thank you very much 🎉

@mukhtarsheriff 21 күн бұрын

I can't thank you enough. You are a great Excel tutor

@hichamhadj9640 4 ай бұрын

Awesome, so much knowledge in one video

@RebekahOster 4 ай бұрын

Glad you liked it!

@mukhtarsheriff 27 күн бұрын

Thank you. We need more of your educative tutorials

@RebekahOster 21 күн бұрын

I’m working on them! I have a free masterclass with great content here: www.excelpowerup.com/training

@karenschoenberg4487 4 ай бұрын

This was excellent - thank you.

@RebekahOster 4 ай бұрын

I appreciate it! Do you have a spreadsheet that could use a search box?

@shoasKp 3 ай бұрын

you know it well and explain simply ;that's what smart teachers do.

@RebekahOster 3 ай бұрын

Wow what a compliment!!

@rexbenemerito1943 3 ай бұрын

It works for me. Thank you Rebekah for this tutorial. Very helpful without VBA coding (I wish I can), I can get the needed results quickly.

@RebekahOster 3 ай бұрын

That's wonderful! Great job!!

@ziggle314 4 ай бұрын

Lots of goodies in this presentation: (1) ActiveX gave you dynamic abilities, (2) Good use of "New Rule" in conditional formatting, (3) filtering using multiple criteria, (4) Nice use of paste while keeping column widths. Thanks. You got a new subscriber. 😁

@RebekahOster 4 ай бұрын

I appreciate it! You sound like an experienced Excel user 😏 Welcome!

@stevegunn2660 2 ай бұрын

Brilliant I have a large database of my cd compilations and this works so well for me many many thanks. Now I have just have to import a version into access.

@RebekahOster 2 ай бұрын

Hello! I'm so glad it works for you! Thanks for watching and commenting 🙂

@DhruvDua88 4 ай бұрын

Very well explained and thanks!

@RebekahOster 4 ай бұрын

I appreciate it! I would love to hear how you would use a search box in your spreadsheet 😀

@andrenalize 28 күн бұрын

I follow step by step and it worked. Thank youu

@RebekahOster 21 күн бұрын

Woot woot! You are so welcome 🤗

@hendryns2419 2 ай бұрын

the best suggested video i got so far.

@RebekahOster 2 ай бұрын

Haha that’s awesome!! Have you seen my free training? www.excelpowerup.com/training 😁

@siva8189 Ай бұрын

Awesome explanation. Thank you :)

@RebekahOster Ай бұрын

You're very welcome! 🎉

@frankrogers2254 Ай бұрын

really enjoyed this video. very informative. question if one of the col contains a number value. is there anyway to get a total value for that row to display at bottom. thanks in advance.

@jayceeharb1305 4 ай бұрын

this was amazing !!! simple, concise, and very useful #new sub

@RebekahOster 2 ай бұрын

So happy that you found it useful!

@thehank5368 Ай бұрын

Nice, thanks for sharing. Would you have any ideas on how to make this with multiple selections (without VBA, i.e. macro's disabled) similar to a slicer with multiple selections. When you use a slicer with multiple selections defined on a long data set/list you easily miss the overview of all your selections...

@nihoniumog5185 4 ай бұрын

thank you. this is helpful

@RebekahOster 2 ай бұрын

That’s great to hear! Thank you for watching 😁

@PaulieVlogs 3 ай бұрын

By far the best solution I found on excel and it worked . Is it possible to populate just one row instead of all of them in the sheet with out VBA?

@RebekahOster 3 ай бұрын

That's so great!! I'm sure there is. I would test out the TAKE function. It lets you choose a specific number of rows or columns to return from an array 😀

@CRUSHRSTL 4 ай бұрын

Amazing!!

@RebekahOster 4 ай бұрын

Thank you so much!!

@satish3777 Ай бұрын

Hi Rebekah, very nice tutorial on filter function! 👍 Just a question- Is the filter function applicable to multiple sheets in a same file?

@Gigi82knits 4 ай бұрын

Amazing. Works like a charm. The only thing that didn't work for me was including more columns in the search, it's telling me I can't have more than two. But that's all I need anyway.

@RebekahOster 4 ай бұрын

I'm glad you were able to get it to work!!

@Luciano_mp 4 ай бұрын

Thank you.

@RebekahOster 3 ай бұрын

Of course! I appreciate your comment 🙂

@markdavies4255 27 күн бұрын

Hi I love your search box I am looking for a way of searching data then have the ability to edit data can you help

@bumpersmith 4 ай бұрын

Thank you. These are Exceptionally good clear explanations. Can a form be created that would use a combo box with this type of multi column fuzzy type search? Also it was nice to see in a normal video instead of a short.

@RebekahOster 4 ай бұрын

I appreciate it! That's a great question. Do you mean you want the results to be displayed in a combo box? If that's the case, you might need to use VBA code to populate the combo box.

@bumpersmith 4 ай бұрын

@@RebekahOster Yes in a combobox on a form that will allow a search just like you show in the video. I looked at all your videos but didn't see anything like this. How about making one to show how?

@RebekahOster 4 ай бұрын

Thanks for the idea!! I'll add it to my list 😀

@kabwechola7141 21 күн бұрын

Great!

@innkyjoe 3 ай бұрын

THANK YOU SO MUCH, I WANT TO ASK HOW CAN WE EDIT, FOR EXAMPLE STUDENT ID ON THE SEARCH WORKSHEET TO TAKE EFFECT ON THE DATA/TABLE WORKSHEET😁

@RebekahOster 3 ай бұрын

I appreciate your comment! That would require a lot more work, and probably VBA code. I'm sorry, it's not a simple thing!

@bertvantol9669 17 күн бұрын

GREAT !!! THANKS .... Can I have your file, so Excel translates the formulas to Dutch format ??

@socheatasan18 2 ай бұрын

Hello Rebekah, thank you so much, this is what I need. For some reason why my search box became bigger, and font became smaller every time I entered. I have no idea why.

@RebekahOster 2 ай бұрын

Hi! Is it possible you are in "Design Mode"? There is a toggle button for Design Mode on the Developer tab.

@alishernizamov5356 2 ай бұрын

Great tutorial for a dynamic search field for excel. Thank you. Question: how would you go about creating a search field with more dynamic functionality where the search is focused on one column and is searching multiple words in the column. The search may not follow the actual order of the words in the field but needs to return the lines with all the words that are being searched. Example: If I type "pipe, pvc" or "pvc, pipe" in the search field, the result should return the result as shown below. Is this achievable? Thanks in advance. Search: pipe, pvc result: PIPE, 30", GRAY, PVC YELLOW, 25", PIPE, PVC PIPE, BASE, RACEWAY (CHANNEL), PVC, OFF-WHITE, 2.38 X 0.73 INCHES X 10 FEET

@AmeliaRDream100 Ай бұрын

Thank you! What if the Name of the school and primary contact email address were on a second tab. How could a search bar on the primary sheet "pull through" second tab details like the email address by entering the name of the school?

@user-rx4se1qi1i 4 ай бұрын

Hi Rebekah, I appreciate your clear explanation in this video. It was very helpful and informative. I have a question for you. How can I search for data in multiple worksheets in the same workbook, if the headers are not in the same order as in one sheet? Is there a way to do this easily? Thank you for your time and assistance.

@RebekahOster 2 ай бұрын

Hello! This sounds like quite a challenge. My first idea is to create an intermediate sheet where you combine all of the data into one table and search from there. If you need help with this, you can schedule a call with me here: www.excelpowerup.com/offers/SJPzr8fY . Thanks for watching!

@christinamaharjan818 Ай бұрын

Thank you..new subscriber here✋

@RebekahOster Ай бұрын

Thanks for subbing! 🎉

@akikhan45 4 ай бұрын

awesome

@akikhan45 4 ай бұрын

thanks you

@RebekahOster 2 ай бұрын

Thanks for the comment 🙂

@dariuszbox9678 4 ай бұрын

Perfect.

@RebekahOster 2 ай бұрын

I’m glad you found it useful!

@julianaramburo2023 2 ай бұрын

Hi, thanks for this video. It's awesome. I tried using the dynamic search, and did exactly what you did, but for some reason the cursor doesn't show in the search box. The search actually works but I cannot see what I'm typing. I can only see what I typed after a click away from the box, anywhere in the sheet. It is weird because the search actually works and I can see the data moving or searching, but I cannot see what I'm typing. Can you help? thanks so much

@RebekahOster 2 ай бұрын

Hi! That stinks that you were having this problem. I did a little searching online and found that it does not seem to be a problem with Excel itself, but is more likely to be related to your graphics card or any external monitor that you may be using. I suggest you try it out on a different computer if you have access to one!

@guardianofthemoon Ай бұрын

What if I want this exact functionality but I am creating a Dashboard, I don't want all that on the screen, I just want a search box that you can type a word in and it pulls up what matches

@fcv1967 Ай бұрын

How do I create a text box where it says Type Here and have the text disappear when you click the text box as they do in various websites? And if left blank, the Type Here would return. Also, Type Here would be a light gray. When you type in a query in the box, it would be default black.

@jamshedpathan2269 Ай бұрын

Rebeka u made things so easy but i need ur help instead of School we can use Apartment and in apartment i want to find a certain room that will be in the row . i m trying to find the solution but failed. plz help.

@hassalmua1691 13 күн бұрын

Excellent presentation but I cant get mine working.

@kimiyam8329 2 ай бұрын

Thanks, if I want only some of the headers in the search, what should I do?

@RebekahOster 2 ай бұрын

Hi there! Do you mean you only want to retrieve some of the columns from your data? In this video, the last example at 12:26 explains how to do this. Let me know if you had something else in mind 🙂

@nehalpatel4345 3 ай бұрын

Hey, Rebekah Thanks for the video. pl suggest how to avoid seeing entire table when there is no value in search bar.

@RebekahOster 2 ай бұрын

Hi! Thanks for your question 🙂 Wrap this around the current formula: IF(B2="","", ) I've discovered that the ISBLANK function doesn't work for this, so you have to use ="". Let me know if it works!

@nehalpatel4345 2 ай бұрын

@@RebekahOster Thanks Rebekah. It is working.

@MathieuHart-x1g Күн бұрын

is there anyway to keep this search box and be able to edit the cells that it returns? Currently its giving me a spill error.

@kenanalper361 26 күн бұрын

I have the issue cannot insert objest for the textbox

@josephpandagaruboy6977 4 ай бұрын

I can't filter in excel 2016 and 2010. No filter function available

@RebekahOster 2 ай бұрын

Bummer! I’m sorry it didn’t work for you 😟

@salvokat Ай бұрын

Was so excited to find this video! Then I tried it and my 2013 version of Excel doesn’t allow FILTER function, and my 365 version of excel doesn’t allow ActiveX control. 🥴

@ryanholmgren8509 23 сағат бұрын

When I try to implement this, I get a #Spill error. Does this only work when all the results are unique? I am trying to search for a quote number for example, and have it return all the lines on that quotation (more than 1).

@Patikan 3 ай бұрын

Thanks so much, Rebekah! I did encounter one bug. The text in my search box continually shrinks every time I click out of it. The only way I've been able to fix that is by toggling design mode and changing the size of the box. Otherwise, the text inside the search box shrinks until it's no longer visible. Is there any way to fix that?

@RebekahOster 2 ай бұрын

Hey! I’m so glad you tried out this tutorial, but I’m sorry that you had an issue! I tried to figure out why that could be happening and I couldn’t replicate it. For more personal help, schedule a call with me here: www.excelpowerup.com/offers/SJPzr8fY Thanks for watching!

@GregCrawford-rp2oc 2 ай бұрын

Hi Rebekah. Can I use this using pivot tables?

@RebekahOster 2 ай бұрын

Hi there! I'm not sure exactly what you mean. Do you want to be able to search the Pivot Table results?

@Vali9812 4 ай бұрын

Hello! How can I search multiple words into " Search bar " ? I need to put more than one word in " Search bar". Thank you for your info. that your shared with us!

@julialewis8794 3 ай бұрын

Try using an asterisk (*) as a wild card value to separate your search words. For instance word1*word1 Caveat to this is that both words would have to be in the same cell and would need to be in the order you placed then in the search

@RebekahOster 2 ай бұрын

This is exactly right! Thank you for sharing ❤️

@komisars 4 ай бұрын

It works only with macro enabled workbooks. There are some problems with that because a lot of companies doesn't allow macros.

@RebekahOster 2 ай бұрын

Have you run into this issue yourself? My understanding is that the elements I used in this search box are not macros; they are form controls. Obviously, no solution works for everyone!

@komisars 2 ай бұрын

I checked the solution. Excel warns that there must be a VBA workbook, but allows saving without. And everything works. So I apologize for the inaccurate comment.

@balewgeta7132 12 күн бұрын

I have one problem how to get filter function🥺🥺

@tosh_kiba 4 ай бұрын

question - how come the cells format are not properly copied / formatted just like the source data?

@RebekahOster 2 ай бұрын

Great question! Formulas do not copy formatting, as a rule. If you don’t mind sharing, why would you like it to do that?

@jontrygvemarkussen8126 4 ай бұрын

Is it possible to search across ex. to different tables and return one result list containg data form both tables as long as the column headers are the same?

@julialewis8794 3 ай бұрын

I think you would need to append the 2 tables using something like power query to combine the 2 tables

@RebekahOster 2 ай бұрын

Yes, power query would work! If you want to go through the trouble. There are formulas that can do that as well.

@RebekahOster 2 ай бұрын

Hi! Wonderful question! I have several ideas of how this might work, but it would take some troubleshooting. If you need some personal help with your spreadsheet, schedule a call with me here: www.excelpowerup.com/offers/SJPzr8fY

@jontrygvemarkussen8126 4 ай бұрын

Here you define which columns to search, but is it possible to search across all columns without defining each one?

@RebekahOster 2 ай бұрын

I’m sure there is a way! It might be a very long formula. If you need my help, please schedule a call with me!

@yb2112 2 ай бұрын

How can i remove all the data from the search result cell from showing up even if there is no search quarry?(the search result cell shows all the data from the database) If that makes sense...

@RebekahOster 2 ай бұрын

Hi! Thanks for your question Wrap this around the current formula: IF(B2=-"", ) I've discovered that the ISBLANK function doesn't work for this, so you have to use Let me know if it works!

@spongebobby188 4 ай бұрын

OK, I used this successfully on a table with lots of text comments, so the search is helpful. BUT, I now cannot view the contents of the cell as it doesn't appear in the formula bar as that only shows the index formula. This means I have to copy the result of the search into a new worksheet to view the contents! Please advise on how to resolve this. 🙏

@RebekahOster 2 ай бұрын

Hi, thanks for your comment! I am having trouble understanding the issue. The formula bar will only show the formula, not the results. Is it a spacing issue? Is your formula bar taking up the whole screen? Let me know!

@spongebobby188 2 ай бұрын

@@RebekahOster thanks for your advice Rebekah. So ordinarily the formula bar shows the contents of a cell, right? So if I had a cell just with a comment...eg. 'a white cat' in a cell...when one clicks on that cell, the formula bar reproduces the words 'a white cat'. But after producing a search bar as per the video...the formula bar now only shows the search string in the formula bar. The reason I need the formula bar showing the cell contents is because sometimes the cells have a lot of text...and I don't want to wrap text in the cells so I can keep the worksheet looking uniform..and so the user can just look at the formula bar for the cells contents. Any ideas? 🙏🏻

@fiveandfour 24 күн бұрын

The unnecessary apostrophe in “No Schools Found” is killing me! Please tell me that gets corrected so I can sleep tonight.😂 I have a use case where this may work….looking forward to trying it. Thank you for posting this idea.

@kochanmyae6684 4 ай бұрын

Thank you Madam.

@RebekahOster 4 ай бұрын

You are welcome! Thanks for being here 👍

@jennibudde8358 3 ай бұрын

sorry i had to give up because i can't get header columns to show after the [

@RebekahOster 3 ай бұрын

Oh no! Make sure your data is formatted as an Excel Table. To check, when you click on the table, a new tab will appear called "Table Design". Make sure the table is named, and that you have the right table name before the [.

@jennibudde8358 3 ай бұрын

Thank you! I'm going to try again. I love your channel. You are a great instructor!@@RebekahOster

@ahmedewis6627 2 ай бұрын

Is this applicable on any excel version

@RebekahOster 2 ай бұрын

Great question! The FILTER function is available on Excel 2019 and later!

@ahmedewis6627 2 ай бұрын

@@RebekahOster yes this is true I tried it on 2021 version

@karamalab 4 ай бұрын

Ok, the problem with me is that the filter doesn't work until I press enter.. it doesn't do anything while I'm typing..any help?

@RebekahOster 2 ай бұрын

Hi! I’m so glad you tried it out. But that stinks that you are having this issue. I tried to figure it out and could not replicate this problem. I suggest you check out the automatic calculation settings for your workbook! I could also help you more personally if you schedule a call with me here: www.excelpowerup.com/offers/SJPzr8fY

@neelalam 4 ай бұрын

Before adding any text to the search box why the full data sheet data is being displayed?

@wakefieldfireplace50 4 ай бұрын

I would like to know how to remove the whole sheet when nothing is in the search box also

@julialewis8794 3 ай бұрын

Try wrapping the final formula in an if statement so the logical test is I'd the search term is blank, the true result is a blank as well and the false result is the final formula

@RebekahOster 2 ай бұрын

Hi! The suggestion above was right on 🙂 Wrap this around the current formula: IF(B2="","", ) I've discovered that the ISBLANK function doesn't work for this, so you have to use ="". Let me know if it works!

@dariuszbox9678 4 ай бұрын

How to do simple form ?

@RebekahOster 2 ай бұрын

Hi! There could be a lot of ways to make a simple form in Excel. I would need to learn more about your project to recommend just the right strategy for you. For personal help with your spreadsheet, schedule a call with me here: www.excelpowerup.com/offers/SJPzr8fY