How to Remove an Advanced Filter in Excel – 5 Methods

The dataset represents a duty roster. Advanced Filters were applied. To know if a dataset is Advanced filtered, look at the row index numbers. If they are blue, it means they are filtered.

how to remove advanced filter in excel


Method 1 – Applying the Clear Feature to Remove the Advanced Filter

Steps:

  • Select B4:H13.
  • Go to the Data tab and click Clear in Sort & Filter.

Apply Clear Feature to Remove Advanced Filter

The filter is removed: the row index numbers are black.

Hidden rows are visible in the dataset.


Method 2 – Use a Keyboard Shortcut to Delete Advance Filters in Excel

Steps:

  • Open the active sheet and press  Alt + D + F + A .
  • In the Advanced Filter dialog box, select the range you want to filter in List range.
  • Uncheck Unique records only.
  • Close the dialog box by clicking OK.

Use Keyboard Shortcut to Delete Advance Filters in Excel

Hidden rows are displayed.

The advanced filters were removed.


Method 3 – Manually Unhide Rows to remove the Advanced Filter from Specific Rows

Steps:

  • Right-click the gap between rows 8 and 10.

Manually Unhide Rows to Turn off Advanced Filter from Specific Rows

  • In the context menu, click Unhide.

Hidden cells are visible.

The Advanced filter was removed.

Manually Unhide Rows to Turn off Advanced Filter from Specific Rows


Method 5 – Using the Filter Tool to remove the Advanced Filter in Excel

Steps:

  • Go to the Data tab and click Filter in Sort & Filter.

Withdraw Advanced Filters in Excel Using Filter Tool

 

  • You can also press  Ctrl + Shift + L  to access the Filter feature.

Hidden cells are visible after removing the advanced filters.

Withdraw Advanced Filters in Excel Using Filter Tool


Method 5 – Running a VBA Code to Remove the Advanced Filter in Excel

Steps:

  • Go to the Developer tab and click Visual Basic.

Run a VBA Code to Remove the Advanced Filter in Excel

  • In the Visual Basic window, click Insert and choose Module to create a module.

  • Enter the following VBA code:
Sub Remove_Adv_Filter()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub
			
  • Click Run.
  • Save the code in an Excel Macro-Enabled Workbook and close the window.

Run a VBA Code to Remove the Advanced Filter in Excel

Hidden cells are visible.

The macro removes the advanced filters.


Download Practice Workbook

Download the practice workbook.


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

Get FREE Advanced Excel Exercises with Solutions!
Yousuf Khan
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo