In Excel, there are multiple ways to remove filters from data. But in this tutorial, we not only learn methods to remove the filter, but we will also learn the difference between removing a filter and clearing it.
Keyboard Shortcut to Remove or Clear a Filter in Excel
To remove or deactivate a filter from the data, you can use the same shortcut that you use to apply it:
Ctrl + Shift + L / Alt > D > F > F
Select any of the cells from the data and then use any of the keyboard shortcuts.
Press the Filter Button to Remove the Filter in Excel
You can also use the filter button to remove the filter. When you apply the filter from Data > Filter. The filter button turns ON the filters.
And when you want to turn it OFF, you need to click on filter button again.
- Click on the “Data” tab in the ribbon at the top. This tab is often situated between the “Formulas” and “Review” tabs.
- Now, look for the “Sort & Filter” group within the dropdown menu. This group will generally be found towards the right side of the dropdown.
- Find and click on the “Filter” option which is already activated. This option removes the filter from the selected column or columns.
- And the moment you click on the filter button, it removes the filter from the data. This button works like the keyboard shortcut we used in the first method.
Clear Filters Instead of Removing
You can also clear the filter instead of removing the filter. For this, you need to click on the filter drop-down on the column that you have filtered.
In the above example, you can see that we have a filter for some values in the “Invoice” column and the option to clear the filter from the column. And when you click it, it clears the filter instead of removing it.
Clear Filter from the Home Tab
If you wish to clear a filter from the Home tab here are simple steps for this:
- Open your Excel document: Start by launching Microsoft Excel and opening the document where you’ve applied the filter that you now wish to remove.
- Navigate to the Home tab: Look at the top of your Excel window. You will see a number of tabs, including File, Home, Insert, and others. Click on the Home tab to access its features.
- Click on the Sort & Filter: Once you locate the Sort & Filter command, click on it. This will open a dropdown menu with a variety of options related to sorting and filtering.
- Select the Clear: In the dropdown menu that appears when you click on the Sort & Filter command, locate and click on the Clear option. This will remove the filter that you’ve applied to your Excel data.
Why Should I Remove or Clear the Filters?
- If filters are applied, you see only a subset of your data to view the complete data set. Clearing the filters lets you see all the data in your spreadsheet.
- Before applying a new filter to your data, it can be helpful to clear all existing filters.
- Some operations in Excel are performed on the visible (i.e., unfiltered) data. If you want to operate on all your data, you need to clear any filters first.
- If you’re not aware that a filter is applied, you could draw incorrect conclusions about your data. Clearing all filters ensures you’re seeing the true picture of your data.
- To share or present your data: If you’re sharing or presenting your spreadsheet, you may want to clear all filters to ensure the viewers see all the data.