Mukesh Dipto
Application of the excel filter shortcut in Microsoft Excel is a very convenient approach to use the filtering option. It not only saves our time but also increases the efficiency of our work. Generally, we use a filtering process to show certain data from a dataset under specific conditions.
Download Practice Workbook
You can download the practice workbook from here.
3 Examples with Keyboard Shortcut to Filter Excel Data
We will demonstrate 3 methods of using the excel filter shortcut since the filtering process is very necessary while working with a big dataset.
1. Use Filter Shortcut Option Under Data Tab to Filter Data
In this example, we will use the Data tab to filter data in an excel worksheet. To illustrate this method of excel filter shortcut we have the following dataset of sales amount categorized by the last name of salespersons and their working cities. Let’s take a look that how we can do this by following simple steps:
STEPS:
- Firstly, select any cell from the data range. For example, we will select cell B4.
- Next, go to the Data.
- Then, select the option “Filter” from the “Sort & Filter” section in the Data.
- Finally, we can see the filtering drop-down icons in the headers of our dataset.
Read More: How to Add Filter in Excel
2. Filter Excel Data with ‘Sort & Filter’ Option
In this method, we will make the filter icon visible by using the “Sort & Filter” option from the Home tab. Also, we will continue with our previous dataset for this method too. Let’s see the steps to perform this action.
STEPS:
- First, go to the Home tab.
- Secondly, select the option “Sort & Filter” from the “Editing” section of the Home.
- Thirdly, from the available options in the drop-down, select “Filter”.
- Lastly, we can view filtering drop-down icons in the headers of our data range.
Similar Readings
- How to Filter Unique Values in Excel (8 Easy Ways)
- Filter Excel Pivot Table (8 Effective Ways)
- How to Filter Multiple Rows in Excel (11 Suitable Approaches)
- Perform Custom Filter in Excel (5 Ways)
- How to Filter Multiple Values in One Cell in Excel (4 Methods)
3. Use Keyboard Shortcut Keys to Filter Excel Data
While using any kind of shortcut method, the most frequent term that comes to our mind is the use of keyboard shortcuts. In the case of applying the excel filter shortcut, we will use keyboard shortcuts also. In this segment, we will discuss 8 keyboard shortcuts to filter excel data.
3.1 Switching On or Off the Filtering Option in Excel
We can turn in or off the filtering process for any data range just with a simple keyboard shortcut method. Like the previous examples, we have the same dataset for this example too. To do this we have to follow the below steps.
STEPS:
- In the beginning, select a cell from the data range. We are selecting cell B4 in this example.
- Next, press Ctrl + Shift + L at the same time.
- So, we get filtering drop-down icons in the headers of our data range.
- Finally, if we press Ctrl + Shift + L, again the filtering drop-down icons will not be available in the header section anymore.
3.2 Use Keyboard Shortcut to View the Filter Drop-Down Menu
As we already know that we can access the filtering option by clicking on the filter icon. From this method, we will get to know how we can do the same thing by using keyboard shortcuts. We have the dataset of our previous example in which the filtering drop-down icons are available. So, just follow the below steps to perform this action.
STEPS:
- Firstly, select any cell from the header. We are going with cell B4.
- Secondly, press Alt + Down Arrow.
- Lastly, we can see the available option to filter the “Last Name”.
3.3 Select Option from Drop-Down Menu Using Keyboard
After opening the filtering options using Alt + Down arrow key in this example, we will select an option from the filtering drop-down menu. So, just follow the simple instructions to do this.
STEPS:
- Press Up or Down arrow keys to select a command from the opinions.
- After the section press Enter to apply that command.
3.4 Use Underlined Letters to Filter Excel Data
We can use underlined letters to get access to each command in the drop-down menu. So, just take a look at the following table to understand the use of underlined letters to apply the excel filter shortcut.
| SHORTCUT | SELECTED OPTION |
|---|---|
| Alt + Down Arrow + S | Sort Smallest to Largest or A to Z |
| Alt + Down Arrow + O | Sort Largest to Smallest or Z to A |
| Alt + Down Arrow + T | Open the submenu Sort by Color |
| Alt + Down Arrow + I | Access the submenu Filter by Color |
| Alt + Down Arrow + F | Select the submenu Date Filter |
Also, we can check/uncheck filtering items by pressing the Space Bar.
3.5 Filter Items with Search Box in Excel
In the filtering drop-down, we have a search box just above the list of filtering items. We can use the search box to find out our filtering term when there will be a lot of items in a dataset. Let’s take a look at how we can do this by following simple steps.
STEPS:
- Firstly, open the filtering drop-down menu in a dataset. Here, we will continue with our previous dataset.
- Secondly, press Alt + DownArrow + E. This will activate the search box.
- Thirdly, input text “Sm” in the search box.
- Lastly, we will see the filtering items only starting with text “Sm”.
3.6 Clear Filter from a Particular Column of a Data Range
In the following dataset, we have two filtered columns. Suppose, we want to remove filtering from the particular column “City”. If we press Ctrl + Shift + L all the filtering from our dataset will be removed. But that’s not what we want. So, to just clear filtering from a particular column follow the below steps.
STEPS:
- In the first, select the header cell C4.
- Next, press Alt + Down Arrow + C.
- Finally, we can view that there is no filter applicable in column “City”. But the filtering option in column “Last Name” remains unchanged.
3.7 Filter Excel Data with Custom Filter Dialogue Box
In this method, we will use a custom filter dialogue box to apply a filter for a specific data range. We will continue with our previous dataset for this example. We will filter the column “City” only for the city “New York”. Let’s go through the steps to perform this action.
STEPS:
- First, cell the header cell C4.
- Next, press Alt + Down Arrow to view the filtering drop-down menu.
- Then, press the keys F and E.
- So, a new dialogue box named “Custom Autofilter” will open.
- After that, input the parameters “equals” & “New York” in the input boxes named Check the option And.
- Now, press OK.
- Finally, we can see only the values for the city “New York”.
3.8 Use Keyboard Shortcut to Filter Blank or Non-Blank Cells
Sometimes our dataset will contain Blank cells. We can filter both the Blank cells and Non-Blank cells by using a keyboard shortcut. The following dataset contains Bank cells. We will filter both the Blank and Non-Blank cells individually. Let’s take a look at the steps of doing this.
STEPS:
- In the beginning, select header cell B4. Press Alt + Down.
- Next, press F and E respectively to open the custom filter dialogue box.
- Then, input drop-down value of Last Name section “equals”.
- After that, keep the second input box Blank.
- Now, press OK.
- So, we can see the filtered blank cells of the column “Last Name”.
Again, if we want to filter the Non-Blank cells for the column “Last Name”, just do the following steps.
STEPS:
- Firstly, select header cell B4. Hit Alt + Down.
- Secondly, press F and N This will open the custom filter dialogue box.
- Thirdly, input drop-down value of Last Name section “equals”.
- Next, keep the second input box, Blank.
- Now, press OK.
- In the end, we can view only the Non-Blank cells for the column “Last Name”.
Read More: How to Remove Filter in Excel
Things to Remember
- To apply filtering in more than one data range in a single worksheet we need to use the Excel Table Feature.
- We can use one filtering option at a time in a particular column. For example, we can not use a Text filter and Color filter at the same time in a column.
- Avoid using different kinds of data in the same column.
Conclusion
In the end, this article guides you to understand how to use the excel filter shortcut. We have illustrated various methods throughout this article to make the process easier for you. There is a practice workbook added with this article. So, download the workbook and practice yourself. If you feel any confusion just leave a comment in the below box we will try to answer you as soon as possible.
