How to Add Filter in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

A Filter is a useful tool that helps us display only the specified values in Excel. Based on the filtered result, we can later edit, copy, chart, or print only the visible values. In this article, you will learn 4 methods to add Filter in Excel.


What is Filter in Excel?

The Filter in Excel which is also known as AutoFilter is a tool that you will find in the DATA menu under the Sort & Filter group. This tool allows you to apply the Filter command upon a range of cells or an Excel Table.

The Filter command is used to choose certain data over a range of data in Excel. Based on the filtered result, you can perform several operations such as print, copy, edit, chart, etc.

What is Filter in Excel?


4 Methods to Add Filter in Excel

1. Add Filter From the Data Tab in Excel

I’m using a Product Price List to demonstrate to you to add a Filter from the Data menu in Excel.

I will filter out the data table based on the Category column. Here my aim is to filter out all the products that are only under the Wafer category.

To do that,

❶ Select the entire data table first.

❷ Then go to the DATA tab. You will find this menu in the main ribbon.

❸ From the Sort & Filter group, choose Filter.

Add Filter From the Data Tab in Excel

❹ Now click on the drop-down icon to avail of the filter options.

❺ Mark ticks on the Water from the category list.

❻ Then hit OK.

Now you will see only the products that fall under the Wafer category.

Read More: Excel Filter Data Based on Cell Value


2. Add Filter From the HOME Tab in Excel

This time, I will filter all the products that fall under the Biscuit category.

Here are the steps to follow:

❶ Highlight the whole table first.

❷ Then go to the HOME tab.

❸ Under the Editing group, you will find Sort & Filter. Just click on it.

❹ From the drop-down menu hit the Filter command.

Add Filter From the HOME Tab in Excel

❺ Now click on the drop-down icon at the right-bottom corner of the Category column to avail all the filtering options.

❻ Mark ticks only the Biscuit from the category list.

❼ Finally, hit the OK command.

After that, you will see only those products that fall under the Biscuit category.


3. Right-click to Add Filter in Excel

This method will allow you to quickly add the Filter command in Excel.

To do that,

❶ First select the entire data table and right-click on it.

❷ Go to Filter > Filter by Selected Cell’s Value.

Right-click to Add Filter in Excel

❸ Click on the Filter icon. You will find it on the right-bottom corner of the Category column.

❹ Select a category from the list. For instance, I’m selecting Pasta to avail all products under the Pasta category.

❺ Then hit the OK command.

After that, you will see only the products under the Pasta category.


4. Add Filter in Excel Shortcut

To work faster in Excel, you can use shortcuts. The shortcut key for adding Filter in Excel is

CTRL + SHIFT + L

To use this shortcut key,

❶ Select the whole data table first.

❷ Then press CTRL + SHIFT + L. This will instantly add the Filter to the data table.

❸ Click on the drop-down icon.

❹ Mark ticks on Candies to get all the product details under the Candies category.

❺ Hit the OK command.

Add Filter in Excel Shortcut

When you are done with all the steps above, you will see only the products under the Candies category.

Read More: Shortcut for Excel Filter


5 Methods to Clear Filter in Excel

There are several methods available for clearing Filter in Excel. Let’s get into them all one by one.


1. Clear Filter from a Single Column in Excel

To clear Filter from only a single in Excel,

❶ Click on the Filter icon from your applied column header.

❷ From the drop-down list, select Clear Filter from “Category”.

❸ Then hit the OK command.

Clear Filter from a Single Column in Excel

Read More: How to Remove Filter in Excel


2. Clear Filter from Multiple Columns in Excel

If you want to remove the Filter from multiple columns in Excel, follow the steps below:

❶ Select the whole data table first.

❷ Go to the DATA tab from the main ribbon.

❸ From the Sort & Filter group, click on Clear.

Clear Filter from Multiple Columns in Excel

Read More: How to Filter Multiple Columns in Excel Independently


3. Remove Filter from All the Worksheets in a Single Workbook in Excel Using VBA code

Use the following VBA code to remove Filter from all the worksheets in a single Workbook in Excel.

❶ Press ALT + F11 to open the VBA editor.

❷ Create a new Module from the Insert tab.

Copy the following VBA and Paste it to the VBA editor.

Sub ClrFltr()

Dim A As AutoFilter

Dim B As Filters

Dim C As ListObjects

Dim D As ListObject

Dim E As Range

Dim F As Worksheet

Dim G, H, M, N As Integer

Application.ScreenUpdating = False

On Error Resume Next

For Each F In Application.Worksheets

F.ShowAllData

Set C = F.ListObjects

N = C.Count

For H = 1 To N

Set D = C.Item(H)

Set E = D.Range

G = E.Columns.Count

For M = 1 To G

D.Range.AutoFilter Field:=M

Next

Next

Next

Application.ScreenUpdating = True

End Sub

❹ Save the above code.

Remove Filter from All the Worksheets in a Single Workbook in Excel Using VBA code

❺ Now hit the Run Sub button or press the F5 key to run the above VBA code.

That’s all you need to do to remove the Filter from all the worksheets in a single workbook in Excel.


4. Remove Filter From a Single Workbook in Excel

To clear Filter from a single workbook,

❶ Go to the DATA tab.

❷ From the Sort & Filter group, select Filter.

Remove Filter From a Single Workbook in Excel


5. Shortcut to Remove Filter in Excel

To remove Filter using a shortcut key,

❶ Select the data table first.

❷ Then press CTRL + SHIFT + L.

That’s it.

Shortcut to Remove Filter in Excel


Excel Filter not Working?

Cause

If you enter new data outside of the selection area where you have applied the Filter, AutoFilter may not work in this case.

Solution

  • Clear the applied Filter first.
  • Then select all the data again.
  • Apply the Filter now.

Read More: [Fixed!] Excel Filter Stops at Blank Row


Things to Remember

  • Select all the cells before applying the Filter.
  • Press CTRL + SHIFT + L to apply the AutoFilter.

Download the Practice Workbook

You can download the Excel file from the following link and practice along with it.


Conclusion

To sum up, we have discussed 4 methods to add and 5 methods to remove Filter in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo