How to Filter Microsoft Excel Data Quickly using Keyboard Shortcuts
by Avantix Learning Team | Updated January 23, 2017
Applies to: Microsoft® Excel® 2010, 2013, 2016 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. Turn 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. Display 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. Select 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. Clear 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.
5. Clear 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. Filter 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. Sort 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. Filter 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. Display 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. Filter 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.
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 2019 Avantix® Learning Inc.
You may also like
How to Easily Show or Hide Formulas in Microsoft Excel (3+ Ways)
You can easily show or hide formulas in a number of ways in Microsoft Excel. You can use a keyboard shortcut, click a button and even use a formula to show formulas …
How to Create 3D References in Microsoft Excel
You can use 3D references in Excel formulas to calculate across multiple worksheets that are structured in the same way. A 3D reference refers to the same cell or range of cells on multiple worksheets.
How to Freeze Row and Column Headings in Microsoft Excel
As many Excel worksheets can become quite large, it can be useful to freeze row and column headings so that they are locked in place when you scroll through your worksheet. In Excel, you can freeze both row headings and column headings or just one.
Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.
Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca
