In the Excel sheet, we do apply Filter to do an analysis of particular data. While showing the required data Filter hides other data. Anyone may need the other data for further analysis or any other purpose. To get data back all the data from your sheet you will need to remove Filter. In this article, I’m going to explain how to remove Filter in Excel.
For the purpose of demonstration, I’m going to use a sample dataset of a particular salesperson’s sales information. The dataset contains 4 columns; these are SalesPerson, Region, Month, and Sales.
Download to Practice
How to Know If Filter Has Been Used?
Before removing the Filter, you will need to make sure that the Filter is applied to your dataset. For that, you’ll need to look into the header of your dataset or the table.
If the drop-down icon is converted into a funnel icon that will mean that Filter is applied. Also, if the row number is Highlighted that will also mean that some rows are hidden.
5 Easy Ways to Remove Filter in Excel
1. Remove Filter from Specific Column in Excel
Depending on your need you can remove Filter. If you want to remove Filter from a specific column then you can do it easily.
To demonstrate the procedure to you, I’ve taken a dataset where I applied Filter in the Region column.
To know how to apply Filter you can check this article Filter Data.
First, select the header where Filter is applied.
⏩ I selected Region column header.
Next, right click on the mouse and it will open a context menu.
⏩ Select Clear Filter From “Region”.
Hence, it will remove the Filter from the Region column, and you will get back all the datasets.
2. Remove Filter from All Columns at Once
In the case in all columns or in multiple columns is with Filter then you also can remove all Filters at once.
Let me show you the procedure,
Here, I’ve taken a dataset where I applied Filter in the Region and Month column.
First, open the Data tab >> from Sort & Filter >> select Clear
Therefore, it will remove the Filter from the columns.
If you want you can use the keyboard shortcut ALT + A + C to remove Filter from all columns.
3. Remove Filter from the Entire Excel Table
If you want to remove the Filter from the entire table, you can do it by using the ribbon feature.
Here, I want to remove the drop-down of Filter.
To begin with,
Open the Data tab >> from Sort & Filter >> select Filter
As a result, it will remove the Filter from the entire table.
A similar operation you can perform by using keyboard shortcuts; ALT + A + T.
Similar Readings
- How to Filter Unique Values in Excel (8 Easy Ways)
- Shortcut for Excel Filter (3 Quick Uses with Examples)
- How to Use Text Filter in Excel (5 Examples)
- Filter Horizontal Data in Excel (3 Methods)
4. Shortcut to Remove All Filters
If you want, you can use the Keyboard Shortcut to remove Filter from the dataset.
The keyboard shortcut is ALT + D + F + F
Open the sheet from where you want to remove the Filter then press the keyboard shortcut to remove the Filter.
I’m going to describe to you how the keyboard sequence works.
First, press ALT.
It will select all the tabs of Ribbon.
Second, ALT + D will redirect to the Data tab.
Then, ALT + D + F will select the Filter command of the Data tab.
Finally, ALT + D + F + F will remove the Filter from the dataset. (One click on Filter command applies Filter another click removes it)
You also can use the keyboard shortcut CTRL + SHIFT + L to apply or to remove the Filter.
Open the sheet then press the CTRL + SHIFT + L key to remove Filter from your sheet.
Hence, it will remove the Filter from the dataset.
5. Using VBA to Remove Filters from All Worksheets of Workbook
In case your workbook contains multiple worksheets where Filter is applied rather than removing all the Filter manually you can use the VBA to remove Filters from all of your worksheets at once.
Let me show you, in which worksheets of my workbook Filter are applied.
In the All_Column sheet.
In the Remove Filter From Specific Col sheet.
Also, in the From Entire Table sheet.
To open the VBA editor,
First, open the Developer tab >> select Visual Basic
➤ Now, a new window of Microsoft Visual Basic for Applications will appear.
Next, from Insert >> select Module
Now, type the following code in the Module.
Sub Remove_Filter_From_All_Worksheet()
Dim AF As AutoFilter
Dim Fs As Filters
Dim Lob As ListObjects
Dim Lo As ListObject
Dim Rg As Range
Dim WS As Worksheet
Dim IntC, F1, F2, Count As Integer
Application.ScreenUpdating = False
On Error Resume Next
For Each WS In Application.Worksheets
WS.ShowAllData
Set Lob = WS.ListObjects
Count = Lob.Count
For F1 = 1 To Count
Set Lo = Lob.Item(F1)
Set Rg = Lo.Range
IntC = Rg.Columns.Count
For F2 = 1 To IntC
Lo.Range.AutoFilter Field:=F2
Next
Next
Next
Application.ScreenUpdating = True
End Sub
Here, in the Remove_Filter_From_All_Worksheet sub-procedure, I declared the variable AF as AutoFilter, Fs As Filters, Lob as ListObjects, Lo as ListObject, Rg as Range, and WS as Worksheet.
Also, as Integer I declared IntC, F1, and F2.
Then, I used a nested For loop to look for while Filter is applied and it will remove the Filter from each worksheet.
Now, Save the code and go back to any worksheet to run the VBA code.
Then, open the View tab >> from Macros >> select View Macros
A dialog box will appear. From there select Macros name and Macros in.
⏩ From Macros name I selected Remove_Filter_From_All_Worksheet. In Macros in selected the current worksheet How to Remove Filter in Excel.xlsm.
Then, click Run.
Hence, it will remove the applied Filter from all the sheets.
You can see, the applied Filter is removed from the sheet From Entire Table.
Practice Section
I’ve provided a practice sheet in the workbook to practice these explained examples.
Conclusion
In this article, I have shown 5 ways to remove Filter in Excel. These ways will help you to remove Filters easily. Feel free to comment down below for any types of queries and suggestions.
Further Readings
- How to Perform Custom Filter in Excel (5 Ways)
- Filter by Color in Excel (2 Examples)
- How to Filter Data in Excel using Formula
- Use Profit Percentage Formula in Excel (3 Examples)