Bottom line: Learn a quick and useful shortcut to apply a filter based on a cell's value, color, or icon in Excel.
Skill level: Beginner
Video Tutorial
A Fast Way to Filter for Blanks, Errors, & Specific Values
Filtering for specific values can be a multi-step process in Excel. Especially when filtering for blanks or errors. You might find yourself scrolling to the bottom of the filter drop-down list box to apply these filters.
Fortunately, the right-click menu can save us some time here.
To apply a filter for a cell's value:
- Right-click a cell that contains the value you want to filter for.
- Choose Filter > Filter by Selected Cell's Value
- The filter will be applied to the column.
The filters will also be turned on if they are not already. This can save an additional step.
Another advantage is that you don't have to scroll up to the header row if you are not using an Excel Table or freeze panes.
Keyboard Shortcut to Filter by Cell's Value
We can also perform this task with a keyboard shortcut if your keyboard has a Menu Key.
The keyboard shortcut to Filter by the Selected Cell's Value is:
Menu Key, E, V
For me, this is the fastest way to apply a filter for a single value (criteria).
I explain more about the Menu Key in my article on the best keyboards for Excel keyboard shortcuts. I also share the Logitech keyboard that I use (pictured below) in that post.
The Menu Key is also one of the fastest ways to Paste Values and I think every Excel user's keyboard should have it.
Filtering for Colors & Icons
There are also options on the right-click menu to filter by the selected cell's: Color, Font Color, and Icon. The Icon is an icon that is created by conditional formatting.
The underlined letters in each of those menu items will be the last letter in the Menu Key, E shortcut.
Filtering for Blanks and Errors
You can use this technique to filter for any single value in a column. I use it most often for blanks and formula errors.
It's especially useful if you have a column of VLOOKUP formulas and want to quickly filter to see all cells that are returning an error (#N/A, #REF!, etc.)
Shortcut to Clear a Column Filter
We can also use the right-click menu to clear the filter applied to the column.
The keyboard shortcut to clear the column's filter is:
Menu Key, E, E
Thanks to Naveed for pointing this out in the comments below.
The keyboard shortcut to Clear All Filters in all columns is:
Alt, A, C
I explain more about that shortcut in section #7 of my post on filter shortcuts.
Conclusion
I hope you find this shortcut useful in your daily work. It's one of those that you tend to forget if you don't use it frequently. But can definitely be a time saver that is worth committing to memory.
Check out my article on 7 Keyboard Shortcuts for the Filter Drop-down menus to learn more filter shortcuts.
Please leave a comment below with any questions or suggestions. Thank you! 🙂
Thank you so much Jon for sharing this Filter shortcut tips! I am feeling lucky that I found your website. Your sharing tips inspired me in learning and doing things efficiently.
Thanks again
Thank you Thanh! I’m happy to have you joining us and love your passion for learning. Awesome! 🙂
Found all the contents really very useful.. ☺
Thanks Jayesh! 🙂
A related shortcut –
To remove filter = Right Click Menu (in filtered column), then E, E
Love it! Thanks Naveed! I’ll add this one to the post. 🙂
Dear brother, Suppose I have 20 numbers of value. I want amount only above 5000. How can I get ?
Thanks Jon, for sharing these tips. They are extremely helpful and much appreciated. You are a wealth of knowledge.
Thank you, Lisa! 🙂
Thanks, Learning something new every day.
Thank you, Robert! 🙂
If your keyboard does not have a Menu Key, or if you just want to learn a shortcut that will work on all keyboards, then you can use Shift+F10.
Where do I go to see a video on building charts. In and out of a pivot Table?
THanks
Wonderful information, it helps me a lot in my project, thanks for sharing with us.
Regards
Jam sakky
Fantastic information, I love to read this post bcoz I learned a lot of new things from your blog, thanks for sharing with us.
As always Jon, excellent training style. Modulated, repetition, say it and show it. Makes it easy for all levels of learners to catch on! Great topics.
Thanks! In all these years, I never noticed the Filter and Sort items in the cell context menu. And I’d never used the Menu key on my keyboard. I’ll definitely be using it in the future.
Thanks a lot for all your emails, especially this great timesaver. Your shortcut for “Clear All Filters” doesn’t seem to work, it might be just my version of Excel, so instead of Alt, A, C, I would do Alt, A, T.
Hi Jon, is there any quick way to select multiple items in a filter list ie. when you select the filter list and the drop down appears, is there any way to select multiple entries other than selecting each one individually?
My hobby is trains, and i want to look up how many of one item i have: example would be, I have 20 “Box Car” with different Box numbers, and i want list to show all “Box Car” with there number and name brand, do you have a video i can watch: thanks
Hi Jon, I am learning a lot from your Power Query tutorial, however every time I summarized a spreadsheet using unpivot column, there are columns that does not match with the total in the original spreadsheet where I got the data from. hope you can help me on this. a difference of sometimes 2 or less
Hello Jon
I have an excel file and using this future by right click and select Filter and select value and every thing is OK …. but suddenly after work on table press right click on cell and not found Filter option in list …. I just found Table > … Appreciate your help
I found the reason for this …. I activate the page break preview in this sheet so, this is the reason for why filter option in right click list not found … just make preview normal and it will come appear
[…] Source: Shortcut to Filter by Cell’s Value in Excel – Excel Campus […]
This is a life saver! Thank you!
Thank you so much Jon for sharing this filter tip. This will be very useful for my daily work.
How can add the “Filter by selected cell’s value” to my quick access toolbar? Thanks