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
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.
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.
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.
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.
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!
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
Thank you Marek! I really appreciate your support! 🙂
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.
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. 🙂
You can also add to and modify the list as needed. I hope that helps. Thanks again!
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?
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!
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
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! 🙂
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?
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.
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.
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!
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!
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! 🙂
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
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
The shortcut to do the filter within the column is my new favourite
Thank you Philip! That one is really handy if you are using Excel Tables.
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.
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… 🙂
Dear Jon,
How do one apply logical expressions (AND/OR, brackets) in the filter search box?
Thank you.
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.
I consider myself a Excel Power User – but wait!
shortcut : Menu key opens right click menu!! Seriously! How did I not know this!
Haha! I did the happy dance when I learned this one too. It’s amazing how many things there are to learn with Excel. 🙂
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
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.
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?
Hi Annamaria,
Unfortunately, it looks like that shortcut was introduced in Excel 2010.
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…
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.
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..
Thanks Anne! Congrats on the new job! 🙂
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?
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
Hi Ian,
In the checkbox listbox you can use the up/down arrows, and the page up/down or Home & End keys. I’m not sure I fully understand your question, but hope that helps.
I also have an article with more on the keyboard shortcuts for the filter drop-down menus.