Unlock Excel Secrets: Magic Search Bar You NEVER Knew About!

foreign [Music] how we can create a magical search bar in Microsoft Excel as you are seeing my screen I have created this magical search bar to search your data by the employee name first you have to select the search type so let's say I'm selecting here search from left and as I type here let's say t then I'll get the names starting from T if I will type again a then you can see I'm getting only one name which is starting from ta if I'll click on search from right then I'm getting these two name which are ending with t and if I'll click search from anywhere then you can see I'm getting all the names wherein T is available whether it is in the starting or in ending or in between if I will type here a then I am getting these three names wherein ta is available you can see all right friends I would like to tell you I haven't used any kind of vva to create this search bar this is pure formula based I'll show you how we can create this in Microsoft Excel so let's start without delay let me show you the data first here we have the data this is the same data which we are showing here okay but we are using the formula on this data so let me copy this data and let's press Ctrl n to open the new workbook so this is book one you can see let me paste that data here let's rename this seat as data okay now let's add a new C which is search seed and now we will copy the header from here because header will remain same and let's paste the header from here and make it slightly wider like this all right now we can change the color as per our theme color so let's say I'm taking this color okay now let's create a rectangle here so go to the insert and go to the saves and let's insert this rectangle which is rounded Corner rectangle and drag that rectangle here pick this yellow handle and move to the right hand side we will change the format of this so I will take the white color in the background shape outline let's choose this outline and make it little thinner so go to the bait and let's take this size all right now we can add some Shadow also so select this go to the save format and save effects Shadow and let's take this Shadow which is offset Center okay now make a copy of this press Ctrl D after selecting this and first remove the Shadow from here so go to the Shadow and say no shadow and we will change this shape with the another shape so go to the edit shape change shape and let's take this shape from the rectangles let's take this shape second last okay we will rotate this rectangle and we will rotate it to rotate left 90. and make it slightly smaller like this and let's keep over this rectangle the previous rectangle and we will remove the Border go to the shape outline and say no outline we will fill a dark color here so let's take this color now we will insert a icon so go to the insert and go to the icons and here we will insert search icon in the search icons type here search and enter so let's take this one click on insert so we have this icon here let's make it slightly smaller like this and put here change it to the white color so let's make here white color this one all right now we will make our duplicate of the previous rectangle press Ctrl D and we will put this here this is for the search type go to the developer tab and insert a ActiveX control text box this one and drag that text box here over this search bar all right now we will insert form control option button this one and just drag that here and rename it from left or search from left okay now let's put this here and press Ctrl D to make it duplicate and put this here this will be search from right select this and press Ctrl D to make this duplicate and put this here you can put here search anywhere all right select all three by pressing Ctrl key and mouse right click and then go to the save format align this top and distribute horizontally select the back shape also right click and make them group so this is one group now we will select this one tbx control text box then we will go to the developer tab and we'll click on properties first of all we will change the special effect and let's take let's take this one flat okay so it is not visible here you can see but if I will come out from the design mode then it is working to go to the properties again you need to select the design mode first in the developer tab then you will be able to select this and then you can go to the properties or right click and go to the properties and here we will go to the linked cell we can link it with let's say B1 so just type here V1 okay now whatever I will type here let me come out from the design mode and if I will type here PK that will be appeared here all right now we will connect this option button any of the option button right click and go to the format control and we will link it with the go to the cell Link and Link it with let's say A1 click on OK so if I will select this then here you can see one is there because First Option button is selected two and three all right now we will start putting the name here so this is the selection type and this is search text so beginning of this as selection type go to the formulas and name manager click on new name let's put this as search type Okay click on OK and let's take the new name search underscore text and this time we will take B1 this one just click on OK so we have two names here okay now we will create few other names from the data sheet first to put the filter function we need this name column okay and we need this whole range so we will create it dynamically whenever you will add the new entry here that will be considered automatically so let's create it first if I will take the whole range here let's say I'm creating for the whole range and I'm selecting this range the entire range okay and let's freeze this press F4 and just enter so I am getting the whole range you can see all right that means this is my whole range but this is row number 39 that should be dynamic because whenever I will add the new data that row number will be changed put the inverted comma here to make this text and we have removed 39 from here we will use here and count a and let's go to the data and select the first column so it will count all records from here and that will be my last row press F4 to freeze this and just close the bracket and hit the enter we are getting a range here as a text so we can use the indirect function here indirect and let's close the widget then you can see we are getting whole range here now if I will add a new name so you can see we have till here if I will add a new name here let's say PK then you will see that name in search worksheet also all right let me delete this so this is my whole range formula now I can convert this into a proper range this is dynamic range now I'll go to the formula and name manager and I'll create here whole range and I will paste that formula here which I have copied click on OK close if I will delete and I will type here let's say whole range and just enter I'll get the whole range so let's delete this now we will create the range for names only not the whole range only for the name but it also should be dynamic go to the name manager and from here we can copy this whole range formula click on new and we will type here name and we will paste the same formula here now only change here we are taking from a to G we will take a to a just click on OK and close if I will type here name equal to name this one then I will get only names not the whole range okay so this is working now we will start putting our formula so let's put the formula for left first so we will use here filter function filter and the array we will select from the whole range so we have the whole range here comma include we will search from the name okay so let's take name and let's take equal to whatever is the search stats so this is direct equal to not the starting from left okay let me show you first so let's take here such steps if empty let's go blank and just close okay now let me show you if I will type here the exact name this one if I will type here this name then I am getting the name of that person we can put the logic of first two letters so we can use here left comma and how many characters whatever is the length of this text so I will use here Len of search text okay that means first two character is this then that should appear in the text so let's enter Then here you can see it is working if I will type only T then I'm getting 2. so this is working so this formula is search from left so we can copy this and we can create one new name and let's create a new name that could be search from left and just paste this here okay we can copy this formula and create a new name and we can type here search from right and just paste that here so left and right will remain same the only difference we will use here write function in place of left okay just click on OK and close so now let me delete this I will type simply search from left then it is searching from left name is starting from T we have put here T okay if I will type here search from right then it is giving me these tuning now let's create search anywhere okay to create that search anywhere we can write the formula here once again let's use the filter function whole range comma name that means we will use the search formula here search and the text we will search whatever is the search text this one and within text we will search it in the name okay and just close the bracket it will return the position so search formula return the position let me show you the search formula first and then I will explain you so let's come here let's say I am searching T so I'll use here search T and I want to search here and just enter it will return the value because T is not available if T is available it will return the position of that t here you can see it is available and here you can see it is available on the first position so that means if it is error that is not available and wherever it is not error that means T is available there okay so we will use is error function there so let's use again we will use filter whole range and then we will use here first not okay then we will use is error and now we will use search text within name and just close the bracket let me explain you first it will search here these are Stacks in the name either it will be a number or a error now here we are checking if it is error or not now it will provide true or false we are putting here not that means it should not be an error so wherever it is not an error then it will filter so let's close this close it again comma for empty let's put blank and just hit the enter so here you can see we are getting all the names considering T if I will put here ta then I am getting the result okay so let's copy this formula go to the name manager and put here new and just type search anywhere and paste that formula here click on OK and close all right now if I will click here that logic should be changed automatically so what I can do here I can put a IF function if this value which is search type okay we have already defined it if search type is equal to 1 then use search from left if search type equal to 2 then search from right in the else case we will use search anywhere this one and just close now let's test it I have put ta so I am getting the name starting from ta if I will select this right I'm not getting any name so let's remove a I'm getting two names if I will click here I'm getting two names starting from t here ending with t and here it is searching anywhere so it is working now we can copy this formula and we can create new name you can put the name as let's say search data and paste that formula here and just click on OK you can simply use equal to search data is working now we can change the format of these percentage till the end so use shift control and 5 we are getting in the percentage and we can align this Center go to the home align the center go to the view and remove the grid lines from here you can select these two and you can hide or you can put the white font so it will not be visible now you can put the Border also so let's select this range and go down and let's take here 100 200 300 records according to your data set and then we can put here conditional formatting and use alt OD to open the conditional permitting rule manager click on new rule go to the format only cells that contents here we will select no blank format border and let's select this color theme color and outline click on OK apply you can see our border here if I will select let's say from left then I'm getting the water in these two records only here two records so friends that is how you can create the magical search bar in Microsoft Excel friends I hope you have enjoyed this video If you like this video please hit the like button and please subscribe my channel to get the regular update of my videos thank you so much for watching

Channel: PK: An Excel Expert

Views: 477,977

Keywords: 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, PK's tips and tricks

Id: w-tXwbYpr7I

Length: 17min 58sec (1078 seconds)

Published: Tue Aug 01 2023