How To Remove Auto Filters In Excel

In this tutorial, you volition learn how to filter data in Excel in different ways: how to create filters for text values, numbers and dates, how to employ filter with search, and how to filter past color or by selected prison cell's value. Y'all will also learn how to remove filters, and how to fix Excel AutoFilter not working.

If working with large data sets, it can be a claiming not merely to calculate data, merely as well to notice the relevant information. Luckily, Microsoft Excel makes it piece of cake for you to narrow down the search with a simple yet powerful Filter tool. To learn more well-nigh filtering in Excel, please click on the links beneath.

• What is filter in Excel?
• How to add filter in Excel
• How to employ filter to one or multiple columns
• Filter out blanks / not-blanks
• How to use filter in Excel
  • Filter text values
  • Filter numbers
  • Filter dates
• Excel filter past color
• How to filter in Excel with search
• Filter by selected cell's value or format
• Re-apply filter after changing data
• How to copy filtered data
• How to clear filter
• How to remove filter in Excel
• Excel filter non working

What is filter in Excel?

Excel Filter, aka AutoFilter, is a quick way to brandish but the information relevant at a given time and remove all other data from view. You can filter rows in Excel worksheets by value, by format and by criteria. Later applying a filter, you can copy, edit, chart or print merely visible rows without rearranging the unabridged list.

Excel Filter vs. Excel Sort

Autonomously from numerous filtering options, Excel AutoFilter provides the Sort options relevant to a given column:

• For text values: Sort A to Z, Sort Z to A, and Sort by Color.
• For numbers: Sort Smallest to Largest, Sort Largest to Smallest, and Sort by Color.
• For dates: Sort Oldest to Newest, Sort Newest to Oldest, and Sort by Colour.

The difference between sorting and filtering in Excel is as follows:

• When you sort data in Excel, the entire table is rearranged, for case alphabetically or from the lowest to the highest value. However, sorting does not hide any entries, it only puts the information into a new order.
• When you filter data in Excel, just the entries you actually want to meet are displayed, and all irrelevant items are temporarily removed from view.

How to add filter in Excel

For Excel AutoFilter to piece of work correctly, your data ready should include a header row with the column names similar shown in the screenshot below:

Once the column headings are in pace, select any cell within your dataset, and use one of the following methods to insert filter.

three ways to add filter in Excel

1. On the Data tab, in the Sort & Filter group, click the Filter push button.
   
2. On the Dwelling tab, in the Editing group, click Sort & Filter > Filter.
   
3. Use the Excel Filter shortcut to turn the filters on/off: Ctrl+Shift+L

Whatever method you lot employ, the driblet-down arrows will announced in each of the header cells:

How to apply filter in Excel

A drib-downwardly arrow in the cavalcade heading means that filtering is added, but not practical yet. When you hover over the arrow, a screen tip displays (Showing All).

To filter data in Excel, do the post-obit:

1. Click the drop-downward arrow for the cavalcade you lot want to filter.
2. Uncheck the Select All box to quickly deselect all data.
3. Check the boxes next to the data yous want to display, and click OK.

For example, this is how we can filter data in the Region column to view sales only for Eastward and North:

Washed! The filter is applied to column A, temporarily hiding any regions other than E and North.

The drop-down arrow in the filtered column changes to the Filter push button , and hovering over that button displays a screen tip indicating which filters are applied:

Filter multiple columns

To apply Excel filter to multiple columns, just echo the above steps for every bit many columns every bit you want.

For instance, we tin can narrow down our results to only show Apples for the Due east and Due north regions. When you apply multiple filters in Excel, the filter push button appears in each of the filtered columns:

Tip. To brand the Excel Filter window wider and/or longer, hover over the grip handle at the bottom, and every bit presently as the double-headed arrow appears, drag it down or to the correct.

Filter bare / non-blank cells

To filter data in Excel skipping blanks or non-blanks, do one of the following:

To filter out blanks, i.e. display non-bare cell, click the auto-filter arrow, make certain the (Select All) box is checked, and then clear (Blanks) at the bottom of the list. This will brandish only those rows that have whatever value in a given column.

To filter out non-blanks, i.e. display only empty cells, clear (Select All), and and then select (Blanks). This will display only the rows with an empty cell in a given column.

Notes:

• The (Blanks) option is available merely for columns that contain at least ane empty cell.
• If y'all want to delete blank rows based on some cardinal column, you tin filter out non-blanks in that column, select the filtered rows, right-click the selection, and click Delete row. If you want to delete only those rows that are completely bare and leave the rows with some content and some empty cells, check out this solution.

How to use filter in Excel

Apart from basic filtering options discussed to a higher place, AutoFilter in Excel provides a number of avant-garde tools that can help y'all filter specific data types such as text, numbers and dates exactly the fashion you want.

Notes:

• Different Excel filter types are mutually sectional. For example, y'all can filter a given cavalcade past value or past jail cell colour, but non by both at a time.
• For correct results, do not mix different value types in a single cavalcade because only i filter blazon is available for each column. If a column contains several types of values, the filter will exist added for the data that occurs the virtually. For example, if you store numbers in a certain column merely nigh of the numbers are formatted as text, Text Filters will appear for that column but not Number Filters.

And now, let's have a closer look at each option and see how you lot can create a filter most suited for your data type.

Filter text information

When yous want to filter a text column for something very specific, you tin leverage a number of avant-garde options provided by Excel Text Filters such as:

• Filter cells that begin with or end with a specific grapheme(southward).
• Filter cells that incorporate or do not contain a given character or give-and-take anywhere in the text.
• Filter cells that are exactly equal or non equal to a specified grapheme(s).

Equally soon equally you add together a filter to a cavalcade containing text values, Text Filters will appear automatically in the AutoFilter menu:

For example, to filter out rows containing Bananas, practice the following:

1. Click the drop-downwardly arrow in the column heading, and point to Text Filters.
2. In the drop-down card, select the desired filter (Does Non Contain… in this example).
3. The Custom AutoFilter dialog box volition show upwardly. In the box to the right of the filter, blazon the text or select the desired particular from the dropdown list.
4. Click OK.

As the outcome, all of the Bananas rows, including Green bananas and Goldfinger bananas, volition be hidden.

Filter column with two criteria

To filter information in Excel with two text criteria, perform the above steps to configure the kickoff criteria, and then practise the post-obit:

• Bank check And or Or radio button depending on whether both or either criterion should be true.
• Select the comparison operator for the second benchmark, and enter a text value in the box right to information technology.

For instance, this is how yous can filter rows that comprise either Bananas or Lemons:

How to create filter in Excel with wildcard characters

If you don't recall verbal search or desire to filter rows with similar information, you can create a filter with one the following wildcard characters:

Wildcard grapheme	Clarification	Example
? (question marker)	Matches whatever single graphic symbol	Gr?y finds "grey" and "gray"
* (asterisk)	Matches any sequence of characters	Mid* finds "Mideast" and "Midwest"
~ (tilde) followed by *, ?, or ~	Allows filtering cells that comprise a real question mark, asterisk, or tilde.	What~? finds "what?"

Tip. In many cases, you lot can utilize the Contains operator instead of wildcards. For example, to filter cells containing all sorts of Bananas, yous can either select the Equals operator and blazon *bananas*, or use the Contains operator and simply type bananas.

How to filter numbers in Excel

Excel'southward Number Filters let you to manipulate numeric data in a diverseness of ways, including:

• Filter numbers equal or not equal to a certain number.
• Filter numbers, greater than, less than or between the specified numbers.
• Filter top 10 or bottom x numbers.
• Filter cells with numbers that are above average or below average.

The post-obit screenshot shows the whole list of number filters bachelor in Excel.

For example, to create a filter that displays just orders betwixt $250 and $300, proceed with these steps:

1. Click the autofilter arrow in the column header, and point to Number Filters.
2. Cull an appropriate comparing operator from the list, Betwixt… in this case.
3. In the Custom AutoFilter dialog box, enter the lower bound and upper leap values. Past default, Excel suggests using "Greater than or equal to" and "Less than or equal to" comparison operators. Yous can change them to "Greater than" and "Less than' if you don't want the purlieus values to be included.
4. Click OK.
   

As the upshot, only orders between $250 and $300 are visible:

How to filter dates in Excel

Excel Date Filters provide the greatest variety of choices that let yous filter records for a certain time period quickly and hands.

Past default, Excel AutoFilter groups all dates in a given cavalcade by a hierarchy of years, months, and days. You tin can aggrandize or plummet different levels past clicking the plus or minus signs next to a given group. Selecting or immigration a higher level group selects or clears data in all nested levels. For instance, if y'all clear the box next to 2022, all dates within the year 2022 will be hidden.

In addition, Date Filters allow yous to display or hibernate data for a particular twenty-four hour period, calendar week, month, quarter, twelvemonth, before or after a specified engagement, or betwixt two dates. The screenshot beneath demonstrates all available date filters:

In most cases, Excel filter by date works in a unmarried click. For instance, to filter rows containing records for the current week, you only betoken to Appointment Filters and click This Week.

If you select the Equals, Before, Subsequently, Between operator or Custom Filter, the already familiar Custom AutoFilter dialog window will show up, where you specify the desired criteria.

For example, to display all items for the first 10 days of April 2022, click Between… and configure the filter in this way:

How to filter by color in Excel

If the information in your worksheet is formatted manually or through conditional formatting, you can too filter that information past color.

Clicking the autofilter drop-downwards arrow will display Filter past Color with i or more than options, depending on which formatting is practical to a column:

• Filter past cell color
• Filter by font color
• Filter by cell icon

For example, if you formatted cells in a given column with 3 dissimilar background colors (green, reddish and orange) and you desire to display just orange cells, yous can get it done in this manner:

1. Click the filter arrow in the header jail cell, and signal to Filter past Color.
2. Click the desired color - orange in this example.
   

Voila! Only values formatted with the orange font colour are visible and all other rows are temporarily subconscious:

For more information, delight see How to filter and sort by cell color in Excel.

How to filter in Excel with search

Beginning with Excel 2010, the Filter interface includes a search box that facilitates navigation in large information sets enabling you to swiftly filter rows containing an exact text, number, or engagement.

Suppose yous desire to view the records for all "east" regions. Just click the autofilter dropdown, and first typing the word "east" in the search box. Excel Filter will immediately testify you all items that match the search. To display but those rows, either click OK in the Excel AutoFilter menu, or printing the Enter key on your keyboard.

To filter multiple searches, employ a filter co-ordinate to your first search term as demonstrated above, so type the second term, and as presently as the search results appear, select the Add current selection to filter box, and click OK. In this example, nosotros are adding "west" records to the already filtered "east" items:

That was pretty fast, wasn't it? Only three mouse clicks!

Filter by selected cell value or format

One more than style to filter data in Excel is to create a filter with the criteria equal to the contents or formats of the selected cell. Hither's how:

1. Right click a cell containing the value, color, or icon you desire to filter your information by.
2. In the context carte du jour, point to Filter.
3. Select the desired selection: filter past selected cell's value, colour, font colour, or icon.

In this instance, we are filtering data by the selected cell's icon:

Re-apply a filter afterwards changing information

When yous edit or delete data in filtered cells, Excel AutoFilter does not update automatically to reflect the changes. To re-apply the filter, click whatsoever jail cell within your dataset, and so either:

1. Click Reapply on the Information tab, in the Sort & Filter grouping.
   
2. Click Sort & Filter > Reapply on the Home tab, in the Editing group.
   

How to copy filtered information in Excel

The fastest way to copy a filtered information range to another worksheet or workbook is past using the following 3 shortcuts.

1. Select whatsoever filtered cell, and then press Ctrl + A to select all filtered information including column headers.

   To select filtered data excluding column headers, select the first (upper-left) jail cell with data, and press Ctrl + Shift + End to extend the selection to the terminal cell.

2. Printing Ctrl + C to copy the selected data.
3. Switch to another sheet/workbook, select the upper-left jail cell of the destination range, and printing Ctrl+V to paste the filtered data.

Notation. Usually, when you lot copy the filtered data elsewhere, filtered-out rows are omitted. In some rare cases, mostly on very large workbooks, Excel may re-create hidden rows in addition to visible rows. To prevent this from happening, select a range of filtered cells, and press Alt + ; to select only visible cells ignoring hidden rows. If y'all're not accustomed to using keyboard shortcuts, yous tin can apply the Go To Special feature instead (Home tab > Editing group > Discover & Select > Go to Special... > Visible Cells merely).

How to clear filter

After applying a filter to a certain column, you may want to clear information technology to make all information visible once more or filter your data in a different way.

To clear a filter in a certain column, click the filter push in the cavalcade'south header, and and so click Clear Filter from <Column name>:

How to remove filter in Excel

To remove all filters in a worksheet, do i of the following:

• Become to the Data tab > Sort & Filter grouping, and click Clear.
• Go to the Abode tab > Editing group, and click Sort & Filter > Clear.

Filter not working in Excel

If Excel's AutoFilter stopped working partway down a worksheet, most likely it'southward considering some new data has been entered outside the range of filtered cells. To prepare this, simply re-use filter. If that does not aid and your Excel filters are even so not working, clear all filters in a spreadsheet, and and so apply them anew. If your dataset contains whatsoever blank rows, manually select the entire range using the mouse, and then apply autofilter. As presently every bit you exercise this, the new data will be added to the range of filtered cells.

Basically, this is how y'all add, apply and use filter in Excel. Only there is much more to information technology! In the next tutorial, we will explore and capabilities of Advanced Filter and come across how to filter information with multiple sets of criteria. Please stay tuned!

You may also be interested in

How To Remove Auto Filters In Excel,

Source: https://www.ablebits.com/office-addins-blog/2016/08/31/excel-filter-add-use-remove/

Posted by: poseyultay1999.blogspot.com

Share this post