Search Contextures Sites

Contextures
Excel news
by email

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

30 Excel Functions in 30 Days

 

 

 

Learn how to create Excel dashboards.

 

 

 

Learn how to create Excel dashboards.

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

30 Excel Functions in 30 Days

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

Excel Filters: AutoFilter Basics

Use an AutoFilter to hide some of the data in your worksheet. For example, you can focus on sales to a specific customer, or print a list of your largest orders.

Download a zipped workbook with AutoFilter sample data.

Note: For Excel 2003 AutoFilter instructions, please go to Excel 2003 AutoFilter Basics

Create an Excel Table

1. Set up the Excel Table

In Excel 2007, and later versions, you should create an Excel Table from your data, and use its built in filtering features. If you are using an Excel Table, you can skip to the next section, Filter One Column.

If your data is not in an Excel Table, follow these steps to prepare your list for an AutoFilter.

  1. Add headings: In the row directly above the data, enter a heading for each column. In the example shown here, the data starts in row 2 of the worksheet, and the headings are in row 1.
  2. No blank rows or columns: You can leave blank cells in a row or column but make sure that there are no completely blank rows or columns within the database.
  3. Separate: The ideal setup is to store your database on a worksheet where there is no other data. If that's not possible, keep the database separated from other data on the worksheet, with at least one blank row at the end of the database, and a blank column at the right. If the database doesn't start in cell A1, ensure that there is a blank row above, and a blank column to the left.
  4. Excel Database

2. Turn on Filtering

In Excel 2007 and later, if you're using an Excel Table, it has built-in filtering features. If your data is not in an Excel Table, follow these steps to add an AutoFilter.

  1. Select a cell in the database.
  2. On the Excel Ribbon, click the Data tab, and then click Filter.

    Excel AutoFilter 02

A dropdown arrow appears at the right side of each column heading.

Excel AutoFilter 02

Filter One Column

With the Excel Table, or AutoFilter, in place, you can start filtering the individual columns. You can filter on just one column, such as Customer, or two or more columns.

For example, to filter for a specific customer, follow these steps:

  1. Click the drop down arrow at the right of the Customer heading
  2. In the list of Customers, click Select All, to remove all the check marks
  3. Click the check box for the customer that you want to filter for, such as MegaStore.
  4. Click OK, to see the filtered list.

autofilter customer

In the filtered worksheet, you'll see some changes:

  • only the rows for the selected customer are visible -- the other customer rows have been hidden
  • the row numbers for the filtered rows are shown in blue font.
  • the drop down arrow in the Customer column has changed to a filter symbol, with a small arrow

autofilter customer

Filter Two Columns

After you have filtered one column, you can refine the filtered results, by filtering two or more columns.

For example, after you filter for a specific customer, follow these steps to add a product filter:

  1. Click the drop down arrow at the right of the Product heading.
    Note: When another column is filtered, you might not see the full list of products -- you'll only see the products for the filtered records.
  2. In the list of Products, click Select All, to remove all the check marks
  3. Click the check box, or multiple check boxes, for the product(s) that you want to filter for, such as Bananas and Grapes.
  4. Click OK, to see the filtered list.

autofilter customer

Clear an AutoFilter

After you have applied one or more filters, you can clear a specific filter, or all the filters in the Excel Table.

Clear a Specific Filter

To clear a specific filter, and leave other filters turned on:

  1. Click the drop down arrow in the column heading where you want to clear the filter
  2. Click on Clear Filter From [column name]

    clear filter

Clear All Filters

Instead of clearing a specific filter, you can clear all the filters from the Excel Table, in one step:

  1. Select a cell in the table.
  2. On the Excel Ribbon, click the Data tab, and in the Sort & Filter group, click Clear.
    Note: This will also clear the Sort options that you have applied.

    clear all filters

Filter for Blank Cells

If there are any blank cells in a column, the drop down list in the heading cell will show one additional item -- (Blanks) -- at the end of the item list.

To show only the rows with blank cells in that column, remove the check marks from the other items in the list.

clear all filters

Filter for Highest and Lowest Values

If a column contains numbers, you can filter to find the highest or lowest numbers that column. In this example, you'll filter for the 5 lowest values in the Total column.

  1. Click the drop down arrow in the column heading, click Number Filters, and then click Top 10.
    Note: If fewer than half of the values in the column are numbers, the Number Filters option will change to Text Filters

    clear all filters

  2. In the Top 10 AutoFilter window, select Bottom from the first drop down list.
  3. In the second box, type 5, or use the arrows to change the number to 5
  4. In the third drop down box, select Items, then click OK.

    clear all filters

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.

Excel AutoFilter 02

Filter for a Specific Date Range

If a column contains dates, you can filter to find a specific date or date range, or a dynamic date range. In this example, you'll filter for the orders received in January 2011.

  1. Click the drop down arrow in the column heading, click Date Filters, and then click Between.

    clear all filters

  2. In the Custom AutoFilter dialog box, enter the starting date for the date range -- 01-Jan-2011 -- in the first date box
  3. Enter the ending date for the date range -- 31-Jan-2011 -- in the second date box, and click OK

    clear all filters

Filter for a Dynamic Date Range

If a column contains dates, instead of finding a specific date range, you can filter for a dynamic date range, such as Last Week, Next Month, or Tomorrow. In this example, you'll filter for the orders received in January 2011.

  1. Click the drop down arrow in the column heading, click Date Filters, and then click Yesterday.

    clear all filters

  2. The filtered table shows only the orders that were received yesterday.

    clear all filters

Update an AutoFilter

When you open the workbook at a later date, the AutoFilters don't refresh automatically. For example, if you used a dynamic date filter, such as Yesterday, the correct day's results won't show in the filtered table when you open the file the next day. Or, if new data was added to the table, it won't be automatically filtered.

To see the updated filtered results, you can reapply the filter.

  1. Select any cell in the filtered table
  2. On the Excel Ribbon, click the Data tab, and in the Sort & Filter group, click Reapply.

    clear all filters

This will update any dynamic filters in the table, and will also revise the results for any new or changed data.

clear all filters

Note: You could create a macro to update the filter, and set it to run when the file opens.

Create a Custom Filter

Some of the AutoFilter options in the drop down lists end with three dots, such as Begins With...

clear all filters

These filters, as well as the Custom Filter option, open the Custom AutoFilter window. In there, you can enter one or two criteria, to create a customized filter.

To filter for one criterion:

  1. From the first dropdown list, select an option, such as "begins with".
  2. In the text box, type a value, then Click OK.

In the screen shot below, the Custom Filter will find records where the customer name begins with "M".

clear all filters

To filter for two criteria:

  1. From the first dropdown list, select an option, such as "begins with".
  2. In the text box, type a value, then Click OK.
  3. Select "And" or "Or" as the operator
  4. From the first dropdown list, select an option, such as "ends with".
  5. In the text box, type a value, then Click OK.

In the screen shot below, the Custom Filter will find records where:

  • the customer name begins with "M"
  • OR the customer name ends with "Shop".

clear all filters

Download a Sample File

Click here to download a zipped workbook with AutoFilter sample data.

More AutoFilter Tutorials

  1. Excel 2007 AutoFilter Basics
  2. Excel 2003 AutoFilter Basics
  3. Excel AutoFilter Tips
  4. Excel AutoFilter Programming

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.