231

7 Keyboard Shortcuts for the Filter Drop Down Menus

This post will explain 7 keyboard shortcuts for the filter drop-down menus.  This includes my new favorite shortcut, and it's one that I think you will really like!

The Filter drop-down menus (formerly known as Auto Filters in Excel 2003) are an extremely useful tool for sorting and filtering your data.  When the Filters are applied you will see small drop-down icon images in the header (top) row of your data range.

Excel Filter Drop Down Menus Excel 2013

These menus can be accessed with keyboard shortcuts, which makes it really fast to apply filters and sorting to different columns in your table.  So let's take a look at these shortcuts.

#1 – Turn Filters On or Off

Ctrl+Shift+L is the keyboard shortcut to turn the filters on/off.  You can see this shortcut by going to the Data tab on the Ribbon and hovering over the Filter button with the mouse.  The screen tip will appear below the button and it displays the keyboard shortcut in the top line.  This works with a lot of buttons in the ribbon and is a great way to learn keyboard shortcuts.  See the image below.

Data Filters Screen Tip Keyboard Shortcut Excel

To apply the drop down filters you will first need to select a cell in your data range.  If your data range contains any blank columns or rows then it is best to select the entire range of cells.

Once the data cell(s) are selected, press Ctrl+Shift+L to apply the filters.  The drop down filter menus should appear in the header row of your data, as shown in the image below.

Ctrl+Shift+L Excel Turn Filters On Off

#2 Display the Filter Drop Down Menu

Alt+Down Arrow is the keyboard shortcut to open the drop down menu.  To use this shortcut:

  1. Select a cell in the header row.  The cell must contain the filter drop down icon. Excel Filter Drop Down Icon Unfiltered
  2. Press and hold the Alt key, then press the Down Arrow key on the keyboard to open the filter menu.

Alt+Down Arrow Excel Display Filter Drop Down Menu

Once the drop down menu is open, there are a lot of keyboard shortcuts that apply to this menu.  These shortcuts are explained next.

Bonus Tip: If you are using Excel Tables, and I highly recommend you do, you can press Shift+Alt+ Down Arrow from any cell inside the table to open the filter drop-down menu for that column.

Open Filter Drop-down Menu from Table Cell -Shift-Alt-Down Arrow

This shortcut was introduced in Excel 2010 for Windows, and works in all newer versions as well.

#3 Underlined Letters & Arrow Keys

The underlined letters in the drop-down menu are the shortcut keys for each command.  For example, pressing the letter “S” on the keyboard will Sort the column A to Z.  You must first press Alt+Down Arrow to display the drop down menu.  So here are the full keyboard shortcuts for the filter drop down menu:

  • Alt+Down Arrow+S – Sort A to Z
  • Alt+Down Arrow+O – Sort Z to A
  • Alt+Down Arrow+T – Sort by Color sub menu
  • Alt+Down Arrow+I – Filter by Color sub menu
  • Alt+Down Arrow+F – Text or Date Filter sub menu

Filter Drop Down Menu Keyboard Shortcuts

#4 Check/Uncheck Filter Items

The up and down arrow keys will move through the items in the drop down menu.  You can press the Enter key to perform that action.  This requires the drop down menu to be open by first pressing Alt+Down Arrow.  See the image in #3 above for details.

Starting in Excel 2007, a list of unique items appears at the bottom of the filter drop down menu with check boxes next to each item.  You can use the up/down arrow keys to select these items in the list.  When an item is selected, pressing the space bar will check/uncheck the check box.  Then press Enter to apply the filter.

Space Bar Select Items in Filter Drop Down Menu Excel

#5 Search Box – My New FAVORITE

Starting in Excel 2010 a Search box was added to the filter drop-down menu.  Excel 2011 for Mac users also get this feature.

This search box allows you to type a search and narrow down the results of the filter items in the list below it.

Alt+Down Arrow+E Filter Search Drop Down Menu Excel

When the filter drop down menu is open, you can press the letter “E” on the keyboard to jump to the search box.  This places the cursor in the search box and you can begin typing your search.

Alt+Down+Arrow+E is the shortcut to open the filter drop down menu and jump directly to the search box.

I just learned this shortcut and it is my new favorite because it makes it so fast to type and filter exactly what you are looking for in the list.  Prior to learning this I was using the down arrow key to get to the search box.  This required me to press the down arrow key 7 times to get to the search box.  Now I can do the same thing in one step by pressing the letter “E”.  What a time saver!

#6 Clear Filters in Column

Alt+Down Arrow+C will clear the filters in the selected column.  Again, this is a combination of the Alt+Down Arrow to open the filter menu, then the letter “C” to clear the filter.

Alt+Down Arrow+C Clear Filter in Drop Down Menu Excel

This would be the same as pressing the (Select All) checkbox in the item list.  The next shortcut will explain how to clear all the filters in all columns.

#7 Clear All Filters

Alt+A+C is the keyboard shortcut to clear all the filters in the current filtered range.  This means that all the filters in all the columns will be cleared, and all rows of your data will be displayed.

Alt+A+C Clear All Filters Excel

I add the Clear Filter button to the Quick Access Toolbar (QAT) and would highly recommend that you do this.  It serves two purposes that are very helpful.

  1. You can quickly press the button in the QAT to clear the filters.  If you are a mouse user this means you don't have to navigate to the Data menu to access the button.  You can also use keyboard shortcuts to press the buttons in the QAT.  See my articles on how to setup the Quick Access Toolbar  and how to use the QAT's keyboard shortcuts for instructions on this.
    _
  2. Use the button to visually see if any filters are applied.  This is the most important benefit to me.  If any filters are applied to the filter range, then the Clear Filter button in the QAT will show color (enabled).  If no filters are applied then the button is grayed out (disabled).  See the image below for details.
    _Clear Filter Button Quick Access Toolbar QAT Filter Applied or Cleared
    Debra Dalgleish has a great post and video with a few additional tips to Clear Excel Fitlers with a Single Click over at the Contextures blog.

#8 Filter for Blank or Non-blank Cells or Rows

Alt+Down Arrow+F+E+Enter will filter for blanks cells in the column.

Filter For Blanks Cells or Rows in Excel

The F,E combo opens the Custom AutoFilter menu where you can type a search term.  The box is blank by default.  So if you just hit Enter when the Custom AutoFilter menu opens, the column will be filtered for blanks.

This is another one of my new favorites!  It's much faster than unchecking the Select All box, then scrolling to the bottom of the item list.

You can also filter for Non-blanks using the shortcut Alt+Down Arrow+F+N+Enter.  This opens the Custom AutoFilter menu and sets the comparison operator to does not equal.  The criteria is blank by default, so this applies a filter for non-blank cells.  Thanks to Nilesh for leaving a comment and inspiring this one!

Bonus Tip

Typically you can only have one range filtered on a sheet at a time.  This means that if you have more than one range of data on a sheet, you can not apply the Filters menus to both ranges.

If you use the new Excel Tables feature (introduced in Excel 2007 and available for 2011 for Mac) then you can apply Filters to each table in the same worksheet.

Excel Tables Multiple Tables and Filters on Same Worksheet

Checkout my video tutorial on Tables to learn more about all the great time saving benefits they have to offer.

Want More? – Download the Workbook

I hope you've learned some new tricks that will save you time when working with Filters.  In my opinion, the keyboard shortcuts are the fastest way to work with these menus.  I encourage you to practice these techniques, and also share them with a friend that might benefit.

I have also created a free workbook that contains over 25 keyboard shortcuts for the Filter Menus. The workbook is organized by topic and contains images that will help you learn the shortcuts.  It also includes a data table where you can practice the shortcuts.

Filter Drop-down Shortcuts Workbook Sample Page

Filter Drop-down Shortcuts Workbook Sample Page

xls iconFilter Drop-down Shortcuts Workbook.xlsx

Please click the link above and the Excel file that contains the shortcuts will be emailed to you immediately.

You will also have the option to subscribe to my free email newsletter to stay updated with new articles and videos that will help you learn Excel.  After confirming your subscription you will be able to download my “10 Excel Pro Tips” eBook.  It's all free!

Challenge Question

What does the following keyboard shortcut sequence do?  The selected cell must be in the header of a filtered range.

Alt+Down Arrow+E, Down Arrow, Space Bar, Shift+End, Space Bar, Enter

Please leave a comment below with your answer.  Thanks!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 231 comments
betcy - December 3, 2018

Hi how do I sort multiple rows so that all row are sorted at same time in ascending order in excel 2007

Reply
Raj Kumar - October 14, 2018

can you guide some important shortcut for mac

Reply
KAMAL - October 6, 2018

Hi,

Can some one pls let me know the shortcut for selecting the Number or words direct in the drop down filter. For Example-

In the list 12 months is available and i have already selected the August in the list. When again i go to filter and drop down the filter rather going one by one in months is there any method to go directly on on selected “August” Month with out typing “Aug” alphabets.

Regards

Reply
    Jon Acampora - October 24, 2018

    Hi Kamal,
    I’m not sure of any way to do this directly. You can type the letter that the word starts with once you have set focus to the listbox. To do this:

    1. Alt+Down Arrow, E to select the search box
    2. Down arrow to set focus to the listbox.
    3. Type a to jump to the words that start with “a”.
    4. Up/Down arrow keys to highlight the item.
    5. Spacebar to select or deselect.

    It’s not exactly what you are looking for, but might help.

    Reply
Rafiq - October 1, 2018

In filter will check-Uncheck items in this list box. next time check items in this list direct how to go..?

Reply
Nitin - September 14, 2018

Nice article.
Thank you

Reply
Farhat Aziz - July 26, 2018

Answer

(Alt+Down Arrow+E, Down Arrow, Space Bar, Shift+End, Space Bar, Enter)

It will select the last category of that column/ deselect the all the categories accept the last one.

Reply
Salik - July 2, 2018

Indeed helpful. Thanks very much. Would be beneficial in saving time and increasing productivity/efficiency.

Reply
Bharat - May 28, 2018

how to insert filter in particular one box

Reply
Harry - May 11, 2018

Hi John,

Is it possible to do any of these functions on Excel for Mac. My biggest grime of my company is I had to move to Mac and can’t press Alt to navigate the ribbon anymore (from what I can see).

Thanks,
Harry

Reply
    Garvit - June 26, 2018

    No for Mac it has different shortcut keys

    Reply
      Raj Kumar - October 14, 2018

      please guide check uncheck shortcut key in mac because through space bar not work in mac.

      Reply
BHARANI - April 18, 2018

I need shortcut for applying after filter then check value one by one ascending order in excel

Example : A, B , C ,D

i need check the value one by one in ” D ”
value is ok then again every time i move the mouse or keyboard using multiple buttons,,

but i need after apply filter then check one row value one by one… using single key option…???

Reply
bouhamidi yacine - April 9, 2018

thank you

Reply
Ketan Sharma - March 7, 2018

To dropdown in my PC, Alt+Down Arrow worked, but in my Dell Inspiron Laptop, it rotates the screen. I use Office 16. please advise

Reply
    Shirish - March 19, 2018

    @Ketan: Is your “Ctrl” button in activated mode / did you accidentally hit on it?
    “Ctrl + Alt + Arrow” is the Keyboard combo for rotating the screen in the desired direction.
    Cheers

    Reply
    Rohit - October 23, 2018

    Screen rotation is not the issue of office/excel…it depends which window you are using. I think in window XP only, screen rotation works not in other windows.
    ( Alt + ↑ , Alt + ↓ , Alt + → , Alt + ←…….Works in window XP only )

    Reply
Souvik - February 28, 2018

Hi,

Is there any shortcut to directly select the last selected option?

Reply
    Daniel - May 23, 2018

    Alt+Down down… until you get to the box selection and then press “End”.

    Reply
Brady - February 23, 2018

Is there a shortcut to jump straight to the selection menu box. Similar to alt+E but for the box below?

Reply
    AnotherHiggins - March 5, 2018

    Brady,

    Alt+Down Arrow, E, Down Arrow

    will take you to the Select All box.

    Reply
elonderin - February 16, 2018

shift+alt+down/up not working with latest version of excel as of 2018-02-16

Reply
    Tran Binh - March 1, 2018

    Hi Elonderin,
    You must use Excel Tables.

    Reply
kaleeswaran - February 7, 2018

Thank you sir, very use full hints thank you sir , i will be followed youtube

Reply
Alex - December 31, 2017

Interestingly, your challenge question doesn’t work on date filters. I have a different problem on date filtering that brought me to your article so I thought it was interesting to see another way in which Date filtering diverges from normal filtering. Nonetheless–I do appreciate learning a new combination I hadn’t thought to use before, and it will definitely help for when I’m manually categorizing data in a new column. Thank you!

FYI – Doing the above challenge combination on a date column table header gets you stuck on an extra drop-down menu for date groupings with the following options: “All”, “Year”, “Month”, “Date”. It’s actually this special “grouping” that Excel (2016) provides that has baffled me to no end on how to actually apply a *specific* date to the filter using just the keyboard. In the challange’s case, the solution is to replace with , using has the additional benefit of always working in both regular & date filter formats with the caveat that you’ll have to hit twice in date filters to place the cursor in the list of column items.

The larger problem though, is that even with the column formatted as Date (IE an entry of 10/1/2014), as soon as you use a “/” in the search query, the query returns no results. A bit of playing around and I haven’t found any special kind of syntax that allows me to do this kind of filtering via typed query. It’s so frustrating!

Reply
    Jon Acampora - October 24, 2018

    Hi Alex,
    Sorry to not reply sooner. There is one workaround to this. You can turn off the date grouping feature in the filter drop-down menu.

    1. This is done from the Excel Options window (File > Options).
    2. Go to the Advanced tab, then scroll down to the section “Display options for this workbook”.
    3. Uncheck the box “Group dates in the AutoFilter menu”.
    4. Press OK

    You will now see the dates in the listbox, instead of the groups. You can use the search box to search a date.

    There are a few drawbacks to this though:
    1. The setting is workbook specific, so you will have to set this up on every workbook. You can have a macro that does this automatically though.
    2. You won’t be able to filter by year, month, day groups using the groups on the listbox.

    Here is a page from my friend Debra at Contextures with the macro to toggle the setting.

    I hope that helps.

    Reply
Justin - December 4, 2017

Challenge question:
Looks for spaces in the filter column. Essentially filter for ” “.

I use find and replace a space ” ” with nothing “”. When I have vlookup formula that returned nothing (not #N/A but nothing) and I copy and paste values to get rid of the formulas, I need to remove these left of spaces in cells.

Reply
Sivanesan - December 3, 2017

Good Article.

Answer to your Question (not so challenging though… as you have explained it already in detail)

It takes you to the last option in the filter (it will be blank, in case the list has a blank value)

Thanks

Reply
sujith - November 28, 2017

Gud one

Reply
Hardeep - November 25, 2017

Alt+Down Arrow+E command does not work in filter,

Reply
Iqbal - November 19, 2017

awesome ,,,very very useful

Reply
Sriram - November 18, 2017

Good one. Really helpful

Reply
sakthivel - November 3, 2017

very usefull

Reply
Ranjeet Singh - October 18, 2017

Alt+down does not work for merged Cells. it is not working when A1,A2 B1,B2 is merged. and I want to use this shortcut It is not giving the proper list.

Please help if knows

Reply

Leave a Reply: