How To Enable Filtering In Excel

In this tutorial, you will larn how to filter information in Excel in different means: how to create filters for text values, numbers and dates, how to employ filter with search, and how to filter by colour or past selected prison cell's value. Yous will also acquire how to remove filters, and how to set up Excel AutoFilter non working.

If working with large data sets, information technology tin be a challenge non but to calculate information, simply also to observe the relevant information. Luckily, Microsoft Excel makes it like shooting fish in a barrel for you to narrow down the search with a simple yet powerful Filter tool. To learn more almost filtering in Excel, please click on the links beneath.

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

What is filter in Excel?

Excel Filter, aka AutoFilter, is a quick way to display just the information relevant at a given fourth dimension and remove all other data from view. Y'all can filter rows in Excel worksheets by value, by format and by criteria. After applying a filter, you can re-create, edit, chart or print only visible rows without rearranging the entire list.

Excel Filter vs. Excel Sort

Apart 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 Color.

The difference between sorting and filtering in Excel is every bit follows:

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

How to add filter in Excel

For Excel AutoFilter to work correctly, your data fix should include a header row with the column names like shown in the screenshot below:

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

iii means to add together filter in Excel

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

Whatever method you apply, the driblet-down arrows volition announced in each of the header cells:

How to utilize filter in Excel

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

To filter information in Excel, do the following:

1. Click the drib-downward pointer for the column you want to filter.
2. Uncheck the Select All box to quickly deselect all information.
3. Check the boxes adjacent to the data you want to display, and click OK.

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

Done! The filter is applied to column A, temporarily hiding any regions other than Eastward and N.

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

Filter multiple columns

To utilize Excel filter to multiple columns, merely echo the above steps for every bit many columns as you desire.

For example, we can narrow down our results to only prove Apples for the East and North regions. When you utilize multiple filters in Excel, the filter button appears in each of the filtered columns:

Tip. To make the Excel Filter window wider and/or longer, hover over the grip handle at the bottom, and as before long as the double-headed arrow appears, drag it down or to the right.

Filter blank / non-bare cells

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

To filter out blanks, i.due east. display not-blank cell, click the auto-filter arrow, make sure the (Select All) box is checked, and then articulate (Blanks) at the bottom of the list. This will display merely those rows that have any value in a given column.

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

Notes:

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

How to apply filter in Excel

Apart from basic filtering options discussed above, AutoFilter in Excel provides a number of advanced tools that can aid you filter specific data types such as text, numbers and dates exactly the way you want.

Notes:

• Different Excel filter types are mutually sectional. For example, you tin can filter a given column by value or by cell color, merely not by both at a time.
• For right results, do non mix unlike value types in a single column because only one filter type is available for each cavalcade. If a column contains several types of values, the filter volition exist added for the data that occurs the most. For example, if you store numbers in a sure column only well-nigh of the numbers are formatted as text, Text Filters volition appear for that cavalcade simply non Number Filters.

And now, allow'southward have a closer look at each option and see how yous can create a filter most suited for your data type.

Filter text data

When you want to filter a text column for something very specific, you lot can 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(s).
• Filter cells that contain or do non incorporate a given graphic symbol or word anywhere in the text.
• Filter cells that are exactly equal or not equal to a specified character(s).

As soon as you add a filter to a column containing text values, Text Filters will appear automatically in the AutoFilter menu:

For instance, to filter out rows containing Bananas, exercise the following:

1. Click the drib-downwards pointer in the cavalcade heading, and point to Text Filters.
2. In the driblet-down carte du jour, select the desired filter (Does Not Incorporate… in this example).
3. The Custom AutoFilter dialog box will show up. In the box to the right of the filter, type the text or select the desired item from the dropdown list.
4. Click OK.

As the effect, all of the Bananas rows, including Green bananas and Goldfinger bananas, will be subconscious.

Filter column with 2 criteria

To filter data in Excel with ii text criteria, perform the above steps to configure the first criteria, and then do 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 criterion, and enter a text value in the box right to it.

For example, this is how you can filter rows that contain either Bananas or Lemons:

How to create filter in Excel with wildcard characters

If you don't call up exact search or want to filter rows with similar information, yous tin create a filter with one the following wildcard characters:

Wildcard character	Clarification	Example
? (question mark)	Matches any unmarried character	Gr?y finds "grey" and "grayness"
* (asterisk)	Matches whatever sequence of characters	Mid* finds "Mideast" and "Midwest"
~ (tilde) followed past *, ?, or ~	Allows filtering cells that contain a real question marking, asterisk, or tilde.	What~? finds "what?"

Tip. In many cases, you can employ the Contains operator instead of wildcards. For case, to filter cells containing all sorts of Bananas, you can either select the Equals operator and type *bananas*, or use the Contains operator and merely type bananas.

How to filter numbers in Excel

Excel's Number Filters allow you to manipulate numeric data in a variety of ways, including:

• Filter numbers equal or non equal to a sure number.
• Filter numbers, greater than, less than or betwixt the specified numbers.
• Filter top 10 or bottom 10 numbers.
• Filter cells with numbers that are to a higher place average or below average.

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

For instance, to create a filter that displays only orders between $250 and $300, proceed with these steps:

1. Click the autofilter arrow in the cavalcade header, and betoken to Number Filters.
2. Cull an appropriate comparing operator from the listing, Betwixt… in this instance.
3. In the Custom AutoFilter dialog box, enter the lower bound and upper spring values. By default, Excel suggests using "Greater than or equal to" and "Less than or equal to" comparing operators. You can change them to "Greater than" and "Less than' if you don't want the purlieus values to exist included.
4. Click OK.
   

As the result, only orders betwixt $250 and $300 are visible:

How to filter dates in Excel

Excel Date Filters provide the greatest multifariousness of choices that permit y'all filter records for a certain time catamenia quickly and easily.

By default, Excel AutoFilter groups all dates in a given cavalcade by a hierarchy of years, months, and days. You can expand or plummet different levels by clicking the plus or minus signs next to a given grouping. Selecting or clearing a higher level group selects or clears data in all nested levels. For example, if you articulate the box next to 2016, all dates inside the year 2016 volition exist subconscious.

In addition, Date Filters permit you to brandish or hide data for a particular mean solar day, week, month, quarter, year, earlier or after a specified appointment, or between two dates. The screenshot beneath demonstrates all available engagement filters:

In about cases, Excel filter by date works in a single click. For example, to filter rows containing records for the current week, you simply point to Date Filters and click This Week.

If you lot select the Equals, Before, After, Betwixt operator or Custom Filter, the already familiar Custom AutoFilter dialog window will show upward, where yous specify the desired criteria.

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

How to filter by colour in Excel

If the data in your worksheet is formatted manually or through conditional formatting, you tin also filter that data by color.

Clicking the autofilter driblet-downward arrow volition display Filter by Color with 1 or more than options, depending on which formatting is applied to a cavalcade:

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

For example, if you formatted cells in a given cavalcade with iii different background colors (green, ruby-red and orange) and you desire to display simply orangish cells, you tin get it washed in this way:

1. Click the filter arrow in the header prison cell, and point to Filter by Color.
2. Click the desired colour - orange in this example.
   

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

For more information, please come across How to filter and sort by prison cell colour in Excel.

How to filter in Excel with search

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

Suppose yous want to view the records for all "east" regions. Just click the autofilter dropdown, and start typing the word "east" in the search box. Excel Filter will immediately prove yous all items that match the search. To display only 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 according to your outset search term as demonstrated above, and so blazon the second term, and equally soon equally the search results appear, select the Add together current selection to filter box, and click OK. In this example, nosotros are adding "w" records to the already filtered "due east" items:

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

Filter by selected cell value or format

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

1. Right click a cell containing the value, color, or icon you desire to filter your data by.
2. In the context bill of fare, point to Filter.
3. Select the desired choice: filter by selected cell'south value, color, font color, or icon.

In this case, we are filtering information by the selected prison cell's icon:

Re-use a filter after changing data

When y'all edit or delete data in filtered cells, Excel AutoFilter does non update automatically to reflect the changes. To re-use the filter, click any cell within your dataset, and then either:

1. Click Reapply on the Data tab, in the Sort & Filter group.
   
2. Click Sort & Filter > Reapply on the Domicile tab, in the Editing group.
   

How to copy filtered information in Excel

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

1. Select any filtered cell, and then printing Ctrl + A to select all filtered data including column headers.

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

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

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

How to clear filter

After applying a filter to a certain column, you may want to articulate information technology to brand all information visible again or filter your data in a dissimilar style.

To clear a filter in a sure column, click the filter button in the cavalcade's header, and then click Clear Filter from <Column proper name>:

How to remove filter in Excel

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

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

Filter not working in Excel

If Excel'south AutoFilter stopped working partway downward a worksheet, most probable it'southward because some new data has been entered exterior the range of filtered cells. To set up this, simply re-apply filter. If that does not assist and your Excel filters are still not working, articulate all filters in a spreadsheet, and and then utilize them anew. If your dataset contains any blank rows, manually select the entire range using the mouse, so utilize autofilter. As soon as you practise this, the new data will be added to the range of filtered cells.

Basically, this is how you add, employ and use filter in Excel. Simply there is much more to it! In the next tutorial, nosotros volition explore and capabilities of Advanced Filter and see how to filter information with multiple sets of criteria. Please stay tuned!

You lot may also be interested in

How To Enable Filtering In Excel,

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

Posted by: owenswhearour.blogspot.com

Share this post