Forum Discussion

Stephen_Langel's avatar
Stephen_Langel
Copper Contributor
Apr 30, 2020

Help with filtering

Hello everyone,   Could you please help me to do a text filter of all of the columns in a spreadsheet at once as opposed to filtering each column one at a time?   I would like to isolate rows in...
excel
Formulas and Functions
Stephen_Langel's avatar
Stephen_Langel
Copper Contributor
Apr 30, 2020

SergeiBaklan 

 

Thank you so much, Sergei! This is the best answer I have received about this problem. Let me ask a followup, please. 

 

What are the steps to set this up if I want to include the entire spreadsheet in my filter/search? The spreadsheet goes from rows 1 - 8,025 and from columns A - L.

 

Thank you again, my friend. I really appreciate your help. 

 

Stephen

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 30, 2020

Stephen_Langel 

Stephen, you need to reserve some cell for the text on which you will filter the range. Let say that is $O$1.

 

In M1 enter the formula

=COUNTIF($A1:$L1,$O$1)

 If first row is for the headers, which is much more preferable, when into M2

=COUNTIF($A2:$L2,$O$1)

Keep staying on this cell and in name box type

Enter - column M will be selected

Ctrl+D - formula will be copied in column till end of the range

Select range, apply filter, type something in O1, in column M filter menu uncheck zero.

Resources