Excel -- Filters -- AutoFilter Basics Use AutoFilter to hide some of the data in your worksheet. For example, you can focus on sales of a specific product, or print a list of your largest orders. Prepare the Database Filter the Database Remove a Filter Create a Custom Filter

Prepare the Database 1. Set up the database a) The first row (A1:G1) has headings. b) Subsequent rows contain data. c) There are no blank rows within the database.

For a zipped workbook with sample data, click here.

d) There is a blank row at the end of the database, and a blank column at the right.

2. Turn on AutoFilter a) Select a cell in the database. b) From the Data menu, choose Filter, AutoFilter. A dropdown arrow appears beside each column heading.

Filter the Database To filter the list, for example to view orders for one Product, choose a criterion from one of the dropdown lists. To further filter the list, choose from another column's dropdown list, e.g. Customer. Rows that don't meet the criteria will be hidden. Rows that remain visible have a blue number in the row button. The dropdown arrow for column(s) in which a criterion has been applied will also be blue.

Remove a Filter To remove the filter, and leave AutoFilter turned on: In each column in which a filter has been applied, choose (All), the first item in the dropdown list  OR From the Data menu, choose Filter, Show All To remove the current filter, and turn off AutoFilter: From the Data menu, choose Filter, AutoFilter
Special Filters Blank Cells in a Column If there are any blank cells in the column, the drop down list will contain two additional items -- (Blanks) and (NonBlanks).
Filter Highest and Lowest Numbers To find the highest or lowest numbers in the table, choose (Top 10...) from the number column dropdown. 1. In the first box, choose Top or Bottom. 2. In the middle box, enter a number. 3. In the third box, choose Items or Percent. Note: The results are the highest or lowest values for the entire list, not the currently filtered list. If other columns are also filtered, you may see fewer than the specified number of items.

Create a Custom Filter When you choose a criterion from a dropdown list, the list is filtered for rows that are equal to the criterion. If you need more options while filtering, you can choose (Custom...) from the dropdown list. This opens the Custom AutoFilter dialog box.

To filter for one criterion: a) From the first dropdown list, select an operator. b) In the text box, type a value. c) Click OK.

To filter for two criteria: a) From the first dropdown list, select an operator. b) In the text box, type a value. c) Choose And or Or d) From the second dropdown list, select an operator. e) In the text box, type a value. f) Click OK.

For a zipped workbook with sample data, click here.
AutoFilter Basics AutoFilter Tips AutoFilter Programming

Home     Excel Tips     Excel Files      Blog    Contact
		The Excel Store	Debra Dalgleish