Excel Shortcut Roundup: Filtering & Sorting Data
In these Excel Shortcut Roundup posts, we’ll look at the most relevant and important shortcuts in Microsoft Excel, each time looking at a specific category. We’ll also try and help you memorise these shortcuts without too much difficulty, so you can incorporate them into your normal Excel use.
Sorting and filtering a range of data in Excel a necessary and common task. Whether it’s getting a simple list of names and academic scores for the subject you’re teaching, or sales data from the dozens of stores that you need to keep track of, you need to be able to quickly find the data you need.
Being able to filter or sort data quickly means that you spend less time navigating through menus or clicking in the wrong place with your mouse. It also allows you to show off to your friends or colleagues!
Read on to get the most useful shortcuts to present and arrange your data instantly in Microsoft Excel.
Learn these straightforward shortcuts to get around the basics of sorting and filtering in Excel.
Alt-A-T: Turn filters on/off for the selected cells (alternatively Ctrl + Shift + L)
Alt-A-C: Clear current filters
Alt-A-S-A: Sort currently selected column (ascending)
Alt-A-S-D: Sort currently selected column (descending)
Alt-A-S-S: Custom Sort (surely this one is easy to remember!)
The best thing about these shortcuts is that you can enter them using your left hand only. Which means you can still use the mouse to scroll through your data and select any specific cell you like!
Note: even if your data isn’t filtered, Excel will look at the data surrounding the selected cell, and sort that data range.
Filtering on Specific Cells
When I’ve been analysing large data ranges, the below two formulas have been absolutely invaluable.
These shortcuts use the Menu key, which is present in most modern keyboards next to the right Ctrl key. If your keyboard doesn’t have a Menu key, you can press Shift + F10 instead.
Menu-E-V: Filter on selected value
Menu-E-C: Filter on selected color
Recently, I’ve been a fan of selecting the relevant value in my data, right-clicking on it then pressing E-C or E-V. In my experience, most people are likely to review your data by scrolling through it or clicking occasionally, so it’s handy to keep your hand on the mouse while your left hand helps out with shortcuts. Give it a go!
If you’re a fan of highlighting your cells in order to easily filter on their colour, check out the Formatting Shortcut Roundup so you can format instantly!
Opening the Drop Down Filter Menu
If your data already has filters enabled, you will probably want to make use of the drop down menus on each of the column headers.
The main shortcut you’ll want to learn is this one:
Alt + Down Arrow: Open the drop down menu.
If you use the Alt+Down shortcut on any other cell, Excel will show the list of values that are already entered in that column. This shortcut is helpful when there are a small number of values that should be in that column (eg: departments, categories or statuses).
Apart from Alt+Down, here are some helpful shortcuts to easily select the options in these menus:
Spacebar: Select or deselect individual values to apply filtering on
Enter: Confirm sorting/filtering options
Alt + Down Arrow, then C: Clear filter from that column
Alt + Down Arrow, then S: Sort column ascending (same as Alt-A-S-A)
Alt + Down Arrow, then O: Sort column descending (same as Alt-A-S-D)
Alt + Down Arrow, then F-A: Filter rows if text contains… (only works for columns that contain text)
With these easy shortcuts, you should have no problem arranging your data in just a few keystrokes.
Depending on your personal preference, you can use the mouse in combination with many of these
Are there any other specific shortcuts that you use in your daily Excel workflow?