Keyboard Shortcuts for the Filter Menus - Free Video Training Series - Part 2 of 3 - Excel Campus
34

Keyboard Shortcuts for the Filter Menus – Free Video Training Series – Part 2 of 3

Bottom line: In this 2nd video of the series on Excel Filters I explain all of my favorite keyboard shortcuts for filtering.  These will save you a ton of time!

Skill level: Beginner

keyboard-shortcuts-for-excel-filter-drop-down-menus

Video #2: Keyboard Shortcuts for the Filter Drop-down Menus

In this second video I explain how to use keyboard shortcuts for the filter drop-down menus.  This will save lots of time with common filtering tasks like filtering for blanks, zeros, errors, custom searches, and much more.

Double-click video to view full screen HD.

Downloads

Download the file I used in the video to follow along.  This file also contains instructions on each shortcut, and can be used as a guide to learn the shortcuts.

Excel Filters Training - Keyboard Shortcuts - Part 2.xlsx (1.1 MB)

Become a Keyboard Shortcut Ninja!

Learning keyboard shortcuts in Excel can really save us a lot of time, especially with filters.  The filter menus have a ton of features and options for us to choose from, and this usually means navigating menus and sub-menus with the mouse.

Fortunately, almost every command in the filter menus is accessible with a keyboard shortcut.  Some of these keyboard shortcuts can get long, but I recommend learning the ones you will use the most.

I have another popular article on 7 Keyboard Shortcuts for the Filter Drop-down menus that contains guides for some of the shortcuts in this video.

I also recommend getting a good keyboard that is conducive for Excel keyboard shortcuts.  Checkout my article on the best keyboards for Excel keyboard shortcuts for more on that.  I prefer to use keyboards that have a menu key and function keys that are not shared.

Keyboard Guide Excel Keyboard Shortcuts Comparison

Click here to learn more about what makes a good keyboard for Excel keyboard shortcuts, and see which keyboard I use.

Filter Mate’s Find Feature

At the end of the video I share a tip on how to use the Excel’s Find window to find a column you want to apply a filter to.  This tip can save us time and prevent some horizontally scrolling, but it does require us to know the exact name of the column header we are looking for.

The Filter Mate Add-in has it’s own Find feature that allows you to see a list of all the column names in the filtered range or table, and quickly filter down the list by typing a search.  Once you find the column you are looking for, simply press Enter to select that column and set focus to Excel.  You can then press the Alt+Down Arrow shortcut to open the filter drop-down menu.

filter-mate-search-and-navigate-to-columns-in-filtered-range-table

The entire column is selected so you can also use this feature to tie out numbers.  If the column contains numbers then you can quickly see the metrics for Sum, Count, Average, etc. in the status bar in the bottom right corner of the application window.

filter-mate-select-entire-column-status-bar-metrics

The Filter Mate Add-in will be available soon.  You can signup here to get notified when Filter Mate is available.

What’s Your Favorite Filtering Tip?

I shared quite a few tips for working with filters in this first video.  I’m curious to know if you have a favorite tip or shortcut that you use.

Please leave a comment below with your favorite tip.  You can also leave a question with a tip or technique that you would like to learn.

I’ll be back next week with video #3 of the series.  Thank you!

Videos in this Series

Video #1 – Overview & Introduction to Excel Filters

Video #3 – Excel Filter Logic for Multiple Columns

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 34 comments
Ian - April 2, 2017

Hi Jon
What an excellent service you provide
I am heavily into excel in my work and am currently building one file that is currently 120000 lines and 30 Mb and uses tons of VLOOKUP and Boolean. Using multiple filters I have one tedious part that must have a solution somewhere.
The main table contains in one column, 26000 unique values used anywhere between 2 and 6 times. I can certainly filter other columns and then run down the drop-down list where the selection has been reduced to a few hundred at a time. This can be very tedious inspecting one at a time via the drop-down list.
Is there any way that I can simply run through the list using only a tab or down key to run through the list from start to finish as a quick visual check.
Again – full marks to your service
Regards – Ian

Reply
Mary Harkins - March 6, 2017

Hi Jon – Ive been loving your videos on Pivot Tables. I am a complete amateur and these have been really helpful. Im hopeless at writing complex formulae’s and would like your advice. In Britain all employers need to report on the Gender Pay Gap and I just need to know what would be the best format to do this. Pivot Tables or something else?

Reply
Anne - January 27, 2017

Hi Thank u Mr.Jon.. Iam a Nursing Graduate.. I just Hired as Analyst in Wood Company.. THey used excel a lot.. More on Pivot and more.. I watch ur video on pivot and it helps me a lot.. Great.. Excited to read and watch some or ur video..

Reply
Annamaria - December 18, 2016

Hi,
Still one question:
Alt, A, C doesn’t work on the protected sheet, even if users are allowed to use autofilter… Is there any way to solve this? I need to protect the sheet so that others cannot change mistakenly something, but it would be very useful for them to find a way to clear up all filters without the need to search and find out where they are…

Reply
    Jon Acampora - December 20, 2016

    I believe the only way to do that is by using a macro. The macro will have to unprotect the sheet, clear all filters, then protect the sheet again. You could create a button for the macro and place it on the sheet.

    Reply
Annamaria - December 18, 2016

Hi Jon,
Thanks for your videos,
But Shift+Alt+down arrow doesn’t work in my Excel (2007)… Are there any settings which might hinder it?

Reply
Annamaria - December 18, 2016

Thank you for your videos.
I am a chemist and we use Excel for our database.

How can I sort a list like this: FC1, FC10, FC31, FC5, FC6? I would need Excel to “ignore the FC beginning and to sort only according to the number… Is this possible?
The same is if such a column is filtered (this is my main concern, not sorting). The Dropbox-list shows in the order above, but I would need it to show in the order of the numbers (actually I need to see which is the last number in the list, but if it is FC100, and I just look at the bottom of the list, it will show me FC99, and I will be in error… Can I “tell” Excel to show me in the order of the numbers and not arrange them as a text? Is it possible to do that somehow?
Thank you

Reply
    Jon Acampora - December 20, 2016

    Hi Annamaria,
    It will probably be best to create a separate column with the number only. If all numbers start with “FC”, you can make a copy of the column and then do a Find and Replace to replace FC with nothing.

    Then sort the column that contains the numbers. I hope that helps.

    Reply
Nancy - December 1, 2016

I consider myself a Excel Power User – but wait!
shortcut : Menu key opens right click menu!! Seriously! How did I not know this!

Reply
    Jon Acampora - December 10, 2016

    Haha! I did the happy dance when I learned this one too. It’s amazing how many things there are to learn with Excel. 🙂

    Reply
Andrey Kazak - December 1, 2016

Dear Jon,

How do one apply logical expressions (AND/OR, brackets) in the filter search box?

Thank you.

Reply
    Jon Acampora - December 10, 2016

    Hi Andrey,
    I’m not sure that you can use any AND/OR expressions in the search box. You can use wildcard characters like * and ?.
    I hope that helps.

    Reply
Anh - November 22, 2016

Thank you for sharing these great tips. I love all the shortcut as they’re all new to me. I can’t imagine even filter has so many keyboard shortcut options.
You got to love Excel.

Keep up the good work.

Reply
    Jon Acampora - November 24, 2016

    Thanks Anh! I agree. It’s amazing how many keyboard shortcuts there are in Excel. I sometimes wonder how many I have memorized over the years… 🙂

    Reply
Philip - November 22, 2016

The shortcut to do the filter within the column is my new favourite

Reply
Kris - November 22, 2016

I really enjoyed watching this video and recommend others to watch and learn.

Host is an awesome teacher too. His voice and way of explaining I liked it and hope others will also like.

Host explains clearly and crisply the subject matter.

I watched many of his videos and all I liked and they enriched my knowledge on Excel.

Thanks for educating the community and appreciate your volunteership.

Thanks a bunch have a nice Thanksgiving day

Reply
    Jon Acampora - November 24, 2016

    Hi Kris,
    Thank you so much for the nice comment. I really appreciate your support, and sharing the videos with others. That helps me out a lot.

    I hope you have a nice Thanksgiving as well. 🙂

    Jon

    Reply
Donna - November 22, 2016

I really liked the finding columns shortcut but they were all great. I wasn’t able to download the Excel Filters Training – Keyboard Shortcuts – Part 2.xlsx (1.1 MB)
I kept getting a security warning: The application experienced an internal error loading the SSL libraries. Could be just me.
Thank you! Looking forward to #3!

Reply
    Jon Acampora - November 24, 2016

    Hi Donna,
    I’m sorry you had trouble with the download. I had an issue with a certificate expiring on the site yesterday, and it caused some visitors to not be able to access the site or downloads. The problem has been resolved now.

    I also emailed you the file. Thanks again and have a nice day! 🙂

    Reply
chad - November 22, 2016

thanks for the info, great tips!
Any tips for filtering within a pivot table? For example, if I have a dataset that shows data going back to 2006, and I only want to show results for 2006 through 2009, instead of individually checking each filter box, is there a way to select all those through a shortcut?

Reply
    Jon Acampora - November 24, 2016

    Hi Chad,
    Great question! If the Year field is in the Rows or Columns area of the pivot table then you can use the Label Filter Between criteria to specify the first and last year of the range you want to filter for. Here is a screenshot.

    Pivot Table Fitler for Date Range with Label Between Filter

    The keyboard shortcut to open that menu is Alt+Down Arrow, L, W.

    If the field is in the Filters area of the pivot table then you cannot use the Label Filters menu. You can use the up/down arrow keys and the space bar to check and uncheck items in that list. That’s the only way I can think of for the if the field is in the Filters area.

    I hope that helps.

    Reply
      chad - November 28, 2016

      Thanks Jon! Yeah, the 2nd part is what I am looking for a solution to – I like the “between” option you have when in rows/columns too, thanks again – valuable site!

      Reply
Ray - November 22, 2016

Jon, Your presentations are the best, I subscribe to many to increase my knowledge level, your has helped me the most, Your presentation is clear and concise, your voice is pleasant to listen to, and your pronunciation is right on. Thanks for helping people like me to learn Excel. looking forward to the next lesson.
Ray

Reply
    Jon Acampora - November 24, 2016

    Hi Ray,
    Thank you so much for the nice comment. I’m really happy to hear that you are learning so much about Excel and enjoying the videos. Have a great day! 🙂

    Reply
Alan - November 22, 2016

Very well done. You described very nicely how to filter for things like blanks or errors and so on. What if you just want to uncheck an item from the list and exclude blanks for instance. Is there a keyboard short cut for that?

Reply
    Jon Acampora - November 22, 2016

    Hey Alan,
    Great question!
    You can use the Space Bar to check/uncheck the item you have selected in the checkbox. The download file contains some additional shortcuts for the checkbox list. The Page Up, Page Down, Home, and End keys work in this list to navigate through it.

    There are a few ways to jump down and set focus on the checkbox list. You can use Alt+Down Arrow, E, Down Arrow. If the column contains Dates then you can use Alt+Down Arrow, Up Arrow 3 times.

    I hope that helps. Thanks again!

    Reply
Tim Van den Heuvel - November 22, 2016

keybord shortcuts: great stuff. I like to work via those fast keys. Do you have a list of the keys with short description. The download is to much paper to print out. Continue with those movies, like them very much.

Reply
    Jon Acampora - November 22, 2016

    Hey Tim,
    Great suggestion! I just added a sheet to the download file that contains a printable list of all the shortcuts. You can print it out and put it on your desk or tape it to your monitor like I did. 🙂

    Excel Filter Keyboard Shortcuts Printable List

    You can also add to and modify the list as needed. I hope that helps. Thanks again!

    Reply
Marek - November 22, 2016

Thank you for these keyboard shortcuts. I can not say which is my favorite, because all the easier (accelerate) work with a Microsoft Excel spreadsheet. Link to this post I posted on my Facebook

Reply

Leave a Reply: