How to Remove Advanced Filter in Excel (5 Effective Ways)

We do not find the hidden data in our Excel 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.

How to Remove Advanced Filter in Excel: 5 Effective Ways

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.

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.

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.

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.

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:

Sub Remove_Adv_Filter()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub
			

• 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.

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.

Conclusion

The above-mentioned steps to remove advanced filters in Excel will now provoke you with productivity to apply them in your spreadsheets. Please leave any further queries or recommendations in the comment box below.

<< Go Back to Advanced Filter | Filter in Excel | Learn Excel