In this detailed guide, we will learn to apply, remove, clear, and reapply a filter.
What is Filter in Excel?
A filter is an option to filter data from a single column or multiple columns in Excel. It allows you to have a drop-down on a column to get a list of values you have, and then you can select one or multiple values to filter. It’s helpful to analyze a large set of data.
What is FILTER used for?
In Excel, the filter filters an extensive data set to get the information for specific criteria. Take the example below, where we list the first name, last name, state, and zip codes.
Now, when I use the filter on the state name and filter “AK” it only shows me the entries where names are from the state “AK.” In the same way, you can also filter the data using the zip code.
How to Apply Filter in Excel
There are two quick ways to apply a filter to the data in Excel:
- Apply Filter Button
- Keyboard Shortcut
- Select the entire data or a single cell from the data.
- Next, open the Data Tab.
- After that, go to the Sort and Filter Group.
- Finally, click on the “Filter” button to apply the filter to the data.
Keyboard Shortcut to Apply Filter in Excel
- Select the entire data or a single cell from the data.
- Press and hold the Ctrl + Shift Keys.
- And then press the key “L”.
So, the keyboard shortcut will be Ctrl + Shift + L. And there’s one more keyboard shortcut which you can use: Alt > D > F > F.
Note 1 – If you want to apply a filter to specific columns in a large data set, in that case, you need to select only those columns (let’s say two) and use the method we have discussed above to apply the filter.
Note 2 – You can also apply the filter button to the quick access toolbar. For this, you must right-click the filter button and click “Add to Quick Access Toolbar.”
Note 3 – You can also use the right-click menu to apply the filter. For this, you need to select a cell, right-click it, and then use the options to apply the filter. For this, we have a detailed explanation ahead.
Open Filter in Excel
You have two ways to open the filter once you apply the filter on a column.
- Click on the filter button.
- Select the header cell and then open the filter using the keyboard shortcut Alt + Down.
Use Filter in Excel
In Excel, the filter has a vast and vital use when working with data.
1. Basic Filter Options
When you open the filter from a column header, you can see that there are multiple options to use.
One of the easiest ways to filter values is using the search bar. When you type a value in the search bar, it will show you all the values which contain that value.
You can also use the mouse to check mark values you want to filter. Or, you can use the spacebar to check mark a value if you’re going to use the keyboard shortcut.
If you have a long list of values in the filter drop-down, you can use the scroll bar on the right side to move the list.
Once you tick-marker the values you want to filter, click the “OK” button to apply the filter.
2. Text Filter Options
When you apply a filter to a column where you have text values, it shows you “Text Filter” options. You can use these options to get
In the test filter options, you can use six pre-defined options.
- Equals – To filter exact match value.
- Does Not Equal – To filter values that don’t equal the value you define.
- Begin With – To filter values that begin with a specific value.
- Ends With – To filter values that end with a specific value.
- Contain – To filter values that contain the value you specify.
- Does Not Contain – To filter values that do not contain the value you specify.
In the end, there’s also an option to open the custom filter dialog box. You can find all the options we discussed above in this dialog box.
Let’s take an example to understand how to use these options. From the state column, you want to filter values that start with the alphabet A. You need to open the filter, go to the Text Filters option, and click “Begins With.”
Now, in the custom autofilter dialog box that begins with is already selected, you must enter “A” in the input bar.
In the end, click OK to apply the filter. Now you can see in the below snap show that it has filtered all the values that start with the alphabet A.
3. Number Filter Options
Like text filters, there are specific options when applying a filter on a column with a number.
In the “Number Filters,” you can use the options like:
- Less Than or Greater than to filter values that are greater or lower than the value you specify.
- Use above or below average to filter values above or below the average of the values you have in the column.
- Top 10 to get the top ten values from all your values in the column.
Let’s take an example to understand how to use these options. Let’s use the between options to filter values between 10000 and 11000.
When you click on this option, it opens the custom filter option with a pre-defined condition applied to filter the values between two numbers.
Now, you must enter the first lower number in the first input bar and the higher number in the second input bar.
Finally, click OK to apply the filter to the column.
4. Date Filter Options
In the same way, when you have dates in a column, in that case, you can get options related to dates.
There are options to filter dates based on weeks, months, quarters, and years. You can also filter dates up to a specific date or range.
Let’s take an example to understand how to use these options. Let’s say you need to filter all the dates that are from the last year.
For this, you have a pre-defined option to use. You need to open the filter and then, in the “Date Filters,” click on the “Last Year.”
When you click it, it filters the dates from the last years.
And when you click on the custom filter, you can see a custom filter already created to filter last year’s dates.
There’s a custom filter at the backend for each pre-defined option you have there to use.
These options help you save time applying a custom filter of your own. But you can still use a custom filter to create a condition to filter dates.
While using the date filters, in the custom filter option, you can use the date picker to select the date you want to enter in the input bar.
Note – While using the filter in Excel, you can use wildcard characters with it. And here’s a complete guide to learn about these.
Use Filter from Right Click Menu
You can also apply and use the filter from the right-click menu. Once you right-click on the cell, you can go to the Filter option. And in the filter option, you will find the list of options.
All the essential options are there for you to use:
- Clear Filter
- Filter values that match the value of the selected cell.
- Filter values that match the cell color of the selected cell.
- Filter values that match the font color of the selected cell.
- Filter values that match the selected cell’s Icon (Conditional Formatting).
Filter by Cell Color and Font Color
In the example below, we have a few cells with the cell color, and Excel gives you options to filter cells with the cell color and font color.
When you open the filter drop-down, you can filter using the cell color.
If you have font color applied, you will also have the option to filter cells using those colors.
Clear a Filter
You can use the “Clear” button from the data tab to clear a filter and show all the values back.
You can also use the clear option from the filter drop-down.
Note – The first method works best if you have applied a filter to multiple columns and want to remove it. With the second method, you need to open the filter drop-down for each column and then clear the filter one by one.
Reapply a Filter
There’s also an option to reapply the filter. Let’s say you have to filter a specific value from a column and then add a new value that is not the filtered value.
In the above example, you can see that we have applied the filter on the state column for the “AK” state, but there’s a new entry with the state name “LA.”
Now, to filter out this entry and only show the entries for the state “AK,” you need to reply to the filter. You must go to the Data Tab and click the “Reapply” button to reapply the filter.
Note – You can also use the reapply options from the right-click menu. Check this tutorial’s “Use Filter from Right Click Menu” section for details.
Multi Column Filter
Yes, you can apply a filter to multiple columns simultaneously to filter data. Once you use the filter to the data:
Then, you can apply the filter to the first column and then open the filter from the second column filter values.
You can see that we have applied two filters:
- The first is on the state column, where we have filtered the states “AK” and “CA.”
- Second is the column data, where we filtered the dates that fall in 2024.
Remove a Filter in Excel
If you want to remove a filter, you need to repeat the method which you have used to apply the filter:
- Click on the Filter button.
- Use the keyboard shortcut Ctrl + Shift + L or Alt > D > F > F.
Copy Filtered Data
When you want to filter data, you can copy that data and then paste it into another range. You can see in the below example that we have a filter “CA” state. After filtering, we selected data along with headers.
Then press Ctrl + C to copy data, go to the range where you want to paste the data, and then use the keyboard shortcut to Ctrl + V to paste the values.
Using the SUBTOTAL Function with the Filters in Excel
You can use the SUBTOTAL function while using filters in Excel. The SUBTOTAL function helps you create a dynamic formula to get count, sum, max, min, etc.
In the SUBTOTAL function, in the first argument, you can choose the calculation you want to get when you filter the data. In the below example, we have used the SUBTOTAL to get the count of the values filtered.
In the first argument, we have chosen 3 (COUNTA), which tells the function to count the cells that are not blank. In the second argument, we have specified the entire range.