How to Create a Highlighting Search Box in Excel 2016
video description
Date: 2023-07-08
Related videos
Comments and reviews: 20
Maxime
Here's a tip for you guys, but it requires a bit more work: you can link the cell to a Text Box (go in developer tab -> insert Text Box Active X control. This way, the data will be updated in real time and it actually look like a box in which you can type: much better!
You have to link the text box to the cell (right click the box, click properties and type the cell reference in front of LinkedCell property.
But the formula in the video won't work perfectly when the Text box is empty (it will still highlight everything, so you have to use this kind of formula:
My cell ref is A1 and i search columns A15 to C15
=AND($A$1; OR(ISNUMBER(SEARCH($A$1; $A15&$B15&$C15; 1)
There you have it: )
reply
Here's a tip for you guys, but it requires a bit more work: you can link the cell to a Text Box (go in developer tab -> insert Text Box Active X control. This way, the data will be updated in real time and it actually look like a box in which you can type: much better!
You have to link the text box to the cell (right click the box, click properties and type the cell reference in front of LinkedCell property.
But the formula in the video won't work perfectly when the Text box is empty (it will still highlight everything, so you have to use this kind of formula:
My cell ref is A1 and i search columns A15 to C15
=AND($A$1; OR(ISNUMBER(SEARCH($A$1; $A15&$B15&$C15; 1)
There you have it: )
reply
Vincent
Hi everyone. I hope this is simple to solve but I'm at this stage 3: 01 in the video and when I click OK after making the changes to the formula I get the message Your formula is missing a parenthesis--) or, Check the formula, and then add the parenthesis in the appropriate place.
My formula isn't really any different from his except that I have more columns. What do I do to make it work as shown in the video?
reply
Hi everyone. I hope this is simple to solve but I'm at this stage 3: 01 in the video and when I click OK after making the changes to the formula I get the message Your formula is missing a parenthesis--) or, Check the formula, and then add the parenthesis in the appropriate place.
My formula isn't really any different from his except that I have more columns. What do I do to make it work as shown in the video?
reply
Danny
This was extremely helpful, and I have used it a number of times successfully, so thank you for your tutorial! However, in a recent spreadsheet I have found that the whole row is not being highlighted; the highlighting appears only in the last 6 columns (of 19) for the particular row where is finds the text/value. Any idea why this could be?
reply
This was extremely helpful, and I have used it a number of times successfully, so thank you for your tutorial! However, in a recent spreadsheet I have found that the whole row is not being highlighted; the highlighting appears only in the last 6 columns (of 19) for the particular row where is finds the text/value. Any idea why this could be?
reply
Shaukat
Thnx, it is very working perfectly, i doing my job exactly what u say to do it for me, the question is this I like to jump cursor again in the same place where i have written text to find the text or any article I mean in the same cell. I take the cursor by hand. for typing text or article. pls tell me the formula
reply
Thnx, it is very working perfectly, i doing my job exactly what u say to do it for me, the question is this I like to jump cursor again in the same place where i have written text to find the text or any article I mean in the same cell. I take the cursor by hand. for typing text or article. pls tell me the formula
reply
Joe
Is it possible to create a formula/rule for the search box, that will make the search box update itself, in other words, its range of data to search from will constantly change. Or is that not possible and I will need to adjust the rule every time new data is input in excel?
reply
Is it possible to create a formula/rule for the search box, that will make the search box update itself, in other words, its range of data to search from will constantly change. Or is that not possible and I will need to adjust the rule every time new data is input in excel?
reply
Samer
I have a request if you don't mind. First thank you for this amazing feature it really helps finding data. I want to know what formula/function you can add to this highlighting feature to only show the highlighted rows and making the un-highlighted information disappear
reply
I have a request if you don't mind. First thank you for this amazing feature it really helps finding data. I want to know what formula/function you can add to this highlighting feature to only show the highlighted rows and making the un-highlighted information disappear
reply
marinerules
This works for me as I look at a grocery database with 30, 000 upcs. However when I type in a upc it will highlight but not take me to it. I still have to do Ctrl find for that. Any options on how to get the conditional formatting to take me to the highlighted box
reply
This works for me as I look at a grocery database with 30, 000 upcs. However when I type in a upc it will highlight but not take me to it. I still have to do Ctrl find for that. Any options on how to get the conditional formatting to take me to the highlighted box
reply
Carl
Don't use in multiple data in one cell in multiple columns with merge cells. It will mess up everything and it will highlight even not the keyword that you enter. You can use it by searching only in one column even if you multiple data in one cell
reply
Don't use in multiple data in one cell in multiple columns with merge cells. It will mess up everything and it will highlight even not the keyword that you enter. You can use it by searching only in one column even if you multiple data in one cell
reply
matt
Love the formula. One question. I like how it finds and highlights them but is there a way to make it move to it on the spreadsheet. I have a very long spreadsheet and it would be nice if it went right to it like it does when you hit ctrl f
reply
Love the formula. One question. I like how it finds and highlights them but is there a way to make it move to it on the spreadsheet. I have a very long spreadsheet and it would be nice if it went right to it like it does when you hit ctrl f
reply
Tammy
Thank you very much for the useful lesson. Is it possible to have a search of the form the pull the data from the other sheet & display it on the form (the workbook that has 2 different sheets: Form sheet, Database sheet? thanks
reply
Thank you very much for the useful lesson. Is it possible to have a search of the form the pull the data from the other sheet & display it on the form (the workbook that has 2 different sheets: Form sheet, Database sheet? thanks
reply
El
HELP, when i add the If statement it says that youve entered too many arguments for this function, click the insert button located to the left of the equal sign in your formula. but theres no insert button anywhere
reply
HELP, when i add the If statement it says that youve entered too many arguments for this function, click the insert button located to the left of the equal sign in your formula. but theres no insert button anywhere
reply
Anthony
Hey, nice video. I have a question if anyone might have an answer. No matter what I search using this method, it always highlights the row directly beneath whatever it's reading. Anyone know how to fix?
reply
Hey, nice video. I have a question if anyone might have an answer. No matter what I search using this method, it always highlights the row directly beneath whatever it's reading. Anyone know how to fix?
reply
Michael
I am doing something wrong because I am missing a parentheses error keeps popping up. Also that button to the right of the creating the formula is not there its an up arrow. What am I screwing up?
reply
I am doing something wrong because I am missing a parentheses error keeps popping up. Also that button to the right of the creating the formula is not there its an up arrow. What am I screwing up?
reply
Mir
Yes, it does work. Thank you Teacher. But I wish I could get the result on top row because I don't want to scroll to down.
Provide me such formulas if you know. Thanks again: )
reply
Yes, it does work. Thank you Teacher. But I wish I could get the result on top row because I don't want to scroll to down.
Provide me such formulas if you know. Thanks again: )
reply
Gurel
This is useful but not so good when your data set is 500+ cells long. Is there a way to make the search function hide all cells which do not contain what you have searched for?
reply
This is useful but not so good when your data set is 500+ cells long. Is there a way to make the search function hide all cells which do not contain what you have searched for?
reply
mGtrn
Hi, very informative this.
But, is there any way to keep the searched value/cell highlighted when searching for another value? Like in way it's checked off somehow. Thanks
reply
Hi, very informative this.
But, is there any way to keep the searched value/cell highlighted when searching for another value? Like in way it's checked off somehow. Thanks
reply
Jason
Good day. I've tried that but it there is an error saying There's a problem with this formula. Not typing a formula? .. How to solve this problem? Thanks and more power.
reply
Good day. I've tried that but it there is an error saying There's a problem with this formula. Not typing a formula? .. How to solve this problem? Thanks and more power.
reply
Goan
This is so frustrating, i did exactly this and it wont work. I typed in everything you type but the search box does nothing AND my table is highlighted halfway down?
reply
This is so frustrating, i did exactly this and it wont work. I typed in everything you type but the search box does nothing AND my table is highlighted halfway down?
reply
Behrouz
Thanks a lot but I couldn't finish it as the writing & resolution was so small to read. Would you be kind enough to type down just the final command here? Thank
reply
Thanks a lot but I couldn't finish it as the writing & resolution was so small to read. Would you be kind enough to type down just the final command here? Thank
reply
Kenneth
I'm using Excel 2016 for MAC (not MS Office 365. It appears that the Conditional Formatting. Rule Type. is not available. Is there a way to get that menu?
reply
I'm using Excel 2016 for MAC (not MS Office 365. It appears that the Conditional Formatting. Rule Type. is not available. Is there a way to get that menu?
reply
Add a review, comment
Other channel videos
