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.
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.
❹ 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.
❺ 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.
❸ 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
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.
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.
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.
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.
❺ 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.
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.
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
- How to Filter Data in Excel using Formula
- How to Filter Cells with Formulas in Excel
- How to Use Filter in Protected Excel Sheet
- How to Filter in Excel with Merged Cells
- How to Filter Email Addresses in Excel
- How to Filter by List in Another Sheet in Excel
- How to Filter Column Based on Another Column in Excel
