Excel 2007 : filter: List all rows with word "lion" any place?

[rohed]

How do I manually filter (show) all rows & columns that have a text word such as "lion"? I can do it for a particular column but not for everywhere.

[NBVC]

Add a helper column with a formula like:

=COUNTIF(A2:C2,"*lion*")

this counts how many cells in A2:C2 contain the word lion within the strings.

adjust the range to the correct width, then copy down.

Then filter this column for Greater Than >> 0

[rohed]

This works, I didn't think of doing it that way, but was wondering if it can be done with a filter?