Adding and applying Filter in Excel might not be too challenging. As we all know, the AutoFilter feature is used to hide particular sets of data and display the ones that you need to see at a particular time.
However, there might be cases when you need to look at the complete dataset of your worksheet without omitting any of them. These situations might require the removal of the filters that you applied before. So, how are you gonna pull it off?
Not to worry, in this one, we’ll be discussing how to remove filter in excel with complete details. Just follow the steps and you should be able to do it yourself with ease. Enough with the introductions, let’s get into it.
How to Mark Filters Used in Excel
The first thing you want to do before removing filters in an Excel worksheet is to recognize and mark where the filters are. To do this, all you need to do is, follow the steps below:
- There might be multiple dropdown buttons in your spreadsheet, you have to look for the ones that have the funnel icon.
- Also, if a particular row number is highlighted, it also means that some rows have been hidden.
Reasons to Remove Filter in Excel
We’re all aware that when it comes to sort and analyze data, Excel AutoFilter does a splendid job. However, there are some possible setbacks that may lead to the decision of removing them. Let’s point them out:
- When you’re dealing with a massive amount of data, the use of multiple filters may slow down your PC.
- If an incorrect filter is applied accidentally, it can become difficult to deal with.
- Filters make it rather difficult to take a look at all the data at once.
- A shared excel file containing filters might become hard to manage if the other user doesn’t have the same filter settings.
- Filters can be applied mistakenly and a small slip of the cursor can make things really difficult to handle.
5 Quick Ways to Remove Filter in Excel
Well, there are more than one way to get rid of a pre applied Filter in Excel. Each method is suitable for a particular type of scenario. We’re going to talk about five ways to go about it. So, let’s begin:
Removing Filter from a Single Column in Excel
To demonstrate the method, we’re going to use the dataset given below.
- The first thing you want to do is select the header where the filter is applied.
- You can easily recognize it by looking at the filter icon.
- Now, you have to click on the icon to see the dropdown menu.
- There you’ll find the Clear Filter From “Article Niche” option.
- Select it and the filter should disappear.
- The results should then look like the image below.
Clearing All Filters in Excel Worksheet
Now that we’ve learned how to remove Excel Filter in one column, let’s see how to do it in the entire worksheet.
- The first thing you’ll want to do is, go to the Data
- Under the Data Tab, there will be the Clear option under the Sort & Filter
- Select it and all the filters in the selected worksheet should disappear.
How to Remove Filter from Excel Table Entirely
This method is even simpler to be honest. All you have to do is this:
- Just like the previous method, you have to go to the Data
- Then go to Filter within the Sort & Filter
- Select it and all the filters will be completely removed from the entire table.
Note: This can also be done by pressing the Keyboard shortcut: Alt + A + T.
Removing Filter in Excel Using Keyboard Shortcut
A Keyboard shortcut can be used to remove all filters from a particular Dataset. But to do this, you firstly have to select the worksheet you’re willing to remove the filters from.
Let’s talk about how the sequence works:
- Press Alt to select all the tabs of the Ribbon.
- Then, pressing Alt + D will direct you towards the Data
- Alt + D + Fwill then select the Filter option of the Data
- Finally, press Alt + D + F + F to remove the Filter from the entire dataset.
- The First F applies the Filter command and the Second F removes it.
Using VBA Code to Remove Filter from Excel
To open the Microsoft Visual Basic for Applications window, press Alt + F11. Then go to Insert, and select Module.
Then, you’ll want to place the code below:
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
Now, all you have to do is, run the code from the Macros option under the View tab.
Wrap Up
Now that we’ve gone through all the basic information needed for you to remove filters in Excel, you should be able to get the job in no time. Then again, if time is of the essence, then you might want to follow the fourth method that I explained.
Plus, if you’re used to applying VBA codes, you can use that as well. With that said, let’s close this one up for now. Farewell!
Related Post: