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

Posted on 2014-07-15
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
    LVL 44

    Accepted Solution

    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

    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 44

    Expert Comment

    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

    oh yes, i finally understand.  thank you!

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now