Interactive Search Box in Microsoft Excel | No VBA Code!!!

hello and welcome to our today's Excel tutorial believe it or not this State ofth art search box is made in Excel only without any coding here very easily I can find any record just by typing part of the text if you want to make this yourself watch this video till the end without skipping because I will show you every step in detail so let's start this is the raw employee data that you have with you from where you have to make a search system with a interactive search box where you will put the text and in return Excel will provide only those records to display so the first step is select any cell within the table press crl a to select the entire data press contrl T to convert this data into a table in Excel click on okay and this is now transformed into into a table from this table you have to get the data which you will display in another place let us insert another sheet write the heading ABC company private limited this is the employee database now we have to create our search box where we will enter the text for that we need to go to the developer tab tab from the ribbon above if the developer tab is not visible in your system then first right click anywhere on the ribbon click on customize the ribbon you should check the developer option on and click on okay so now here developer tab is already there from there you go to insert and from the set of the activx controls select a text box and draw a text box here now right click on this and click on properties a set of properties will appear which pertains to this text box that you have just created from here find out the linked sell option and here you need to type the reference of any one Cale of the Excel sheet let us type H1 and hit enter now here whatever we will write that will also appear in the cell H1 let us check that to write WR anything into this text box we need to First switch off this design mode from here now you can write anything here so if I write anything same text will appear here also in the cell H1 because that is a linked cell to this text box now you can change the font type for this text box by right clicking the properties and go to font and from here you can select anything in any font that you like I'm selecting appts narrow bold select 12 as a font size and click on okay now I am closing the properties pen now we will prepare the environment let us get a shape like this I'll send it to backward here we'll go to edit text and this is a search box so there will be a search label here let us increase the font size from here make it Bold and the color will be yellow from here I changing the font we can change the color of the background from here now we will go to insert once again click on lustrations and click on icons to Avail this facility you need to have your uh internet connection on search the magnifying glass icon and insert it so it has come we'll resize it to fit our text box and background level give the color yellow to make it visible I'm changing the font from here for the entire sheet now we'll make this a little different I will give one today function bracket open and close po to display the date of today now we will go back to the table that we have and copy the header from here byrl c i we come back to this sheet and we'll paste it through control V we need to adjust the columns that the same columns that are in our data in the table that we are pasting here the alignment has been Disturbed for this we will select everything and right aligned with the table below it so here we can give any color that we want we will freeze the pan so that this part is visible always even if we scroll down to the record at the bottom of the table so now we have to change the name of the table for that we will select the table in anywhere go to table design and from here we will change the name of the table let us keep it my table and hit enter now the name of the table has been changed to my table you can check that from the formula tab clicking the name manager there is one table and it is called my table let us close that now we will have to enter the most important thing that is the function that will display the data and the function function is filter so let us type filter open the bracket Excel is asking for the array as a first argument the array that we will refer to now is the table that we have created so for that we have to type my table here my table has come double click on that put one comma next Excel is asking for the include parameter type three functions consecutively first one is not open the backet type is error that is for the error handling if no data is matched with your search criteria and then put the third function that is search open the bracket now to find the text you need to refer to the cell where our text box is linked and that is H1 select H1 put one comma Now Excel is asking where you will search the text that you have type that is withing text for that you have to refer the table my table open the square bracket we will search the text that we will type in the first name gender Department designation and state of living columns so let us start with first name we'll double click on that close the square bracket put one erson here open the in double inverted comma put one Asterix close the double inverted comma put another erson notice one thing this part is common and that to be entered in between the other fields also now we will enter the second condition of search that is might table open the square bracket and this time we will double click on gender close the square bracket put this part with contrl c and contrl V there also now we will provide the third search criteria that is my table open the square bracket double click on Department there is the third column again the fourth column mble this time the designation close it and now the fifth column that is M state of living close the bracket now we will close the bracket of search function bracket of easer function and bracket of note function now again we are within the filter function we'll provide one comma and now you see Excel is asking for another optional parameter that is if mty provide it as blank because we don't want any null value to return if no record is found as per the given criteria then it will return a blank let us close the bracket and hit enter and you can see the data from the table that we have in sheet one has come to display area here and it is now showing you all the data because there is no text in the search box so we will now write something on the search box let us first close off the design mode and now here let us type something I'm typing accounts you can see now only the accounts related data records are displayed here if I delete that once again the entire data will be displayed here let us consider I want to search only the managers from the record so let us just type manager and now only the managers are displayed here I can search for any state like Rajasthan and there are only the data that contains the text Rajasthan I can also search with anybody's name let us type utar utk and one utar is there this data is display here keep the font color and the background color of the H1 cell the same so that if I write anything it should not be displayed here so your search feature in the Excel is ready and this can be a very nice presentation of your data to find out anything out of thousands record in a particular table and you can display it in a nice way you can do some more formatting for better display you can give any color that me fit your choice and display all the records in a very nice manner if you have liked this video please share it with your friends who will like it and if you have not subscribed to our Channel please do that now thanks for watching

Channel: Excel Ideas

Views: 3,301

Keywords: excel search box, excel search bar, excel search, excel search function, add search box in excel, excel search box filter, search function in excel, Interactive Search Box in Microsoft Excel

Id: KZc9W0JfNrU

Length: 10min 46sec (646 seconds)

Published: Tue Jun 25 2024