I work with large amounts of data in Excel and am always looking for ways to apply or remove filters across my datasets.
A filter in Excel is a powerful tool for narrowing down your data in Excel that meets certain criteria that you can define.
For example, you can use it to display only those records where the price exceeds $50 or filter records only for a specific product or region.
Knowing the shortcut to apply and remove filters in Excel can save you a lot of time.
Keyboard Shortcut to Apply Filter in Excel
Here is the keyboard shortcut to apply filters in Excel:
Control + Shift + L
How to Use the Keyboard Shortcut to Apply Filter in Excel
Here are the steps to use the keyboard shortcut mentioned above:
- Select any cell in the dataset in which you want to apply the filters
- Hold the Control key and the Shift key, and then press the L key
When you press this keyboard shortcut, Excel applies the filters to the current column, as well as to all the adjacent columns which are part of the dataset
The shortcut would apply filters in the column headers of your dataset.
And if there are filters already present in the data, using the shortcut would remove the filters.
An alternative to the above shortcut that you can use to apply or remove filters is ALT + A + T
Press the ALT key, followed by A and T keys (one after the other) to use this shortcut. This will work in an identical manner to the above shortcut!
Using the Filter Option in the Ribbon
You can also the Filter option in the Ribbon to apply filters (or remove filters) in any data set:
- Select any cell in the data range
- Click on the Data tab and then click on the Filter option
- Select Text filters or Number Filters (depending on the dataset)
- Enter the criteria and then click OK to apply the filter
Some Useful Things to Know about Filter in Excel
Excel allows you to filter data on the following conditions – Value, Text, or Color
- If your dataset contains numeric values, you can filter based on the values (greater than, less than)
- If your dataset contains text values, you can filter based on text conditions such as Begins with, Contains, Equals
- You can also Filter by Color (by Cell Color or by Font Color)
- Excel automatically detects the data range to apply the filter based on the current selection. Make sure you select a cell within the correct range to avoid applying filters to the wrong set of data.
- Filters work best when your data range includes headers. Excel uses the first row of your selected range as headers by default when applying filters.
- Once filters are applied, each header will have a drop-down menu. You can click these to specify your filtering criteria for each column – Excel allows filtering by text, numbers, dates, colors, and custom criteria.
- Excel functions that summarize data, such as SUM, AVERAGE, COUNT, MAX, MIN, and others, are not affected by filters.
- To specifically cater to the formula’s dynamic, Excel provides the SUBTOTAL function. This function is designed to perform calculations like sum, average, count, etc., on filtered data, ignoring hidden rows.
Filters are extremely useful when you want to analyze your data and quickly get different perspectives.
Other Excel shortcuts you may also like:
- Excel Keyboard Shortcuts
- Clear Contents in Excel (Shortcut)
- Format Painter Shortcut in Excel
- Insert Pivot Table in Excel (Shortcut)
- Shortcut to Apply Borders in Excel
- Shortcut to Remove Duplicates in Excel
- Shortcut to Insert Drop Down in Excel
Other Related Excel articles you may also like: