Discover the most wanted Excel filter shortcut list! Apply or remove filtering and sort your data through useful examples.
This article will explain how to use shortcuts for filtering and displaying data in Excel.
Table of contents:
- Turn filtering on or off
- Filter menu
- Filter Menu Navigation using arrow keys
- Remove all filters
- Clear filters in a column
- Drop-down menu shortcuts using underlined letters
- Quick Sorting Shortcuts
- Search box
- Display the Custom AutoFilter dialog box
- Shortcut keys for Text Filters
- Shortcut keys for Number Filters
How to Filter Data Using Keyboard Shortcuts
If you work with lists or a table, you can apply filter shortcuts or move the mouse over the table header row and filter like a drop-down list.
The Filter shortcut in Excel is a handy feature for anyone who regularly works with data in spreadsheets.
Here are some reasons why:
- Fast: With the Filter shortcut, you can quickly sort through and manage large data sets without manually searching information, which can be very time-consuming.
- Built for data analysis, it allows you to focus on specific segments of your data by hiding rows that do not meet certain criteria. This is extremely helpful for analyzing data patterns or for comparison purposes.
- Reduce errors: By filtering data, you reduce the risk of errors from manually searching for partial data sets. It ensures that the data you view is consistent with your set criteria.
- Dynamic visualization: Filters can display only the data relevant to a specific chart or graph.
- Dynamic data management: Filters are dynamic. Adding or removing data from your dataset allows you to easily reapply filters to update your views and analyses to reflect the new information.
- Multi-criteria filtering: You can simultaneously apply filters to multiple columns, enabling complex criteria for more advanced data segmentation.
- Customization: Custom filters work with specific data ranges or criteria, including text, number, date filters, and even custom formulas.
How to Turn filtering on or off
Select a cell in a range and use the Ctrl + Shift + L keyboard shortcut.
Tip: Convert the range to an Excel table using the Ctrl + T insert table shortcut, and the filter will appear automatically.
Another way to activate the filter:
Select the Data tab. Under the Sort & Filter Group, click on the Filter icon.
How to show the Filter menu
Steps to show the Filter menu in Excel:
- Select the header row, and locate the column where you want to apply the filter.
- Locate the down arrow.
- Use the Alt + down arrow shortcut to display the Filter menu for the selected column.
Filter Menu Navigation using arrow keys
Navigating between options in the filter menu is easy using the arrow keys. In the example, you want to apply number filters. First, press the down arrow key to select the Number Filters option. Now press the right arrow key once to navigate to the ‘Greater than or equal to‘ logical operator.
Finally, press Enter.
Remove all filters
In the following example, you have more than one filter. How to remove all?
Shortcut to remove all filters:
Alt, A, C
How to clear filters in a column
Steps to clear the filter in a single column:
- Select the column header
- Press the Alt + down arrow key
- The Filter menu appears for the selected column
- Press C to clear the filter
Drop-down menu shortcuts using underlined letters
Excel provides built-in shortcut keys (underlined letters) to apply various filter commands in the Filter menu. To use filter shortcuts in the drop-down menu, press the Alt + down arrow key.
These are the following:
- S – Sort data A to Z
- O – Sort Data Z to A
- T – Sort by Color using the Custom Sort option
- C – Clear all filter
- I – Filter by Color (when available)
- F – Number / Text Filters (it depends on cell value)
- E – Enter a filter value manually (search bar)
For example, type “I” to filter data by color. This option is available only if your range contains color(s).
Quick Sorting Shortcuts
In Excel, there are various sorting methods. However, the fastest way is using the following shortcuts:
- Press the Alt + down arrow, then ‘S’ to sort your data in ascending order.
- Press Alt + down arrow, then ‘O’ to sort your data in descending order.
Two options are available, depending on the type of data you want to sort.
Tip: In the example, we use the Profit columns for sorting purposes. In column E, the data type is number, so you’ll see the ‘sort largest to smallest‘ command. In the case of text data, you’ll see the ‘sort Z to A‘ function.
Search box
Excel uses the Filter menu Search box to provide custom filter options. In the example, we are working with text data in column B.
Start typing your search criteria, and Excel filters the selected column.
How to display the Custom AutoFilter dialog box
Shortcut to open the custom filter dialog box:
- Select the column in the header where you want to apply the filter
- Press the Alt + down arrow key
- Release the keys and press F, then E, to display the Custom AutoFilter dialog box
Enter criteria for the Profit column. In this case, we want to show only values greater than or equal to 2500. Click OK to filter the data.
Shortcut keys for Text Filters
If you are working with text values, use the following filter shortcuts that you can use after opening the Custom Filter dialog box:
- Equals: Alt + ↓ + F + E
- Does Not Equal: Alt + ↓ + F + N
- Begins with: Alt + ↓ + F + I
- Ends with: Alt + ↓ + F + T
- Contains: Alt + ↓ + F + A
- Does not contain: Alt + ↓ + F + D
- Custom Filter: Alt + ↓ + F + E
Shortcut keys for Number Filters
In the case of numerical values, use these shortcuts after opening the Custom Filter dialog box. I’ll list only the differences in text values:
- Greater Than: Alt + ↓ + F + G
- Greater Than Or Equal To: Alt + ↓ + F + O
- Less Than: Alt + ↓ + F + L
- Less Than Or Equal To: Alt + ↓ + F + Q
- Between: Alt + ↓ + F + W
- Top 10: Alt + ↓ + F + T
- Above Average: Alt + ↓ + F + A
- Below Average: Alt + ↓ + F + B
It was our definitive guide on using the filter shortcuts in Excel. Stay tuned.