Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


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.

how to remove advanced filter in excel


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.

Apply Clear Feature to Remove Advanced Filter

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

Use Keyboard Shortcut to Delete Advance Filters in Excel

  • 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 begin with, right-click on the gap between rows 8 & 10.

Manually Unhide Rows to Turn off Advanced Filter from Specific Rows

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

Manually Unhide Rows to Turn off Advanced Filter from Specific Rows

Read More: Advanced Filter with Criteria Range in Excel (18 Applications)


Similar Readings


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.

Withdraw Advanced Filters in Excel Using Filter Tool

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

Withdraw Advanced Filters in Excel Using Filter Tool

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.

Run a VBA Code to Remove the Advanced Filter in Excel

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

Run a VBA Code to Remove the Advanced Filter in Excel

  • 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

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo