Filter Excel Data Faster with These 15 Keyboard Shortcuts

In Excel, a filter can hide information that’s not currently needed while letting you work with highly relevant, targeted data. If you work with large data lists, you know how important it is to be able to work with a subset of your data to support analysis and decision making.

While most people use a mouse to select commands on Excel’s ribbon and various dialog boxes to filter data, you can also use keyboard shortcuts. Ask any experienced spreadsheet user and they’ll tell you that keyboard shortcuts are the key to being more productive in Excel!

Many of the shortcuts that we’ll talk about in this article are displayed when you hover your mouse pointer above commands on the ribbon in Excel.

What is a Data List?

The shortcut keys described in this article will only work with data lists or tables in Microsoft Excel. If you’re not sure what a data list looks like, check out the screenshot (below).

A data list organizes information into rows and columns.

The top row in a data list contains column headings (field names). Each column represents a field, each row represents a record. The entire list constitutes a database in Excel.

Without any further ado, here are 15 keyboard shortcuts to help you filter data faster in Microsoft Excel, organized by task.

Turn Filters On or Off

Press Ctrl + Shift + L to toggle filters (formerly AutoFilter) on or off in an Excel data list.

Here is what you do.

1. Click a cell within your data list.
2. Press Ctrl + Shift + L to toggle filters on.
3. Press Ctrl + Shift + L to toggle filters off.

The screen capture (below) shows my data list with filtering enabled. You can see buttons beside each of the column headings.

Convert a Data List to an Excel Table

If you would rather work with your data in an Excel table, press Ctrl + T.

Here’s the process.

1. Click a cell within your data list.
2. Press Ctrl + T to display the Create Table dialog box.
3. Verify that the cell addresses represent the boundaries of your data list under Where is the data for your table?
4. Click OK.

Excel tables come with a lot of features that make working with databases easier. The most noticeable difference is how tables are formatted. The table below shows the header row formatted in dark blue with alternating shading applied to rows in the data list.

Display the Filter Menu

After you enable filtering, you can display the filter menu by pressing Alt + Down Arrow.

1. Navigate to a column heading.
2. Press Alt + Down Arrow to display the filter menu.

In this example I displayed the filter menu for the Item column. This column heading is in cell B3.

Select Menu Items with the Arrow Keys

You can navigate within the filter menu using the Arrow keys, Enter, Esc, and the Space bar.

Let’s filter the list to focus on Cake.

1. Navigate to Item column heading in cell B3.
2. Press Alt + Down Arrow to display the filter menu.
3. Press the Down Arrow until (Select All) is highlighted then press Space to clear the checkbox.
4. Press the Down Arrow again then press Space to select Cake.
5. Press Enter to apply the filter.

Select or Deselect Checkboxes with the Space Bar

The Space Bar lets you select or deselect checkboxes.

In the previous example, we tapped Space to clear the (Select All) checkbox then we tapped Space to select Cake.

Select Menu Items with Accelerator Keys

Many commands in the filter menu contain underlined letters called accelerator keys. Accelerator keys let users select commands without using a mouse to navigate the graphical user interface (GUI) in Excel.

Here are the steps to sort our list by date, in descending order.

1. Navigate to Date column heading in cell A3.
2. Press Alt + Down Arrow to display the filter menu.
3. Press O to select Sort Newest to Oldest.

To use commands with accelerator keys, just look for the underlined letter.

Important: Remember, you have to press Alt + Down Arrow to display the filter menu before you can access these commands.

Here is a list of commands in the filter menu with accelerator keys.

Use the Search Box

Microsoft added the Search Box when they released Excel 2010. The search box lets you narrow down the available options for filtering.

Here’s what I did in this example.

1. Navigate to Item column heading in cell B3.
2. Press Alt + Down Arrow to display the filter menu.
3. Press E to select the search box.
4. Type coo to display Cookies in the list.
5. Use the arrow keys and Space to select Cookies then press Enter.

Display the Custom Auto-Filter dialog box

If your filtering requirements are more complex, you can use the Custom Auto-Filter dialog box.

Here are the steps to limit records in our data list to items with a quantity greater than 50.

1. Navigate to Qty column heading in cell C3.
2. Press Alt + Down Arrow to display the filter menu.
3. Type F to select Number Filters.
   A second panel opens.
4. Type F to select Customized Filter.
5. Select is greater than from the drop-down menu and type 50 in the appropriate boxes in the Qty section then press Enter.

If you’re committed to using keyboard shortcuts, the Tab and Shift + Tab key combinations can be used to navigate from one field to another in the Custom Auto-Filter dialog box.

Filter Blank or Non-Blank Cells

The Custom Auto-Filter dialog box can be used to display or hide blank cells, assuming you have any in your list.

Here is how to display all the blank cells in your list. Let’s assume a couple of the records don’t have prices.

1. Navigate to Price column heading in cell D3.
2. Press Alt + Down Arrow to display the filter menu.
3. Type F to select Number Filters.
   A second panel opens.
4. Type F to select Customized Filter.
5. In this column the default configuration for the Custom Auto-Filter dialog box says Show rows where Price equals null. If you don’t enter a value in the second column it’s blank.
6.  Press Enter to display all rows that contain a blank cell in the Price column.

To suppress records where the price is missing change equals to does not equal and press Enter.

Clear Filters from a Column

To clear filters from a column, display the filter menu then press Ctrl + C. This technique is useful if you’ve applied one or more filters and you need to selectively remove them.

If I’ve filtered my list to show only items where the Qty is less than 25 and Price is greater than 10, I can remove the filter from the Price column while leaving the Qty filter intact.

1. Navigate to Price column heading in cell D3.
2. Press Alt + Down Arrow to display the filter menu.
3. Type C to clear the filter from the price column.

Clear All Filters

To clear all filters from a Data list press Alt, A, and C.

Don’t press all three keys at once. Press (and release) the Alt button to display keyboard shortcuts in Microsoft Excel. Press A to select the Data tab, then press C to clear all filters from the data list.

This sequence will remove criteria from multiple columns at once, restoring your data list to its unfiltered state.

What’s Next?

These keyboard shortcuts will help you work with filters in Excel more efficiently. If you’d like a refresher on the basics, check out this article where we explore how to add a filter in Excel to display relevant data.

If your having trouble filtering data in Excel, you might want to check out Excel filter not working? 10 common problems and solutions.

See you next time!