7 Keyboard Shortcuts for the Filter Drop Down Menus in Excel
205

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 205 comments
David Landy - February 4, 2016

Hi Jon,

Great bonus tip about data tables!

One thing I’m struggling with, when using filters: if I copy a cell with a value in, and then try and paste it into the search box, it won’t work 🙁

Do you know why not – and if there’s an easy workaround?

At the moment I either use F2, Ctrl-Shift-Home, Ctrl-C to copy the cell contents, or paste it to Notepad and copy it from there – both of which are a bit of a drag.

Thanks in advance,

David

Reply
    Jon Acampora - February 4, 2016

    Hi David,
    What version of Excel are you using? It does work for me on the Windows versions of Excel.

    You can also filter for the selected cell’s value by using the keyboard shortcut Menu Key,E,V. Here is an article on the menu key shortcuts.

    Reply
      David Landy - February 4, 2016

      Thanks for the quick response.

      Hmmm… Now that I’ve written in to you about it, it’s started working! Spooky 🙂 How did you do that?!? (And ain’t that the way…)

      I’ve got the latest version of Excel, I think it’s 365.

      Thanks again for your help.

      Reply
      David Landy - February 4, 2016

      Ah OK, I think I can reproduce it now…

      If you hit Ctrl-C to copy the cell, then go to the range you want to filter and hit Ctrl-Shift-L to activate the AutoFilter, then try to paste the cell value with Ctrl-V, it doesn’t work.

      I think this must be because Ctrl-Shift-L removes the copied cell from the clipboard 🙁

      But if you’ve copied the value from eg Notepad, or if you’ve used F2 to edit then copy actual cell value, then you can paste it even after hitting Ctrl-Shift-L.

      Reply
Rowena - January 20, 2016

Hi, how can I use filter in MacbookPro? I can’t find it. I do usually filtering by color in an ordinary kind of laptop though.

Reply
Jørgen Wulff Rasmussen - January 20, 2016

Hi Jon

As many others have written this is a great article. If you allow I Would love to translate it to Danish and put it on our website.

Unfortunately many Danish users do not search for solutions in English, and this great article Can save Them hours.

At the moment I am at a clients site and just teaching them the technique without the shortcuts has saved them hours of work. This article can improve the saved time further.

Thanks in advance
Jørgen

Reply
    Jon Acampora - January 20, 2016

    Hi Jorgen,

    Thanks for the great feedback! I’m happy to hear that this is saving users time all over the world.

    Yes, you can translate the article. Just put a link to this article on that page and also send me a link so I can post it here.

    Thanks again and have a great day!

    Reply
Sean - January 6, 2016

AWESOME!
Thank you Jon!
I found your site as I was looking for an answer to this question – is there a way to find the next filtered column?
EG cursor is in cell A1 and there is a filter set in column AB – how can I get to that filetered column directly without scanning each cell?

Reply
    Jon Acampora - January 6, 2016

    Thanks Sean! Unfortunately there isn’t any way to do that. At least not that I know of. It would be a really nice feature to have though!

    Reply
      Sean - January 6, 2016

      Bummer!
      Thank you Jon, and keep up your good work – I am looking forward to delving into your app over the next few weeks…
      Sean

      Reply
JAWAD - January 4, 2016

Nice Share Jon. Stay Blessed,

Reply
Tyler Clawson - December 3, 2015

This is so well written! It is so hard to find Excel threads and how-tos that are easy to follow. I was looking for a shortcut on how to clear all the filters, and I found it right away here. Thank you so much, Jon.

Reply
Gurjeet singh - November 11, 2015

Valuation Information for Financial Professionals. We often work with long tedious Data and These types of Techniques are very useful to work with and lot of time saving handy tools.

Reply
beenie - October 30, 2015

Hi Jon,
Thank you for the tips.
I am just looking for a shortcut: how would you make the context menu appear using only the keyboard, when an Excel cell is selected (equivalent of right click)
(when you don’t have the context menu key on your laptop)
thanks!
beenie

Reply
    Jon Acampora - October 30, 2015

    Hi Beenie,
    Great question! If your keyboard has a Menu Key (on the right side next to the Ctrl Key) then you can use that to open the right-click menu. If you don’t have a menu key then Shift+F10 should work. Here is an entire article on the Menu Key shortcuts for Excel.

    Please let me know if you have any questions. Thanks!

    Reply
Markus - October 15, 2015

Is there a way / shortcut so that the filter drop down menu is automatically expanding down further? When there is a lot of data, i always have to drag the right bottom corner down so that the filter expands till the bottom of my excel / screen.

Reply
    Jon Acampora - October 15, 2015

    Hi Markus,
    Great question! Unfortunately there is no way to expand the menu with a keyboard shortcut. The mouse is the only way. It would be great if you could at least set the default size of those menus, but no go… bummer!

    Reply
      Becky Perry - October 20, 2015

      I can not expand the drop down menu ever since I upgraded (i’m on mac). I used to be able to pull the corner but now nothing comes up.

      Also, my scroll bars (already checked in preferences) do not come up.

      Help!

      Reply
        Jon Acampora - October 21, 2015

        Hi Becky,
        Sorry to hear you are having problems with the new Mac version. What version are you on? I believe the latest version is 15.15. You can see that by going to the Excel menu and selecting About Excel…

        I am able to resize the filter drop down menu on the latest Mac version (15.15). It requires a pretty precise hover over the edge of the menu though.

        I also noticed that the menu snaps to the top of the application window when you click on the filter icon. Normally it would appear right below the cell that contains the filter. Here is a link to a screenshot with the filter appearing at the top of the window. I was able to resize the filter menu, as you can see in the screenshot, but still weird behavior.

        Filter Drop Down Screenshot Mac 2016

        I do see the scroll bars appear when I’m scrolling. But they fade away and disappear a few seconds later. I’m honestly not a big fan of that design. The scroll bars can be useful in quickly determining the length or width of the used range on the sheet. I don’t know of any way to the scroll bars always visible in the Mac version.

        Sorry I couldn’t be of more help. Hopefully upgrading to the latest version will solve the issue with the filter menus for you. Please let me know if you have any other questions. Thanks!

        Reply
Durgesh - October 2, 2015

great…
thank you so much sir.. for help me in filter and this is very helpful to me. Tanks Jon!

Reply
CHANDRIKA - September 24, 2015

SUPER

Reply
Ken B - September 16, 2015

Great article! I have a few questions. I tried using filter by color and it worked great, but once I changed the color of a cell and selected the filter menu, I wasn’t able to remove the colored selection by pressing OK. I had to deselect the filter and reselect it. Whereas with text filters, I can just press OK to update the filter.

Also, is there a general keyboard shortcut (where I don’t have to be on the exact filter cell) to refresh the filter so it removes items that no longer apply. For example, if I’m filtering for No items and change a no to a yes, I want to remove the yes quickly.

Thanks again!

Reply
    Jon Acampora - September 27, 2015

    Hi Ken,
    Great questions. There is a “Reapply” button on the Data tab of the ribbon that will help with your filter by color issue. Instead of deselecting and reselecting the color filter, you can hit the Reapply button and the filter will be reapplied to include/exclude your changes.

    The keyboard shortcut to Reapply the filters is Ctrl+Alt+L. You can see the keyboard shortcut in the screentip when you hover your mouse cursor over the Reapply button.

    Please let me know if you have any other questions.

    Thanks again Ken!

    Reply
jeessey - September 8, 2015

Great tutorial. Thanks a lot!!!

Reply
kamal - September 3, 2015

Dear Sir,

is there any shortcut key to rename the sheet name?

Reply
    Jon Acampora - September 4, 2015

    Hi Kamal,
    The keyboard shortcut to rename a worksheet is Alt+ O H R. That’s an old shortcut from Excel 2003, but it still works in current versions of Excel.

    Reply
GLENN - August 27, 2015

VERY GOOD AND THANK YOU

Reply
Shrey - August 22, 2015

Thanks a lot for the cool tips!

Reply
SUNANDA SAHU - August 19, 2015

thanks, greatly helped and wonderfully explained.

Reply
Mahesh Kumar - August 14, 2015

Thank you very much…you have taken great effort to explain’em.
Cheers!!!

Reply
Nishad - July 15, 2015

Gentlemen!!

Is there any way to get the short key to select the entire row which includes the row header. Consider there will be merged cells.
Need only one row to be selected as we can do with mouse.
Please let me know how to add picture followed by my comment so that I can easily show what is in my mind if still confuses.
Thanks and best regards,
Nishad MK.

Reply
    Jon Acampora - July 18, 2015

    Hi Nishad,
    Shift+Spacebar selects the entire row. Ctrl+Spacebar selects the entire column. Please let me know if that helps.

    Thanks!

    Reply
Champika Duminda - July 14, 2015

Hi John,

Thanks for the Keyboard shortcut for filtering & sorting. It is very useful and saved valuable time. Less considering of the mouse.

Cheers,

champika Duminda

Reply
seri - June 16, 2015

Hi Jon,
While trying the filter shortcut, I “accidentally” open a drop down list that consist of cell content in the column filtered and I can navigate in it. But I cannot remember it. Do you know what it is, it will be very helpful.

Cheers,
Seri

Reply
    Jon Acampora - June 18, 2015

    Hi Seri,

    I believe you press Alt+Down Arrow in the cell. If the cell contains a validation list (in-cell drop-down), then Alt+Down Arrow will open the list. If it does not contain a validation list then it will display a list of the different cells in that column. Let me know if that answers your question. Thanks!

    Reply
pradeep kumar - June 15, 2015

These tips are very cool and very helpful and saves lot of time. Thank you very much.

Reply
Nilesh - June 4, 2015

Hey Jon,

Thanks, these tips are pretty handy.

I actually came here when I was looking for a shortcut to filter non-blank rows.

While playing around some shortcuts, I discovered (Eureka! Eureka! but I’m in full clothes :-)) following sequence works to filter non-blanks.

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

Cheers,
Nilesh

Reply
    Jon Acampora - June 6, 2015

    Hi Nilesh,

    I love the feeling of making those discoveries! Thank you so much for posting it. After reading your comment, I made a discovery that’s going to save you a little time…

    Alt+Down Arrow, F, N Enter

    This will also filter for non-blanks. It basically opens the Custom AutoFilter menu and sets the comparison operator to “Does Not Equal”. The criteria box is blank by default, so this is the same as filtering for non-blank cells.

    Try it out and let me know what you think! I also added #8 in the post above to filter for blanks & non-blanks.

    Thanks again Nilesh! I hope you have a great day.

    Reply
Ganesh - April 9, 2015

thnxxxx. it help me very much…

Reply
srujana - March 19, 2015

very useful

Reply
Old - March 15, 2015

Yes. Thank you. [Shared] is bad. Unshared is good!
Now if only i could resolve the “corruption”… It has max rows and columns. Saving as xls does not work. After saving and reopening the new xls version it immeditely wants to do a recover. If i do, then most of my formatting is gone after it recovers, including coloring. Leaving it as a xlsx file means its slow and sluggish. And trying to use it in the 2012 compare spreadsheet utility causes a out of memory failure. Argh. Thanks again.

Reply
    Zack Barresse - March 16, 2015

    OLD, you *don’t* want to save as XLS, but in the new file formats. Are you sure there are background colors applied to cells in the filter range? If a file is corrupt, or keeps corrupting, I’d recommend rebuilding the file from scratch. Copy/paste data, rebuild formulas, conditional formatting, charts, images, etc.

    Reply
old - March 13, 2015

So I see YOUR Filter by Color is GRAYed Out.
1.) How do you get Filter by Color if IT IS TOTALLY MISSING FROM THE MENU?
2.) How do you un-gray it if it is gray like yours?

I have many spreadsheets one has the options visible and selectable, another does not show the Filter by Color or Sort by Color at all.

Why is this? thanks.

Reply
    Jon Acampora - March 15, 2015

    Hi Old,
    Great question! Here are a few things that can cause the filter by color to not be available for disabled:

    • The workbook is saved as a “.xls” file instead of the newer “.xlsx” file version.
    • The workbook is shared.
    • The filter is applied, but does not include all the data rows.
    • There are no cells in the column with a font or fill color applied.

    Please let me know if any of those help.

    Thanks!

    Reply
GUNJAN VASWANI - March 1, 2015

FILTERS ARE OF GREAT HELP
I M JUST STARTING TO WORK ON EXCEL N YOUR SITE WAS OF GREAT HELP TO ME

Reply
Ram Bhushan - February 24, 2015

Thank you very much! It is very helpful……..
thanks a lot……….

Reply
ismail - February 24, 2015

Thank you very much

Reply
Andrew - February 19, 2015

Have you ever had an issue where CTRL+SHIFT+L did not turn on/off filters? I’ve used this many times before but all of a sudden it’s not working anymore. When I manually click filters it works fine, but when I use the shortcut keys all I get is an hour-glass for half a second and nothing happens. Any help would be greatly appreciated!

Reply
    Jon Acampora - February 19, 2015

    Hi Andrew,

    Is there any chance that you have a macro or add-in that is using that shortcut combination? I have a few add-ins, Paste Buddy and Formatting Shortcuts, that use the Ctrl+Shift+Letter to trigger a macro. Something like that might be interfering with Ctrl+Shift+L. Let me know if that helps. Thanks

    Reply
    Zack Barresse - February 19, 2015

    Best way to test if something else is utilizing or redirecting your keyboard shortcut is to open a fresh Excel, close all workbooks, uninstall all add-ins (including Excel, COM, XLL, etc.), open your file and test again.

    For a keyboard shortcut for turning AutoFilter on/off, I generally use ALT+D, F, F.

    Reply
      Andrew - February 20, 2015

      Thanks for the replies, I was actually able to figure it out. I’m connected to a Bloomberg terminal and Bloomberg recently enabled some hotkeys, CTRL+SHIFT+L being one of them that simply refreshes your sheet.

      Reply
        Jon Acampora - February 24, 2015

        Well that makes sense. Alt+A+T will also turn the filters on/off.

        Reply
        Shane - March 4, 2015

        Well found… that was killing me, didn’t seem like anyone was having the same issue. Bloomberg always messing things up.

        Reply
        Shane - March 4, 2015

        FYI- In order to disable a Bloomberg hot key:
        Go to the Bloomberg Tab
        Under the Utilities Ribbon, click the drop down from “Options”
        Select “Hot Key Manager”
        From there select and deselect any hot key you like!

        Reply
shylesh - February 17, 2015

how do you traverse a filter without using Alt+down arrow
Say filtered list in column 1 and other data in column 2 and so on
When i select a value in column 1, a chart appears for the remaining selected data
This chart will keep changing for every value change in column A
Now everytime I dont want to open the filter box –> Type E–> then search again.
I have to just use some key combination and select the next value in column A

Reply
    Jon Acampora - February 18, 2015

    Hi Shylesh,

    You can use the right-click menu to filter by the selected cell’s value. Right-click on the cell > Fitler > Filter by Selected Cell’s Value.

    You can also use a keyboard shortcut to do this. If you keyboard has a menu key, then the keyboard shortcut is: Menu key+E+V

    The following screenshot shows an example.

    Filter Cell Value with Right-Click Menu Keyboard Shortcut

    Let me know if this helps. Thanks

    Reply
KRupa - February 3, 2015

very helpful and simple to understand. Thank you!!

Reply
naga - December 10, 2014

Awesome

Reply
Nikhil - December 10, 2014

thanks exactly what I wanted.

Reply
Alex - November 28, 2014

Hi Jon

Great article with some very useful follow-up comments and tips.

One simple question … how do you vertically align the drop-down filter button in a cell with a larger than normal height?

It’s easy enough to do so with a cell’s contents but the drop-down filter button stubbornly remains at the bottom and I need it at the top!

I’ve looked everywhere and haven’t located an explanation to what I am sure is considered an Excel basic.

Cheers
Alex

Reply
Vignesh - October 30, 2014

Many thanks. Time saver.
Additional tip. After following Tip #5, and you want to select all the rows that are in the search result, use SHIFT + SPACE BAR.

I actually wanted to delete rows that match a filter search.
This article helped me select the rows to be deleted really fast.

Then I later found that to select them and delete them, I needed to use SHIFT + SPACE BAR.

Ctrl (+) – is short cut for deleting a selected row. Enjoy!

Reply
    Jon Acampora - October 31, 2014

    Thanks for the tip Vignesh! Shift+Space Bar is one I use all the time. Ctrl+Space Bar is the shortcut to select an entire column. Once selected the Ctrl++ (hold Ctrl and press plus key) will add rows or columns. Ctrl+- (hold Ctrl and press minus key) will delete rows or columns. Thanks again for the comment!

    Reply
Ben Kusmin a/k/a Excel Esquire - October 28, 2014

Great post Jon. I filter by blanks all the time, and even 7 keystrokes is better than reaching for the mouse! (speaking of which, I can skip to the last check box using End–without the Shift)
One comment I would add is that you can use Ctrl+Up Arrow to get up to the Header box immediately, as long as there are no blank cells in the way.

Reply
    Jon Acampora - October 28, 2014

    Hey Ben,
    Thanks for the suggestion! I recently found a faster way to filter for blanks.

    Alt+Down Arrow, F, E, Enter

    The “F” opens the Filters sub-menu, “E” selects the Equals option and opens the Custom AutoFilter window. The criteria box in the Custom AutoFilter window is blank if there is not filter applied to the field. If there is a filter applied, then hit backspace/delete to clear it. Hit Enter to filter the column for blanks.

    I call this one “Find Empties” to help me remember the F,E combination. It’s much easier than the 7 keystrokes.

    You can also search for blanks:

    Alt+Down Arrow, E, blank, Enter

    This is another way to search for blanks. Excel gives us so many options… 🙂

    Reply
Miro Matas - October 2, 2014

Briliant!!!

Reply
haneef - September 24, 2014

Thanks alot, this is very helpfull to me, thanks again.

Reply
anil - August 3, 2014

great value addition for learner…thank you so much

Reply
Julie Heyer - July 15, 2014

I’m sure you know, but just in case, re: finding blanks and errors if you are NOT using filter. For errors, go to the Find Go to Special menu.

Click on the Formulas radio button. Uncheck all selections but logicals. It will find all the @NA which is the main error issue I need to look for.

There is also a choice of “Blanks” radio button.

The program ASAP a cheap Excel add-in may have a way to do both at the same time. It has a lot else I need like that. Normally I refrain from any kind of software mention but that one truly is a great value and the only answer I know to several Excel problems. Site admin – feel free to delete this part if inappropriate.

Julie Heyer

Reply
Doug Glancy - July 14, 2014

I have nothing informative to add, but want to say thank you! Alt-Down-E was the last piece I needed in being able to keyboard through a six-step task that’s repeated several times. So nice not to reach for the mouse in the middle of it.

Reply
    Jon Acampora - July 14, 2014

    Thanks Doug! I totally agree. I think my mouse is jealous that I’ve been doing so much filtering without it… 😉

    Reply
Taylor - July 1, 2014

Hi Jon,

Do you know if it is possible to to use shortcut #4 (Check/Uncheck Filter Items) in Excel for Mac 2011? I just made the switch and figure out how to do this using keys.

Thanks so much for this awesome article! It’s the closest anything I can find has come to helping out. 🙂

Reply
    Jon Acampora - July 1, 2014

    Hi Taylor,

    Great question! I’ve tried about every key combination but can’t find a way to really access that list. I did find that if you press the following keys you can get into the list. Try this:
    1. Press Opt+Down Arrow to open the filter menu
    2. Then press the Tab key once, this sets focus on the list.
    3. Now press Fn+Down Arrow. The list will scroll to the bottom if you have a lot of items in the list.

    So this means that the keyboard has focus in the filter list. But I can’t find any other keyboard shortcuts that select/unselect any of the items in the list. I think I tried every combination on the keyboard, but nothing works.

    The drop-down filters on the Mac version seem to be pretty limited with the keyboard. You can use the Tab key to toggle through and set focus on the filter and search fields, but not much else. It’s not a very keyboard friendly menu.

    I will check with the MVPs and let you know if there is anything we’re missing.

    Reply
Dominic - May 7, 2014

Hi Jon

Thanks for putting this up.

While trying it out, I found a new shortcut that you’ll like.

Alt-Shift-Down in a table column will open the drop down menu no matter where you are in the column. And it applies to all the other tips you’ve listed up there.

Great work

Reply
MF - May 1, 2014

Nice post. Thanks for sharing.

I think in #5, it’s worth mentioning the check box “Add current selection to filter”.
I find it very useful when i need to filter more than 1 criteria from a large list.

Reply
    Jon Acampora - May 1, 2014

    Thanks MF! That’s a great suggestion! Three down arrows and a space bar will check the “Add current selection to filter” from the search box.

    For those that have not used this feature, it can be very handy…

    Let’s say you have a column of data that contains drink items, and you currently have it filtered for “coffee”. Now let’s say want to add “tea” to the filter, so the list will displays rows that contain either coffee or tea.

    1. Type “tea” in the search box of the filter drop down menu.
    2. You will see another item appear in the item list that says “Add current selection to filter”.
    3. Check the box and click OK.

    Here’s a screenshot to help explain.

    Excel Add Current Selection to Filter Drop Down Menu

    The column will now be filtered for either coffee or tea. And this will work with a lot more than just two criteria. You can continue to add more items to the filter with the steps above.

    Great suggestion MF! Thank you!

    Reply
      MF - May 2, 2014

      You are very welcome Jon.
      Actually it’s on my list of contents that I am going to write about… ;p

      Reply
      Gunjeet Singh - October 6, 2015

      Is there a shortcut to activate this as well.. i know it is only 3 down buttons away from the search box.. but still think it will be helpful

      Reply
Zack Barresse - April 30, 2014

I use Alt + Down Arrow in a cell to access the “Pick From Drop-down List” feature of the right-click menu. One of my most used. 🙂

I use a slightly different configuration for toggling items on/off in the autofilter drop down (like your example above):
Alt + Down Arrow, E, Down Arrow, Spacebar, Shift + End, Spacebar, Enter

It is nice being able to navigate the filter drop-down from the keyboard though! I didn’t know about the “E” keyboard shortcut. I always just used the arrow keys or the mouse. I like it!

Reply
    Jon Acampora - April 30, 2014

    Hey Zack! The Alt+Down Arrow for the pick list is another great tip. Thanks for mentioning that one! I use it a lot too. It is especially useful when you are working with a list that is filtered, and you want to input a value that you can’t copy/paste because it is in a hidden row. Sorry, that’s probably not the best explanation. 🙂 Probably a topic for another post…

    I was trying to figure out the “E” shortcut a few days ago and I think I pressed every other key on the keyboard before I landed on “E”. It was one of those “backflip” moments. haha 🙂

    Reply
Jeff Weir - April 30, 2014

Great post, Jon. I noticed when trying these out that Alt + down arrow on a cell with data validation opens the dropdown list…very handy.

Reply
    Jon Acampora - April 30, 2014

    Thanks Jeff! And thanks for pointing out another use for Alt+Down Arrow. It has quite a few uses. Zack mentions another great use for it in the comment below.

    I also use it when editing structured reference formulas to display a list of the column names.

    Reply
don - April 30, 2014

very helpful. I find that keyboard shortcuts save a lot of time when your working on a laptop without the luxury of a dedicated mouse. The answer to your challenge, this will select the last item in your list of items in selections list.

great job!

Reply
    Jon Acampora - April 30, 2014

    Thanks Don! That’s the answer! And as I mentioned in the comment above, it can be used to filter blanks or cells that contain errors. I should have asked if there is a faster way to filter for either of these.

    And you are right about the laptop. For me, nothing is slower for me than the laptop track pad.

    Reply
Peter Raiff - April 30, 2014

The shortcut: Alt+Down Arrow+E, Down Arrow, Space Bar, Shift+End, Space Bar, Enter
selects the last item from the drop down menu. Alt+Down Arrow+E opens the filter drop down and selects the search box.

Down Arrow selects “Select All”

Space Bar un-selects all items

Shift+End moves to the last selectable item in the list

Space Bar selects the item

Enter applies the filter.

Reply
    Jon Acampora - April 30, 2014

    That’s it! Thanks for the comment and detailed explanation Pete! It is one way to filter for blanks or errors because either of those options are listed at the end of the list.

    But it’s definitely NOT fast. I probably should have asked if anyone knows of a faster way to filter for either blanks or cells that contain errors…

    Reply

Leave a Reply: