Microsoft Excel - Zero Click Search & Filter Results

[@FsoOmar]

Learned so many things in this tutorial. Special thanks to you for making the file downloaded in just 1 click, no hassle, no email, no nothing. 😊

[@TylehurstXL]

That was so precisely explained! Excellent video, i knew highlight one but filter one i didn’t, thank you.

[@bcti-bcti]

Thank you for taking the time to watch and comment. Very kind words.

[@iankr]

Fantastic. Thank you for explaining these techniques so clearly.

[@bcti-bcti]

The pleasure was ALL mine! Thanks for sticking with us.

The idea itself is pretty cool and also the explanation is clear and easy, thanks a lot!

[@bcti-bcti]

Thank you for watching.

[@robbe58]

Hello Byron, Another very interesting way to dynamically highlight and filter out your searches in a data table. 👍👍👍

[@kebincui]

Excellent video as always. Thanks for sharing your wisdom in Excel ❤🌹

[@bcti-bcti]

So sweet of you to say. Thank you.

[@stevejez]

Very nicely presented & explained with no "fluff"

[@bcti-bcti]

Thank you for watching.

[@lalit387]

Very useful trick, thanks for empowering us.😊

[@bcti-bcti]

Our pleasure. Thanks for taking the time to watch.

[@hankgrimes1806]

🙏How might we go about highlighting an entire row that matches the text entered into the control? Also, does this control "float" above the cells, thereby making it possible to hide the input cell under the control? Finally, if I hide, say, column A after identifying A1 as the input cell, will the filter function still work?

[@bcti-bcti]

Highlighting the entire row would require a custom formula in Conditional Formatting. Let me see what I can come up with for an example file. Yes, the control "floats" above the grid on the "Drawing Layer". You could place the control over the reference cell. Yes, the function will continue to work if the reference cell is in a hidden column.

[@hankgrimes1806]

Thank you, and I'm on it!@@bcti-bcti

[@clay4gli]

Great video. Can you filter on a table name rather than cell range? I need this but the size of my table or cell range updates often.

[@bcti-bcti]

Sure. If the table is named "Data", then the spill formula (Filter sheet, cell A8) would look like the following: =IFERROR(SORT(FILTER(Data, ISNUMBER(SEARCH(H1, Data[Product]) ) ), {3,8}, {1,-1} ), "Item Not Found")

[@RadAlzyoud]

A very useful video. Thx for sharing.

[@HVACALC]

Excel-lent tutorial. I've got a question about making this search bar even more flexible. Is it possible to make the filter function search all columns instead of just the 3rd column? The intent would be for the table to only show rows where the search term appears anywhere, but then have the conditional formatting highlight the cell where the result is found.

[@bcti-bcti]

See the link below for a solution file I made to answer your question. Let me know if you have any questions about its functionality. www.bcti.com//wp-content/YT_Downloads/Whole_Row_Search_with_Cond_Fmt.xlsx

[@HVACALC]

@@bcti-bcti BTW if you are taking requests for a future video, I think I have a great one that a lot of people could benefit from: A power query (or Visual Basic) tutorial on summarizing file counts from folders in windows. If you work in an office, you likely files jobs under job numbers, so you have a regular folder structure. In the folder structure you likely have an 'incoming' or a 'submittals' folder full of PDFs that need to be reviewed. an 'inbox' of sorts. How would you make a power query request that would scan all of these various folders automatically to count up how many open PDFs are in each folder? I think the most important part of this type of tutorial would be a lesson in power query syntax, dealing with data tables within each query, and drilling down into specific information using the M langauge. I'm not a great programmer, but I was able to build this file to count all the files from all of my jobs, but it seems like I did it in a sloppy repetitive way because I had no idea how to implement functions. drive.google.com/file/d/1QLQ-Oc6bLX8ofKNb-no-DjyCjJht07S9/view?usp=sharing

[@rob235]

Exellent video, thank you. I have question. I followed your steps but In my filter table some rows in the datecolumn get 00-01-1900 because in original table they was empty. Do you know how I can handle that so I get the data as it is in original table? I tried to include COALESCE in my filter formula but it didn't help?

[@bcti-bcti]

Can you post the formula you wrote so I can see what are doing and how to possibly modify it for your needs? Thanks for watching.

[@suvojitghoshal1453]

Superb content,

[@bcti-bcti]

Thanks for saying so. We appreciate your viewership.

[@gandhisunil3]

Hello Sir Already implemented How to apply filter formula if search text from any column, will concatenate wrapped inside filter work ?

[@bcti-bcti]

I would need more details and possibly an example file to advise properly. Thank you for watching.

[@maciejkopczynski55]

If I understand you correctly this would do the trick: =SORT(FILTER(Data!A7:H352;ISNUMBER(SEARCH($H$1;BYROW(Data!A7:H352;TEXT.COMBINE))));{3\8};{1\-1}). It would be looking for a given string in the whole record and filter out recrods not containg the said string.

[@imatzav]

You don't have to use another table for the results. You can filter the original table if you first apply conditinal formatting to colour the entire row when it contains a result and use simple filters for the coloured cells or for an extra column with a formula indicating if the row contains results.

[@bcti-bcti]

That’s the great thing about Excel: there’s always more than one way to solve a problem based on the needed result. Thanks for contributing to the conversation with a different perspective. 👍🏻👍🏻👍🏻

[@nicor1501]

couldn't we use the '[if empty]' argument of the 'FILTER' function to prevent the error message if no result is found ? That would avoid using the 'IFERROR' function.

[@bcti-bcti]

Unfortunately, no. The reason is if the FILTER function returns an error message, like "Item Not Found", the SORT function is expecting an array of results by which to process. The SORT function will then fail, thus the need for the extra layer of error checking. Great idea though. Glad you're thinking of these things.

[@nicor1501]

clear, thx for the clarification 👌@@bcti-bcti

[@robertw236]

interesting, but this is not the method I use. I used the advanced filters in place where the criteria rows are underneath the titles and hidden. Then I show the filter/search row below the main titles. Run a selection change macro for the search text and I can search any column. Your solution is hard code and more complex than my solution. is. I should create a channel and show it off because no one is using my method and it's really cool!!

[@bcti-bcti]

Agreed. Start a channel and join the party! More content means more perspectives. It's always good to see how others solve the same problem.