How to Create a Search Bar in Excel (in two minutes)

Подписаться 52 тыс.

Просмотров 46 тыс.

50% 1

In this video, you’ll learn how to insert a search bar into your worksheet so that you can quickly search large data sets for any value. You’ll be able to type anything into the search bar, and all the rows containing the value entered will automatically be returned!
This search bar is easy to create, requires no VBA, and is guaranteed to impress your boss. First, we’ll enter a developer button, link the button, and then use the FILTER, SEARCH, and ISNUMBER functions to filter the data set on rows that contain the value entered in the search bar.
📖RESOURCES
- Download the workbook and follow along: excel-dictionary.com/pages/yo...
🤓SHOP EXCEL DICTIONARY
- COURSES: excel-dictionary.com/collecti...
-GUIDES: excel-dictionary.com/collecti...
-MERCH: excel-dictionary.com/collecti...
-TEMPLATES: excel-dictionary.com/collecti...
📩NEWSLETTER
-Unlock the full potential of Excel and PowerPoint with expert tips delivered to your inbox each week: www.excel-dictionary.com/subs...
📺SUBSCRIBE
-Subscribe to never miss a video: / exceldictionary
🕰️TIMESTAMPS
‘0:00 Intro
‘0:45 Add the Developer tab
‘0:55 Insert a text box
‘1:07 Link the text box
‘2:03 Use FILTER, SEARCH, and ISNUMBER return matches
‘2:53 Wrap up
#excel #exceltips

Опубликовано:

29 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 31

@GFam0703 Месяц назад

Presented briefly and full of info. I don't know how you people figure all this out, but THANK YOU! 🤗

@mrlizard6529 Месяц назад

This works great. Couple of things to note 1. Spilling doesn't work in tables, so if you're using a template from excel that has tables in it and you're trying to return more than one column, it will give a #SPIILL error 2. Adding logic to the end to convert the boolean to 0 instead of 1 when the search field is empty ensures that no results are shown when the search field is empty 3. Enclosing the entire thing in an IF statement allows you to show text in the cell with the formula different than what is shown if no results are returned, such as "Please enter search criteria" Just a couple of ideas incase anyone else comes across this while trying to create a search box. Had no idea about the filter function. Good stuff.

@rizwanqadeer2412 6 месяцев назад

Love your all tutorials! ❤

@oxygendirect8588 4 месяца назад

This is the formula I needed, thanks! I've tried exactly the same and it worked! So happy with this.

@medaminehaji8801 2 месяца назад

tried it and working as magic, thanks for sharing

@khairulns8495 6 месяцев назад

this is what i want and finaly found tq

@user-de3do7ps8g 6 месяцев назад

Great

@RaffetAli2006 5 месяцев назад

But the filter function is not available in non-office365 excel. Is there any other alternative?

@DarylNotDead 6 месяцев назад

Came here from thread. Thank you. Can this be set to look for values on another tab in the same spreadsheet?

@brandiblankenship4892 3 месяца назад

I would like to know this as well

@rachelcasemore5483 Месяц назад

Would this still work if the original data was on Sheet 2 and the Search was on Sheet 1?

@user-sp1ym5qu9z Месяц назад

Do you have a template I could get hold of.

@JaneWade-dw2cl Месяц назад

Does this formula work in excel 2010 and 2016?

@ramseygr 6 месяцев назад

Very Nice! Is there a way to search across multiple columns? In your example, you're searching Representative (B5:B10004) - Is there a way to use the same search bar to look at other columns, like region for example? My goal is to search multiple columns in one search bar.

@ramseygr 6 месяцев назад

ah - I did figure out a way to 'cheat' - I created a column in my source table that concatenates multiple columns. ..works for my purpose anyway :)

@nicholashackie1407 5 месяцев назад

May you explain how you were able to. My issue is i have 22 columns with 10,000 rows each and my excel keeps freezing @ramseygr

@z9.b 3 месяца назад

Copy paste the formula from the isnumber part then add a + and paste it with a new range at the end of ur formula but before the ,”none”)

@nickbijl3142 6 месяцев назад

It is not working for my version of excel. =ZOEKEN (search in dutch) returns only the exact name when typed completely in the search. Not a numer. Also not able to type in the searchbox. Just in the cel linked to it. The text does end up in the textbox after typing in the linked cel.

@Dsgagp Месяц назад

I have 365 and It doesn't work.. I always get a value error.. works without being an insider member? Thanks

@bangru-nr2wv 20 дней назад

how to filter by numbers?

@user-th6ed3py9q Месяц назад

Anyone know how it working on Excel 2003 ? It prompt me a result #Name?

@Zrksys 3 месяца назад

why doesnt it work for me? just stays on #VALUE! all the time. is it cos the formula only works for 1 column or row for me? ye i have to add every column with a plus and change the array/range part. i add them with a + after )) before ,"none"

@loktar1234 2 месяца назад

Can not write commas in the formulas, only allows semicolons.

@susanmcrae7715 4 месяца назад

not working for me. I have the formula =FILTER(G5:K300,ISNUMBER(SEARCH(C2,G5:K300)),"None") and it is returning #VALUE. Could you tell me my mistake please?

@z9.b 3 месяца назад

Same

@teachcaldschannel9819 3 месяца назад

Found out the formula is incorrect. The second array should just be G5:G300 and not G5:K300. Change yours to =FILTER(G5:K300,ISNUMBER(SEARCH(C2,G5:G300)),"None")

@z9.b 3 месяца назад

@@teachcaldschannel9819 bro thank you so much its been a week trying to figure it out and i just saw this