Filter for "blanks" in multiple columns simultaneously

  • Thread starter Thread starter confused_in_London
  • Start date Start date
C

confused_in_London

I have a spreadsheet with several columns of data that I need to filter to
show blank cells. Is there a way to apply the autofilter to more than one
column to save me from filtering each individual column?
Thanks
 
Say we have in cols A thru Y. We are interested in blanks. In Z2 enter:

=IF(COUNTIF(A2:Y2,"")=25,25,IF(COUNTIF(A2:Y2,"")=0,0,1)) and copy down

Set the AutoFilter for column Z.

To see which records are all blank, set the filter to 25
To see which records have no blanks, set the filter to 0
To see the records that have some blanks, set the filter to 1
 
I'm not sure why you want to filter to show blanks - cos, you wont be
able to see anything !!

You could make use of a helper column to see if there are any blanks
in any of the cells in that row, eg by putting this in L2:

=COUNTIF(A2:K2,"")

and copying down, and then you could appy a custom filter to that
column for not equal to zero.

Hope this helps.

Pete
 
Back
Top