When working with large data sets, it can be confusing finding relevant information from large sets of data. With the Excel filters, you can narrow down the search and find what you are searching for.
The AutoFilter feature helps you to filter data in Excel and there are different ways to go about it. In this article, you will learn how to remove AutoFilter in Excel.
In This Article
What is AutoFilter in Excel?
The Excel AutoFilter quickly displays only the information relevant to you at a given time and eliminates other irrelevant data from view. The different ways to filter rows in Excel worksheets is either by value, by format or by criteria. You can copy, edit, chart or print only visible rows without rearranging the entire list.
How AutoFilter in Excel works
When your data is filtered in Excel, only the information you want to see will be displayed, and all irrelevant items temporarily removed from view.
For Excel AutoFilter to work correctly, the data set should include a header row with the column names. Once the column headings are in place, select any cell within your dataset, and use any of the methods discussed in this article to insert a filter.
What is the difference between AutoFilter and Advanced Filter in Excel?
Excel’s Advanced Filter is different from AutoFilters in some important ways. The Excel AutoFilter is a built-in capability that is applied by hitting the Filter button on the ribbon. But the Advanced Filter cannot be applied automatically as it has no predefined setup. One has to configure the list range and criteria range manually before applying.
One thing you have to know is that AutoFilter allows filtering of data with 2 criteria; those conditions are specified directly in the Custom AutoFilter dialog box.
But to use Advanced Filter, you can find rows that meet multiple criteria in multiple columns, and the advanced criteria need to be entered in a separate range on the worksheet.
You can create an advanced filter for your sheet through the following steps:
- Organize the source data.
- Set up the criteria range in a separate range on the worksheet.
- Then apply Excel Advanced Filter.
- Configure the Advanced Filter parameters.
- Click OK to get results
Where is the AutoFilter Button in Excel?
To find the AutoFilter button:
- First, select the data you want to filter.
- Click Data and select Filter under the “Sort & Filter” section.
You can as well use the shortcut: Ctrl + Shift + L.
Applying AutoFilter in Excel
Before we consider the steps involved in removing AutoFilter in Excel, let us learn ho to add filters in the first place.
How to Add Filter in Excel:
- Open the worksheet you want to filter data on.
- On the Data tab, in the Sort & Filter group, click the Filter button.
- In the Editing group, navigate to HOME and click Sort & Filter.
- Select Filter.
- You can choose “Number Filters” if you are working with numbers or “Text Filters” if you are working with text values.
Another way to insert filter in Excel is by using the shortcut; Ctrl + Shift+ L which turns the filter on or off.
Once the filter is activated, a drop-down arrow will appear in each of the header cells. The drop-down arrow in the column heading means that filtering is added, but not applied yet. Go ahead to apply the filters by:
- Hovering over the arrow for a screen tip to display Showing All.
- Uncheck the Select All box to deselect all data.
- Check the boxes next to the data you want to display, and click OK.
Lets say you want to view data in column A only, apply the filter to column A, this will temporarily hide any regions other than column A. The Filter button in the column header indicates that the filter is applied to the column.
NB: To quickly find the number of filtered or visible rows, refer to the Excel status bar.
How to Filter Multiple Columns in Excel
You can apply Excel filter to multiple columns, repeat the steps for as many columns as you want to filter.
How to Filter Blank and Non-blank Cells in Excel
To filter data in Excel skipping blanks or non-blanks, follow these steps:
- Click the auto-filter arrow.
- Make sure the ‘Select All’ box is checked.
- Clear ‘Blanks’ at the bottom of the list. This will display only those rows that have any value in a given column.
How to Filter Out Non-blanks in Excel
Select All, and then select Blanks. Only the rows with an empty cell in a given column will be displayed.
How to Use AutoFilter in Excel
AutoFilter in Excel helps users with a number of advanced tools that can help you filter specific data types such as text, numbers and dates exactly the way you want. You can even filter by search, color, etc.
How to Filter Data by Text in Excel
There are a number of filter options in the Excel Text Filters such as:
- You can filter cells that begin with or end with specific characters.
- You can also filter cells that contain or do not contain a given character or word anywhere in the text.
- Also, filter cells that are exactly equal or not equal to specified character(s).
Once you add a filter to a column containing text values, Text Filters will appear automatically in the AutoFilter menu:
- Just click the drop-down arrow in the column heading, and point to Text Filters.
- Select the desired filter in the drop down menu.
- The Custom AutoFilter dialog box will pop up. And in the box to the right of the filter, select the desired item from the dropdown list.
- Click OK.
How to Filter Numbers in Excel
You can use the Excel’s Number Filters to manipulate numeric data in a number of ways, including:
- You can filter numbers equal or not equal to a certain number.
- You can filter numbers greater than, less than or between the specified numbers.
- You can filter top 10 or bottom 10 numbers.
- You can filter cells that has numbers that are above average or below average.
- Just click the auto filter arrow in the column header, and point to Number Filters.
- Choose the appropriate comparison operator from the list.
- A custom AutoFilter dialog box will appear, where you can enter the lower bound and upper bound values.
- Click OK.
How to Re-apply a Filter After Changing Data
If you edit or delete data in filtered cells, Excel AutoFilter does not update automatically to reflect the changes. You have to re-apply the filter by clicking any cell within your dataset, Or:
- Select Reapply on the Data tab, in the Sort & Filter group.
- Click Sort & Filter.
- Select reapply on the Home tab, in the Editing group.
How to Clear Filter in Excel
You may want to clear a filter and make all information visible again or filter your data in a different way.
- Just click the filter button in the column’s header.
- Then click Clear Filter from the column.
How to Remove AutoFilter in Excel
Follow these steps to remove auto filters in Excel sheet:
- Go to the Data tab in the Sort & Filter group.
- Click Clear.
- Then go to the Home tab > Editing group, and click Sort & Filter, then Clear.
Filter Not Working in Excel
If Excel’s AutoFilter is not working in your worksheet, it ,might for some technical reasons. You can reload your sheet, or re-apply filter.
Alternative Methods to Filter Data
There might be times when you cannot use the FILTER function and you’re looking for an alternative method that offers similar results.
You can use a combination of the INDEX, AGGREGATE, and ROW(S) functions. This formula thus:
=INDEX($C$8:$C$13,AGGREGATE(10,6,ROW($C$8:$C$10)-ROW($C$8)+1/($C$8:$E$13=$F$7),ROWS(H$10:H10))).This formula provides the same output as the FILTER function in the attached sample dataset.
That’s it on how to remove auto filter in an Excel sheet and make your work organized, simply follow the steps outlined in this article.
Before you go…
FAQs
What is the shortcut to clear filters in Excel Mac?
You can quickly clear filters in Excel Mac if you select a cell in the table and press Command + Shift + L.
How can I remove all active filters in Excel?
Remove all active filters in a worksheet; go to the Data tab and click the Filter button, or use the keyboard shortcut Alt+ D+ F+ F.
What is the error ‘This will change a filtered range on your worksheet’ in Excel?
This error message indicates that an Excel table cannot be created when AutoFilter is enabled and comes up when you try to apply Auto Filter.
