Using Filter in Excel – A Detailed Guide

In this detailed guide, we will learn to apply, remove, clear, and reapply a filter.

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
• Reapply
• 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.