Key Skills > Filters > AutoFilters
Excel AutoFilter Basics
Use an Excel 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
|

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.
Note: For Excel 2003 AutoFilter instructions, please go to Excel 2003 AutoFilter Basics
If your data is not in an Excel Table, follow these steps
to prepare your list for an AutoFilter.
- 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.
- 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.
- 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.

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.
- Select a cell in the database.
- On the Excel Ribbon, click the Data tab, and then click Filter.

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

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:
- Click the drop down arrow at the right of the Customer heading
- In the list of Customers, click Select All, to remove all the
check marks
- Click the check box for the customer that you want to filter for,
such as MegaStore.
- Click OK, to see the filtered list.

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

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:
- 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.
- In the list of Products, click Select All, to remove all the check
marks
- Click the check box, or multiple check boxes, for the product(s)
that you want to filter for, such as Bananas and Grapes.
- Click OK, to see the filtered list.

Clear an AutoFilter
After you have applied one or more filters, you can:
To save time, you can
Clear a Specific Filter
To clear a specific filter, and leave other filters turned on:
- Click the drop down arrow in the column heading where you want
to clear the filter
- Click on Clear Filter From [column name]

Clear All Filters
Instead of clearing a specific filter, you can clear all the filters
from the Excel Table, in one step:
- Select a cell in the table.
- 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.

Add Clear All Button to QAT
If you spend lots of time clearing filters, make that task easier - put the Clear All button on your Quick Access Toolbar (QAT). This video shows the steps.
Filter With Search Box
Instead of scrolling through a long list of items, when filtering an Excel table column, use the Search Box feature, to find specific text strings.
Watch this short video to see how to filter a column by using the built-in Search Box. This feature was introduced in Excel 2010. There are written steps below the video.
Use the Search Box
In this example, we'll use the Search Box to look for product names that contain "am".
To use the Search Box when filtering, follow these steps:
- Click the drop down arrow at the right of the column heading where you want to filter
- In the Search Box, type one or more characters that you want to filter for -- in the screen shot below, "am" is in the Search Box
- As you type, the list of product names will automatically filter, to show the matching items
- When the list shows the items that you want, click OK, or press Enter, to apply the filter on the worksheet
- OR, click Cancel, or press the Esc key, to close the filter drop down, and return to the worksheet, without applying a filter

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.

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.
- 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

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

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.
Filter for Conditional Formatting Colour
In addition to filtering for text or numbers, you can filter by the
cell fill colour, even if that colour was applied by conditional formatting.
- Click the arrow in the heading for the column where you applied
the conditional formatting
- In the drop down, click Filter by Color, and select the color
that you used filter by conditional format color

Filter for Date Range Colour
You can filter a list of dates for a date range, by using the Between date filter, as shown in the next section. With that method, you'll have to type the dates in the filter settings, each time you want to use a different date range.
This video shows how to add Conditional Formatting to the date cells, so the font colour changes slightly, based on the start and end dates entered on the worksheet. Then, you can use the Filter by Color option, to show only the dates in the specified range.

This technique is the second method shown in this video, starting at the 2:00 mark
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.
- Click the drop down arrow in the column heading, click Date Filters,
and then click Between.

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

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.
- Click the drop down arrow in the column heading, click Date Filters,
and then click Yesterday.

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

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.
- Select any cell in the filtered table
- On the Excel Ribbon, click the Data tab, and in the Sort &
Filter group, click Reapply.

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

You could create a macro to update the filter, and set it to run
when the file opens.
Delete Filtered Rows
After you apply a filter, you can delete the filtered rows.
- Make a backup copy of your file first -- just to be safe.
- In the filtered table, select the cells in one of the columns
- On the Ribbon's Home tab, click the arrow under the Delete command
- Click on Delete Table Rows.

Check the Results
As soon as you delete the rows, clear the filter, and check the results:
- Click the filter arrow in the column heading, and click the Clear
Filter command

- Then, check that all the other rows are still okay, and the colored
cells have been deleted
- If the list doesn't look right, click the Undo button a couple
of times, or press Ctrl + Z to undo the deletion.
Create a Custom Filter
Some of the AutoFilter options in the drop down lists end with three
dots, such as Begins With...

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:
- From the first dropdown list, select an option, such as "begins
with".
- 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".

To filter for two criteria:
- From the first dropdown list, select an option, such as "begins
with".
- In the text box, type a value, then Click OK.
- Select "And" or "Or" as the operator
- From the first dropdown list, select an option, such as "ends
with".
- 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".

Get the Excel Files ✅
More AutoFilter Tutorials
Excel AutoFilter Tips
Excel AutoFilter Macros
Filter Right-Click Macros
Filter to Different Sheet