In this tutorial we’ll take a look at basic filtering in Excel, but more specifically how to filter by selection. We will also discuss how to do all this without the use of a mouse, utilizing keyboard shortcuts or key tips only.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here we have a data set with varying information, some in color:
If we wanted to filter the data by, say, the New Orleans location, we would need to take the following steps:
1) Click into the data set
2) Go to the Data tab
3) Activate Filters
4) Click the down arrow on the Location header
5) Uncheck Select All
6) Click on New Orleans
7) Click OK
And we would have this result:
To avoid that many clicks, we can just do the following:
1) Without activating the Filter option, right-click on “New Orleans”
2) Select “Filter”
3) Select “Filter by Selected Cell’s Value”
And we would end up with the same result as above.
Now, how can we do all this without the mouse? Well, there are a few options.
One way is to add an Auto Filter command to the QAT (Quick Access Toolbar). Here is how you do that:
1) Click the down arrow at the end of the QAT and choose “More Commands”
2) Select All Commands
3) Scroll down to Auto Filter
4) Click Add
5) Click OK
This has now added an command to the QAT. All you need to now is select any cell that you want to filter by, click on that command, and it will automatically filter the data by that selection. But I said that we were going to do this without the use of a mouse! That’s where Key Tips comes in. Take the following steps:
1) Move your cursor using the cursor movement keys to position the active cell based on what you want to filter the data by (e.g. “New Orleans”)
2) Hit the “Alt” key
3) Hit what ever key or combinations of keys necessary to activate that QAT option. I’ve moved the Auto Filter option up so that, in the example below, I hit the “9” key:
Previously, we talked about right-clicking on any cell within the data set, choosing Filter and then selecting Value, Color, or other option to filter our data by. In order to use this method without using a mouse, you need to know one of two shortcuts to simulate a right-mouse click. Those are:
1) Shift + F10
2) Menu key
With either of these, move your cursor to the cell you want to filter your data by (e.g. “New Orleans”), then use one of those two options and then choose “Filter” (hit the “E” key) and then the method for filtering your data (“V” for Value, “C” for Color, etc). The Menu key looks like this:
And that’s all there is to Filtering by Selection!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!