Filter for "blanks" in multiple columns simultaneously

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
 
G

Gary''s Student

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
 
P

Pete_UK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top