Excel is useful for managing large amounts of data, leading many professionals to work with spreadsheets that contain hundreds of rows. Filters help you sort information based on criteria you set, and shortcuts can make your workflow even more efficient. Learning different filter shortcuts allows you to sort your data faster and keep your spreadsheets organized. In this article, we share seven Excel filter shortcuts and discuss their benefits.
Recommended
Benefits of using filter shortcuts in Excel
Using filter shortcuts in Excel can benefit you by:
-
Making it easy to sort your data: When you have large amounts of data, it can be helpful to use filters to sort it by the criteria you determine.
-
Streamlining your workflow: You can trigger filtering options by using your mouse in the ribbon bar, but keyboard shortcuts are much quicker and can save you a lot of time.
-
Offering in-program instructions: If you forget a specific filter shortcut, you can hover your cursor over the corresponding button in the top ribbon to view the keys you’re supposed to press and remember the shortcut for future use.
7 Excel filter shortcuts
Here are seven keyboard shortcuts that help you filter in Excel:
1. Turn filters on and off
If you have a large set of data, you might want to add filtering capabilities to each column. You can accomplish this task by selecting any cell in your data range. Then, press Ctrl Shift L on your keyboard to add filters to each cell in the top row. If you want to remove these filters, just select any cell in the table and press Ctrl Shift L again on your keyboard.
2. View a filter’s drop-down menu
When you apply filters to a table, drop-down icons appear in each cell in the header row. Click on one of these cells that contains a drop-down icon. Press the “Alt” and down arrow keys on your keyboard to open the filter menu. If you’re using Excel Tables, you can press “Shift,” “Alt” and the down arrow with any cell selected to open the filter menu for the cell’s respective column.
3. Manage the filter drop-down menu
With a filter’s drop-down menu open, you can use various keyboard shortcuts to manipulate your data. Many users press the up and down arrows on their keyboard to navigate to each item. When you reach the task you want to perform, you can press “Enter.”
If you want to use fewer keystrokes, consider referencing the underlined letters in the drop-down menu. For instance, in the “Sort A to Z” menu item, the “S” is underlined. When you press S on your keyboard with the filter drop-down menu open, the program automatically sorts the data in that column alphabetically. You can sort data in reverse alphabetical order by pressing “O” on your keyboard, since “O” is underlined in the menu item “Sort Z to A.”
4. Check and uncheck filter items
If you want your table to only display cells that meet certain criteria, you use the down arrow key to navigate to the list of items near the bottom of the drop-down menu. You can also go directly to the checkbox list by pressing “E” and “Tab.” Use the up and down arrows to navigate between items in this list. When you highlight a checked box, press the space bar to uncheck it. Similarly, you can check unchecked boxes by pressing the space bar. Once all the appropriate boxes are checked, press “Enter” to apply the filters.
Checkbox lists can be fairly long if your table has lots of data, but other shortcuts make the information easy to navigate. For instance, you can use the “Page Up” and “Page Down” buttons on your keyboard to jump to the first and last items in view. The “Home” key jumps to the first item in the list, and the “End” key jumps to the last item in the list. If you want to use the search bar to find an item in the list, press “E” and type your search query.
5. Clear all filters in a column
Open a column’s drop-down menu by pressing “Alt” and the down arrow on your keyboard. You could clear all filters in the column by pressing “E” and “Tab” to go directly to the checkbox list and pressing the space bar to uncheck the “(Select All)” field. Alternatively, you can just press “C” when the drop-down menu is open to clear all filters.
6. Clear all filters
Unlike the other shortcuts we’ve discussed so far, it’s not necessary to open a column’s drop-down menu before clearing all filters in a filtered range. Select any cell in the range and pressing “Alt,” “A” and “C” at the same time. This action removes any filters you applied to the table and restores it to its original state.
7. Filter blank or non-blank cells and rows
Press “Alt” and the down arrow to open a column’s drop-down filtering menu. Press “F” to open the “Text Filters” sub-menu. Press “E” to open the “Custom AutoFilter” dialog box. The search terms are blank by default, so you can press “Enter” to filter the column for blank cells.
If you want to filter for non-blank cells, follow the steps above but press “N” instead of “E.” You complete this action because “N” corresponds with the “Does Not Equal” function. When the “Custom AutoFilter” dialog box opens, you can press “Enter” since the fields are already blank.
I hope you find this article helpful.
