Excel filter:  Show all rows where column 1 text contains any of 5 different words

Posted on 2014-07-15
Medium Priority
Last Modified: 2014-07-15
I have a spreadsheet with thousands of rows.   I want to show only those rows where Column A contains any of the following words (not case sensitive):   cat, dog, bird, pigeon, lizard

I have figured out how to make a text filter, but it only allows me to designate 2 words.  I'm not sure how to do it for 5 words.  Thanks!
Question by:arthurh88
  • 2
  • 2
LVL 46

Accepted Solution

aikimark earned 2000 total points
ID: 40197415
for that, you should use AdvancedFilter.  You create a criteria range with the values you want and then invoke the advanced filter dialog, which should be a menu item below the regular filter you are currently using.  The column to be filtered needs a column header.  In the example below, I'm using "Animal" as the header.

Open in new window

Note: You can also invoke this programmatically.

I have a video tutorial introducing the viewer to the AdvancedFilter feature:

Author Comment

ID: 40197439
i really do not know how to do it based on what you said.  i clicked "advanced filter" and I have only 2 boxes:  list range and criteria range, and I'm not sure where to put my animals
LVL 46

Expert Comment

ID: 40197455
The criteria range is a a set of cells with a header row, containing one column for every column you will be filtering.  In your case, there is only one column.  Below that header are values or expressions for use by the advanced filtering engine.  In included a sample criteria range configuration for your use.  Copy/paste those cells into some part of your workbook and select them after you have selected the criteria range text area in the dialog.

Also, you can use your F1 key, watch my video, or do an internet search to find other examples of applying and using AdvancedFilter to filter your data.

Author Comment

ID: 40197463
oh yes, i finally understand.  thank you!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question