How to remove the filter in Excel?
A filter applies to an Excel table to display the relevant data and hide other data to analyze something. Basically, it is used for analysis purposes. But when we set the filter on an Excel table, it hides some table’s data for better analysis. Once the work is done, one would like to back the original Excel sheet containing the entire data.
In that case, we have to remove the filter from that Excel table to get all the data back and make it visible in the table. In this chapter, you will learn how one can remove a filter from an Excel table. Before this, we will give you a brief on filters.
Before start learning how to remove a filter from an Excel table, you should know what filter is.
What is a filter?
A filter is used in Excel to display the relevant data and hide the other information from the file. It does not delete or remove the Excel worksheet data; it just hides the data that does not satisfy the filter condition.
A filter can be applied to numeric data as well as string data. It provides distinct conditions for both. You can add multiple filters to an Excel table for the analysis of data. Now, move to remove a filter from an Excel table.
How to find out that filter is applied on a column?
Before start removing the filter, you should know that either the filter is applied on a column or not in an Excel spreadsheet. You can easily find out that a filter is applied to a column or not.
You will find a small funnel icon near the dropdown arrow next to the column name, as shown in the below screenshot.
You have recognized that the filter is applied only on the Product column, not the Price column in the above screenshot. Besides that, a simple down arrow shows that no data is filtered out in this column.
For example – See the Price column containing a simple down arrow icon, which indicates that no data has been filtered in this column.
How to remove all filters from an Excel table?
Now, you will learn how you can remove the filter from an Excel table. You can remove the filter in two different ways, i.e.,
- Remove all filters from an Excel table
- Remove filters from a specific column in an Excel table
Remove all filters from an Excel table
This method will describe you how one can remove filter added on two or more columns at once in an Excel table. Basically, this method removes all the filters applied to all columns of an Excel spreadsheet in one go.
This method fails when you want to remove the filter only from one or two columns instead of all. In that case, use another method to remove the filter of a specific column discussed below.
Here, we have a few simple steps for removing the filters from an Excel table. Follow these steps and learn.
Step 1: We have a table of data on which filter is applied on both Product and Price columns, as you can see in the below screenshot.
Open your Excel spreadsheet containing filtered data in it.
Step 2: In the menu bar of Excel, go to the Data tab.
Step 3: Click on the Clear button present inside the Sort & Filter section and near the Filter option.
Step 4: You will see that filter has been removed from both columns at once. It has removed all the filters applies to this table, and complete data is displaying now.
Note: This method fails when you want to remove the filter only from a specific column instead of all. Follow the complete tutorial below and use another method to remove the filter of a specific column discussed below.
Remove filter from a specific column in an Excel table
Sometimes, we do not require to remove all the filters from an Excel table. We only need to remove the filter of a specific column. The above method does not work in that scenario because it removes all the filters applied to an Excel table.
So, we come with another method that is helpful to remove a filter from a specific column. Using this method, one can easily remove the filter of a specific column in an Excel table. However, you can also use this filter to remove all filters. But it takes time because it does not remove all filters in one go.
Here, we have a few simple steps for removing the filter from an Excel table. We will use the same table data as used in the above example to remove the filter from a particular column. For this, follow the below steps and learn.
Step 1: Open or create an Excel sheet having filters on columns. See the table header that each column will have a down arrow button, which shows that filter is applied on this column.
Step 2: Go to that particular column from which you would like to remove the filter. When you take the mouse over the column heading, you can see the filter applied on that column.
Step 3: Now, click on the down arrow button just next to the column heading to remove the filter and select the Clear filter from Price option.
Step 4: Filter is now cleared from the Price table, and the data which was hidden because of filter added on Price column is now visible.
Similarly, you can remove the filter of other columns one by one as needed.