In our recent article about simple Excel PivotTables we made a passing reference to the Data | Filter command for lists. Here’s how it works.
Take any Excel worksheet that has lists with headings on the top row. Go to the Data tab and click the Filter button. This adds extra pull-down menus to each of the headings.
Under those pull-down menus are options to sort and filter the list.
Sort – ascending or descending
Sort by … – leads to the Custom Sort dialog for multi-column sorting rules.
Clear Filter – removes all filters and displays all rows.
Text Filters – make filtering expressions: Equals, Does not Equal, Begins with, Ends with, Contains, Does not Contain, Custom Filter.
Number Filters – Equals, Does not Equal, Greater Than, Greater than or equal to, Less Than, Less Than or Equal to, Between, Top 10, Above Average, Below Average, Custom Filter.
Then there’s a list of all the items in the list, you can check/uncheck the rows to show.
Any filtered column gets a little ‘funnel’ icon on the top row.