How to Filter Microsoft Excel Data Quickly using Keyboard Shortcuts
by Avantix Learning Team | Updated July 29, 2020
Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows)
You can turn on filtering (formerly known as auto filtering) for Microsoft Excel lists and tables and easily filter and sort data using a mouse. When you first turn on filtering, arrows appear in the header row for each field with a drop-down menu.
Although most users will use the mouse to apply and remove filtering, you can also use your keyboard. The shortcuts are available in Excel 2010 and later versions but some will also work in 2007.
Recommended article: How to Enter Data in an Excel Filtered List into Visible Cells [2 Ways]
The following are 10 useful keyboard shortcuts for filtering.
1. Turning filtering on or off
To turn filtering on or off, ensure a cell in the range is selected and then press Ctrl + Shift + L.
Down arrows will appear beside field names in the header row as follows:
If your data range contains any blank columns or rows, select the entire range of cells first.
If you have converted a list to a table, the Filter menus should automatically appear.
2. Displaying the Filter menu
To display the Filter menu for a column:
- Select a cell in the header row that contains a Filter arrow.
- Press Alt + down arrow to display the Filter menu for the column.
If your data has been converted to a table, you can press Alt + Shift + down arrow in any cell in the table to display the Filter menu for that column.
3. Selecting menu items using arrow keys
Once the Filter menu is displayed, you can use your arrow keys to navigate the menu and use Enter and the Spacebar to select and apply filtering:
- Press the up or down arrow keys to select a command.
- Press Enter to apply the command.
- Press the Spacebar to check or uncheck a checkbox.
4. Clearing all filters
To clear all filters for all fields in the current filtered range, select a cell in the range and press Alt > A > C.
Don’t press Shift and don’t press these keys at the same time as they access the Ribbon. Simply press Alt, then A, then C.
You can also press Ctrl + Shift + L t turn filtering off which will remove the filters. Press Ctrl + Shift + L to turn filtering on again.
5. Clearing filters in a column
To clear the filters in a column:
- Select a cell in the header row and press Alt + down arrow to display the Filter menu for the column.
- Type the letter “C” to clear the filter.
6. Filtering by typing underlined characters
Once the Filter menu is displayed, you can type underlined letters to select a filter option. The underlined letters that appear in the menu are the shortcut keys for each command. For example, typing the letter “F” would display the Text, Number or Date Filters sub-menu. Don’t press Shift while typing these characters.
Note the underlined characters in the menu below:
7. Sorting by typing underlined characters
Once the Filter menu is displayed, you can type characters to sort (do not hold down Shift):
- Type “S” to sort in ascending order.
- Type “O” to sort in descending order.
- Type “T” to sort by color.
8. Filtering using the Search box
Starting in Excel 2010, a Search box was added to the Filter menu. You can enter search criteria in the Search box and Excel will automatically filter in the column.
To use the Search box:
- Select a cell in the header row and press Alt + down arrow to display the Filter menu for the selected column.
- Type the letter “E” to jump to the Search box where you can type your criteria.
Note the Search box in the menu below:
9. Displaying the Custom Filter dialog box
When you want to filter using custom criteria, you can display the Custom Filter dialog box:
- Select a cell in the header row and press Alt + down arrow to display the Filter menu for the column.
- Type the letter “F”.
- Type the letter “E” (this displays the Custom Filter dialog box which sets the comparison operator to Equal).
- Select options from the menus (such as Equal, Not Equal, etc.) and enter criteria.
- Select And or Or.
- Press Enter to apply the filter.
Enter criteria in criteria boxes in the Custom Filter (AutoFilter) dialog box below:
In the Custom Filter dialog box, you can keep pressing Tab to select different options in the dialog.
10. Filtering blanks or non-blanks
To filter to display blank cells in the selected column:
- Press Alt + down arrow to display the Filter menu for the column.
- Type the letter “F”.
- Type the letter “E” (this displays the Custom Filter dialog box which sets the comparison operator to Equal).
- Press Enter.
To filter to display non-blank cells in the selected column:
- Press Alt + down arrow to display the Filter menu for the column.
- Type the letter “F”.
- Type the letter “N” (this displays the Custom Filter dialog box which sets the comparison operator to Does Not Equal).
- Press Enter.
If you use filtering a lot, these keyboard shortcuts can save you quite a bit of time.
Subscribe to get more articles like this one
Did you find this article helpful? If you would like to receive new articles, join our email list.
Related
How to Use Flash Fill in Excel to Clean or Extract Data (Beginner’s Guide)
How to Quickly Fill in Missing Values from the Cell Above in Excel
How to Delete Blanks Rows in Excel (Great Strategies,Tips and Shortcuts)
Recommended Microsoft Excel training
Microsoft Excel: Intermediate / Advanced
Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools
Microsoft Excel: Introduction to Visual Basic for Applications (VBA)
Our instructor-led classroom training courses are delivered at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some courses may also be delivered at an alternate downtown Toronto location). Contact us if you’d like to arrange custom training at your office on a date that’s convenient for you.
To request this page in an alternate format, contact us.
Copyright 2020 Avantix® Learning
You may also like
How to Delete Blank Rows in Excel (Great Strategies, Tricks and Shortcuts for All Versions)
You can delete blank rows in Microsoft Excel worksheets manually or you can use several tricks to delete multiple blank rows quickly. First, it’s essential that Excel users know how to select rows quickly using shortcuts or by using Excel commands such as Go to Special, Filter and Sort.
How to Move a Pivot Table in Excel
Moving a pivot table is not as simple as moving other objects in an Excel worksheet or workbook. You will typically need to use the Move PivotTable command in the Ribbon to move a pivot table to a different area on a worksheet or to a different sheet in the same workbook.
How to Hide Rows and Columns in Excel (including Unused Rows and Columns)
You can hide rows and columns in Excel using the context menu, using a keyboard shortcut or by using the Format command on the Home tab in the Ribbon. If you have hidden rows or columns, you can unhide them as well.
Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca
