I have a large table that looks like this:

ID      String
1       abcde
2       defgh
3       defgh
4       defgh
5       ijkl
6       ijkl
7       mnop
8       qrst

I want to selectivley hide rows by populating a list of filterd values. For example, I'd like to filter out (hide) all rows that contain 'ef', 'kl', and 'qr'. Is there an easy way to do this?

I know how to use Advanced filters to include only the rows that contain those substrings, but not the inverse. Has anyone does this before?

  • There is a "does not contain" option under autofilter, but this only permits two custom settings.
    – variant
    Aug 12, 2011 at 18:00
  • This is exactly what I need, but for a variable length of strings. Two is insufficient for this application. Aug 12, 2011 at 18:02

3 Answers 3


You can still use the Advanced Filters. Here's how you can use it for your problem:

Single Criterion

Here's a way to exclude or hide cells that contain the substring "ef."

enter image description here


enter image description here

This is the formula in A13:


which works the same as: =ISERROR(SEARCH("ef",B2))

Note the usage of absolute and relative references. The 2nd reference (B2) points to the first item in your data range; in order for the filter to work, it needs to be relative. The reference that points to the text substring that you don't want to include needs to be absolute.

Also, the Criteria range needs to have a blank header; although you have include it when setting up your advanced filter (see the Criteria range field in the screenie).

Multiple Criteria

This example hides cells that contain the substrings "ef", "j" and "rs."

enter image description here


enter image description here

Here's the formula in A13 this time:


or you could use:

  • Bah! Imgur ruined the screenie :(
    – Ellesa
    Aug 12, 2011 at 19:25
  • Thanks for the awesome screens, however I need to do the exact opposite of this. I want to hide rows in the table based on a criteria range, not include them. Any idea on how to invert this? Aug 12, 2011 at 19:28
  • Updated my answer. Sorry if the screenies are ugly.
    – Ellesa
    Aug 12, 2011 at 20:30

One easy and wholly inelegant way I can think of is setting up another column to check if the value in your target column contains your filter strings.

Your formula would look something like


You could then autofilter on 0.

  • This is close - if there was a way to say FIND(C:C,B2) and have it return true if any of the string in column C were present in B2 I would be all set. Aug 12, 2011 at 18:08
  • @Sam Johnson, You can do that with an array formula. =IF(SUM(IFERROR(FIND($C:$C,B2),0))=0,0,1) entered by pressing Ctrl+Shift+Enter. Use that formula in Row 2, then fill down.
    – Excellll
    Aug 15, 2011 at 13:48

I know your question dates more than one year, but you can use the conditional formatting if you havent figured it out yet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.