Microsoft Excel is a powerful software. We do not find the hidden data in our spreadsheet if we take a filtered dataset. However, we can now access new Excel features to withdraw advanced filters quickly and effectively to redisplay our data. With this in mind, we will start from scratch and show you 5 effective ways to remove advanced filter in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the steps.
[wpsm_box type=”download” float=”none” textalign=”left”]
[/wpsm_box]
5 Effective Ways to Remove Advanced Filter in Excel
Removing Advanced Filters can be cumbersome if we consider a large dataset. Fortunately, it is not an impossible task. Withdrawing an advanced filter is rather easier if we can implement these 5 methods perfectly. So, let’s jump into the article and learn the necessary steps to redisplay our hidden data. To demonstrate, I have taken a dataset that represents a duty roster. Also, we have applied the Advanced Filters in it. To know if a dataset is Advanced filtered, look left at the row index numbers. If they appear in blue, it means they are filtered. Alternatively, they will appear in black if there is no filter.
1. Apply Clear Feature to Remove Advanced Filter
The first step aims to apply the Excel Clear feature to delete advanced filters in our dataset. The Excel Clear feature is a useful tool to reverse any selected range to its original state. Also, this feature can work on the whole dataset at once and redisplay the hidden data. Therefore, follow the steps to see how this useful tool works.
Steps:
- Firstly, select the range B4:H13.
- Afterward, go to the Data tab and click Clear in the Sort & Filter group.
- As a result, the filter is removed as the row index numbers appear in black.
- Thus, the hidden rows are visible in our dataset.
Read More: Apply Advanced Filter Based on Multiple Criteria in One Column in Excel
2. Use Keyboard Shortcut to Delete Advance Filter/Filters in Excel
The objective of the second method is to completely and quickly remove data using a keyboard shortcut. See these easy steps to implement this.
Steps:
- First, open the active sheet and press the Alt + D + F + A keys.
- Subsequently, the Advanced Filter dialog box appears.
- There, select the range you wish to filter in the List range.
- Further, uncheck the Unique records only checkbox.
- Finally, close the dialog box by tapping OK.
- Hence, we can see the hidden rows in our display.
- Therefore, the advanced filters are not there anymore.
Read More: How to Use Advanced Filter for Unique Records Only in Excel
3. Manually Unhide Rows to Turn Off Advanced Filter from Specific Rows
The goal of the third method is to delete advanced filters by unhiding rows manually from our dataset. As we already mentioned, the blue row index represents that an advanced filter is used. We can hover our mouse there and clear the filters. Follow the below steps to do so.
Steps:
- To abegin with, right-click on the gap between rows 8 & 10.
- Consequently, a context menu pops up.
- Next, scroll down and click the Unhide option.
- Thus, the hidden cells are visible again.
- Therefore, we withdraw the Advanced filter.
Read More: Advanced Filter with Criteria Range in Excel (18 Applications)
Similar Readings
- Excel Advanced Filter: Apply “Does Not Contain” (2 Methods)
- How to Use Advanced Filter If Criteria Range Contains Text in Excel
- Excel Advanced Filter Not Working (2 Reasons & Solutions)
- How to Use the Advanced Filter in VBA (A Step-by-Step Guideline)
- Create Dynamic Advanced Filter in Excel (2 Applications)
4. Using Filter Tool to Withdraw Advanced Filter in Excel
In this step, we will thoroughly use the Excel Filter tool to implement the procedure. Once we apply the Filter tool it becomes clickable again. However, we can use the tool again to reverse the situation. Follow the described steps.
Steps:
- First, go to the Data tab and click Filter in the Sort & Filter group.
- Alternatively, we can use a keyboard shortcut to access the Filter feature.
- Press the Ctrl + Shift + L buttons.
- Thus, the hidden cells are visible after removing the advanced filters.
Read More: Advanced Filter with Multiple Criteria in Excel (15 Suitable Examples)
5. Run a VBA Code to Remove Advanced Filter in Excel
In our last step, we will try a VBA code to implement this. The VBA FilterMode immediately tests if the advanced filter is available. If it is True, then it removes the filter using the ShowAllData function. See the procedure below.
Steps:
- Firstly, go to the Developer tab and click Visual Basic.
- Eventually, the Visual Basic window appears.
- Afterward, tap Insert and then Module to create a module.
- In the module box, type the following VBA code:
[wpsm_box type=”solid_border” float=”none” textalign=”left”]
Sub Remove_Adv_Filter()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub[/wpsm_box]- Later, use the green Run button to execute the code.
- Save the code in Excel Macro-Enabled Workbook and close the window.
- Thus, the hidden cells are visible again.
- Therefore, the macro removes the advanced filters from the whole dataset.
Read More: Excel VBA Examples with Advanced Filter Criteria (6 Cases)
Conclusion
The above-mentioned steps to remove advanced filters in Excel will now provoke you with productivity to apply them in your spreadsheets. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.
Related Articles
- How to Use Advanced Filter to Exclude Blank Cells in Excel (3 Easy Tricks)
- Excel VBA: Advanced Filter with Multiple Criteria in a Range (5 Methods)
- How to Use Custom Autofilter in Excel for More Than 2 Criteria
- VBA to Copy Data to Another Sheet with Advanced Filter in Excel
- How to Use Advanced Filter to Copy Data to Another Sheet in Excel
- Apply the Advanced Filter to Copy to Another Location in Excel
- How to Use Auto Filter and Advanced Filter in Excel
