🔥Excel Tutorial: Magic Search Bar You NEVER Knew About!
🔥Excel Tutorial: Magic Search Bar You NEVER Knew About!
In this video we have explained, how to create a magic Search Bar in Excel using Excel Shapes, Form Controls and ActiveX Control. We have not used any VBA coding to create it.
How do I create a dynamic search bar in Excel?
How do I create a search box in Excel without VBA?
Time Stamp:
00:00 Intro
00:20 Creating/ designing search bar template
04:00 Setting reference for search bar
05:50 Excel Filter formula and functions
13:47 Wrap up
Subscribe to my channel:    / @unofficialkaryalay Â
Follow us on Instagram: https://www.instagram.com/unofficialk…
Follow us on Twitter: https://twitter.com/UKaryalay
Excel Reports Playlist:    • Excel Reports Â
Excel Playlist:    • Excel Â
Excel Tips and Tricks:    • Excel Tips and Tricks Â
MS Word:    • Microsoft Word Â
Excel formula and function playlist:    • Excel Formulas and Functions Â
Download Template: https://docs.google.com/spreadsheets/…
Your Queries:
Excel Tutorial
Excel Tips
Excel Tricks
Excel Search Bar
Magic Search Bar
Excel Secrets
Excel Guide
Excel 2023
Excel Navigation
Data Management
Excel Hacks
MS Excel
Microsoft Excel
Excel Learning
Excel Shapes
Excel ActiveX Control
Excel Form Control
Excel Filter Function
Excel Workspace
Excel Productivity
Excel Improvement
Excel Search Type
Excel Magic Search Bar Tutorial
FILTER function in Excel
#excelsearchbar #exceltutorial #exceltipsandtricks #exceltutorialinhindi
Content
0 -> Welcome to you in today's new video of those official office, in today's video, I will teach you how you
4.5 -> can search any value in big data by creating such a search box in Excel and you
10.26 -> can give multiple criteria, you hope That you will get to learn something new from this video, if you
14.34 -> like this video, then you must like this video, give your feedback by commenting and if
18.84 -> you are new on this channel, then definitely subscribe to this channel, let's start
22.8 -> So friends, I have created
26.94 -> a dummy data in excel of some employees, here now we
32.46 -> will set the search criteria by using this data, search it in excel, to set it, first we copy the header which is stable.
37.68 -> I will take it and paste it in a new tab.
42.9 -> I will adjust, now after this, in the next step, we will have to enter a rectangle shop, which will
47.58 -> reduce our search box, then for that we will first click on the shirt, here we will
52.02 -> click on the illustration, here there is an option of shapes, click on it and Here I will go to the rectangle area
56.58 -> and here I will select a rectangle with a round date corner, do it like this, here
61.14 -> I will draw, after that I will hold the yellow point and drag it to the right side, then it is good
65.52 -> It will become round, now in the shop file, I will make it no flower, so this is the outline, I
70.44 -> will take it in the color of my header so that it matches our template completely, now I
74.94 -> will give a little shadow effect to this box, if you like it For this we will go to shadow effects and I will do this option thing,
78.9 -> shadow with offset center, now next which is our border of this shop, we will adjust it a little bit,
84.36 -> so I will again click on this outline here, it will go to the weight. If there is one, I will make it one and half,
90.12 -> I will fix it a bit, now after this we will make a duplicate of this shop, after selecting this, we will control and deep
94.5 -> press, a duplicate will be made of it, first we will remove its shadow effect, then we
99.06 -> will go to the shop forward shop After going to effect, going to shadow, we will make no effect, if we change the shop also a little bit,
104.28 -> then we will go to edit shift, change shop and here I am selecting the second last option, the
108.9 -> top corner round inside the rectangle, after that we will make it cry. Will click on rote and rote left
113.94 -> will click on 90 degree after that we will reduce its size a little bit then we will track and
118.98 -> set here on this corner after setting it here we will go to shop format and its outline from
123.84 -> here If we remove then we will select no outline here and we will also take it a shop
129.36 -> in slightly dark color then we will select this one after that we will go to Insert then go to Illustrations and here we will click on icons and here
135.18 -> we will Will add the search icon, then we will type in the icons, we will search by typing search
139.98 -> here, we will press enter, we will select this option, if we adjust the size of this shirt in the right way
146.16 -> and set it here, we will go to the graphic feel here. And we will give it white color, in
151.86 -> this way our search box will be ready, next we will select the search bar, we will
156.12 -> make a duplicate of it by pressing control, then by dragging it here on the side, we will set it parallel to it
161.82 -> . After that we will go to developer then pay a click on insert drop dr here and
167.04 -> select this option here text box on active x control and inside our search bar we
172.14 -> will draw this text box something like this After next we will go back to insert again
176.64 -> inside develop press and inside form controls here there is a choice of option button then
182.04 -> click on it and here we will draw it adjust a little inside the box we will
186.18 -> check it first Next to the box we'll name it Search from right we'll register another check box next to it
191.1 -> and we'll name it Search from left Next we'll insert another check box and we'll
197.04 -> name it Search Anywhere Next we'll control click After selecting all these three check boxes, we will only
203.1 -> go forward, click on align and align top, after that distribution
208.02 -> will be horizontal, by clicking on control, we
212.58 -> will clean select the box that we have added to this background, after that right Will click on the option of group, here we will click on the group
216.9 -> and it will become a group, next we will select our search box here, then the developer will go to
222 -> And we will click on the properties, after clicking on the properties, we will have an option, we will
225.66 -> click on the special effect of the special effect, and by going here in the drop down, we will select the effect flat,
230.4 -> close it and click on the side, see Can I know here that the
234.3 -> borders of the search box along with the borders have disappeared, then we know that now we have created the search box, how does it reduce,
239.16 -> then we will select it again, we will go to the properties, here there is an option of link cell which
243.72 -> The key is black, now I write b1 here and we will cross it, come back out of design mode,
248.94 -> now whatever I type here, it will be upper in b1 as if I write here
253.02 -> unofficial The office will be typed there, let me enlarge the size of the search box
258 -> so that it is visible, I go back to the design mode and adjust the size a bit, ok,
262.74 -> our search box is something like this Yes, it will reduce, after that let's see how to
266.64 -> reduce this checkbox which is ours, so first we go here, exit the design mode, right click
271.62 -> on any one of the check boxes, the option of format control will come. Will click on it and here below
276.12 -> is the option of link cell so I give a1 here suppose and press ok here now
280.8 -> as soon as I select this check box then here one a second box will go this round I will go to A
285.24 -> and if I select the third, then I will go to three A, then in this way we will reduce our check which we have
289.98 -> linked to the sales here, now we will go to the formula manager and give some names to both these sales. Will give
294.6 -> as if this is our search type because we will call the check box here as search type
299.28 -> and the text we are writing here, so we will call it search text, which
303.54 -> is having value in b1, we will search it. Will speak so now to name them here we will go to formula type
307.02 -> click on name manager click on new and here we will write search underscore type ok
312.6 -> but press will add another new one here will write search understand now Here we will change the value
318.78 -> to B, take b1 and make it ok and close it, after that we will
324.24 -> set some more names on the particular side of our data which we have entered here in the data seat. Those who
328.56 -> search for our name, then for this we will give a name for the name, I have named it inside it and we have
333.72 -> our data according to the name, then we will name it accordingly, next we will dynamically
338.4 -> set this name. Will do that if you add a new record here then that too will be a consideration Jain in our search box if you
342.78 -> search for new record then that too should be searched in that way we will design it then first of all we
347.7 -> will set it for our entire range So here we will go to our search up here
353.34 -> we will write equals tu and complete range ko we will select and till and freeze it press f4 and press enter to complete range and
358.92 -> after that we will retire something like this It will be present here, now we want
362.88 -> our data to be updated dynamically, like if we insert some new line here, it will automatically
367.08 -> update, so for that we will change some formula here, for that first we will enter 21 from here. and this
371.88 -> is the complete formula, we will take it in inverted commas so that it
377.52 -> will be in the form of a tax, after that in the end we will put more here and here we will write the formula, by writing the formula of count a count tech,
383.1 -> here we will open the bracket. After entering the data type, we will select the complete A pen so that if
387.66 -> our narrow like 22 is also added here , then that too will be considered.
393.3 -> Also be considered, so we will select the complete B range,
398.58 -> we will lock it by pressing f4,
404.76 -> we will close the bracket and press enter. Here
409.26 -> we will use this direct function by writing these direct, we will open the bracket and after coming in and we will close the bracket,
414.36 -> we will press enter and I will again get my full range here, now if I
418.8 -> go to the data type and add some new medical here But as I can write official enter, now we will go here and see in
424.32 -> the search whether it is coming here or not, then here a new line item has been created, in the name of on official,
429.06 -> we have input that value of zero. No, if we input, it will also come here, then
432.96 -> going back to the next, I delete this line here, for now, after that we have entered the formula here.
437.04 -> We have created our own, because of this we have full dynamic range, so I will copy this formula, copy it and
442.32 -> go to formulas, here name will click on manage, click on new and here
446.7 -> will give it a name, whole range and Below we will paste our formula which I just copied
451.32 -> our dynamic range formula after that press ok and close it now suppose I am
455.82 -> deleting this formula here now I just equal tu here I will write the whole range, I
460.08 -> will enter the whole range, then it will give us the complete data automatically here, so we delete it for now
464.7 -> and we will create a new name, now next we will create only for the name
469.5 -> in our data set, so we If we keep it dynamic, then for that we will first go to the formula, inside it
473.94 -> we will go to the name manager, here we have already created a name for the whole range, so we will
478.62 -> copy this formula, it will click on the new formula reference. I will paste the formula from us, now
484.92 -> one thing we have to do inside it is that our data which is selected here is from A to F and
489.96 -> our name is only in B, so we will go from here to B to B. Will select it, just press OK
494.88 -> and close it, now let's see whether it is reducing or not
498.66 -> If the names are being found
502.8 -> then it means that it is doing less, then like we will delete here, now next we will
507.3 -> start entering our formula here, then first of all we will set the formula for search from right, then I will
512.28 -> first go to the cell below the employee add here we will write equals you will open the filter bracket kar lenge
516.72 -> oh me we will put the whole range comma press include if a is there then in include we will write the name
523.68 -> equals you search text search text and if it is black If it remains, we will close the show black bracket, we will
529.32 -> press it, then let's see whether it is reducing or not, let's say that we
532.92 -> copy a name from here, as if I have copied this name here. Here, if I
538.62 -> paste the search text in the search text, then here we will get its value here, now suppose that I am
543.78 -> typing only two letters here, but I am writing, then here we do not get the result. For this
548.04 -> we have to change the formula, first we will go to our cell where we
552.72 -> had added the filter formula, after that we will have to add the left formula that if the
557.1 -> length of our name is a little further from the left side Aam dal ke land will be written next to the name Aa Jayenge, its length is equal to the search tax,
563.4 -> so if it shows us all the matches, then we will select it,
568.26 -> close the bracket and press enter, then we can see that here But PR is the first letter
573.42 -> of these two names, so we are seeing both the records here, if I write only P here, then
577.44 -> only two names are matching, so I am getting two names, if I write here There are only two names actually matching
582.36 -> and we will get both the names here, after that we will give a name to the formula of our filter, then
586.56 -> copy this formula, copy it and click on the name manager, we will go to the new window and give it a name
591.06 -> . Search from left will paste the formula here press OK and copy this formula back will give
596.16 -> a new name here we will give the same name we
602.1 -> will paste the formula from search from right here now only one difference is empty It will appear that we had entered a left format here,
607.92 -> now we will have to change the formula of the left and put it right, then here we will remove the left and write right
612.84 -> and press OK, close it, then check our formula. Doing or not doing
617.04 -> so here I write A and after removing it we write here Search from left so search
621.84 -> from left is doing so first here the name we have where it is the first letter A got it he
626.94 -> picked it up now this is how we try it by giving search from right select search from right
632.34 -> enter way right first item found in this name so it picked up this name so now we have these two
637.38 -> formula If we are doing then finally we try how we
642.3 -> can set formula above search anywhere so let's now set formula for search other variety from here we will remove formula
646.68 -> then for that we will apply formula bracket of filter We will open the whole range
652.56 -> By putting a comma, we will write here, we will open the note function, then we will open the function with this error, after that we will apply the
658.68 -> search function, here in the fine text , we will select the name manager of the search text that we have
663.54 -> created. I will select the name of the name which we have
668.1 -> created and close the bracket, I will always explain you
672.6 -> the search function, suppose we have written A here, then the search function will search this A. Where is our search in the entire data,
678.24 -> if there is any error in it, then the function which is on this error will
684 -> give us the value of true or false and applying the note function means that we will not get any error if the value If there
689.16 -> is a value or nothing comes, then basically if we do not have an error, then it will search whatever we
694.92 -> write in the text box, as if we have written A in the text box, then it will match A in the column named
700.26 -> our data set. I and according to that he will present the data here, then he does the inter press
704.88 -> and wherever he has got the story, he has presented all those days here, so in this way
709.8 -> we have searched the necessary ever formula, now in this formula We have missed one part, so if it is empty, then
715.08 -> we will not leave it, we will put two inverted commas, we will close the bracket and press Inter, so
720.42 -> now our formula is complete here. will also copy
724.26 -> from here copy name will go to measurement create a new name and name it search anywhere rest you
729.66 -> we will paste this formula here and press ok and close it finally now finally
734.04 -> we If we want to select any checkbox here, then our formula
737.94 -> should be refreshed accordingly, so we will delete the formula from here, now here we
742.02 -> will apply IF function, Equals, you will open IF bracket, select your search type. Will do this search type
746.4 -> by putting this equals tu one comma we will give it value search from right then our if second IF will give and
751.5 -> here we will give logic function that IF search type it equals tu give search from left common if
758.64 -> both these are not there then Search anywhere bracket close and press enter then here we
763.56 -> will get the value this search is from left so this is in left first so here I am getting the value of goods
769.38 -> if I search it from right then here we will get If he is getting A in the last, that is why
776.58 -> he will search for A on the story as well and will present us the value here, so basically he is doing less, finally
781.5 -> we will copy this formula, after typing the formula, the name manager will also go.
785.64 -> We will create a new formula, we will name it, we will paste the formula here and
791.28 -> press OK, and we will close it, now simply we will write only equals to search data here, echoes
796.32 -> to search data, press Inter and it will automatically apply the formula. It will be done here, finally, we
801.24 -> will convert the value of our attendance and performance here, we will convert it into percentage, then till this we will
806.1 -> select this row pen, control shift and press five and it will be converted the day after tomorrow, its
810.9 -> completely selected By doing this, we will make the central line look a little better, now to hide it,
815.1 -> we will whiten the font color so that it is not visible here, so friends, you can create
820.32 -> a search box like this in your excel file. You can make the steps by following
825.48 -> just a few steps without any V is or retirement of coding, I
829.5 -> will give you the link of the set in the description box of this video, go there and download the set now for practice plus purpose if you want If you like this video
833.46 -> , then like this video, subscribe to the channel and
837.24 -> you can explore my channel to see more videos like this, for today, that's all we get in the next video, thank you
Source: https://www.youtube.com/watch?v=OjBzoK9Sua4