How To Set Filter In Excel Sheet

In this tutorial, yous will acquire how to filter data in Excel in different ways: how to create filters for text values, numbers and dates, how to use filter with search, and how to filter by color or by selected cell's value. You will as well acquire how to remove filters, and how to set up Excel AutoFilter non working.

If working with large data sets, it can be a challenge not but to calculate data, but also to observe the relevant information. Luckily, Microsoft Excel makes it easy for you lot to narrow downwards the search with a simple yet powerful Filter tool. To learn more near filtering in Excel, please click on the links below.

• What is filter in Excel?
• How to add together filter in Excel
• How to apply 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 by color
• How to filter in Excel with search
• Filter by selected jail cell's value or format
• Re-apply filter afterwards changing data
• How to copy filtered data
• How to articulate 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 but the data relevant at a given fourth dimension and remove all other data from view. You lot can filter rows in Excel worksheets by value, by format and by criteria. Subsequently applying a filter, you tin copy, edit, chart or print only visible rows without rearranging the unabridged listing.

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 past Color.
• For dates: Sort Oldest to Newest, Sort Newest to Oldest, and Sort by Color.

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

• When yous sort data in Excel, the entire tabular array is rearranged, for instance alphabetically or from the lowest to the highest value. Still, sorting does not hide whatsoever entries, information technology merely puts the data into a new lodge.
• When you filter information in Excel, only the entries you lot really 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 prepare should include a header row with the column names like shown in the screenshot below:

Once the column headings are in step, select whatsoever cell inside your dataset, and use one of the following methods to insert filter.

3 means to add filter in Excel

1. On the Data tab, in the Sort & Filter group, click the Filter button.
   
2. On the Home 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 use, the drib-down arrows volition announced in each of the header cells:

How to use filter in Excel

A driblet-down pointer in the column heading means that filtering is added, just not applied 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-down arrow for the cavalcade you want to filter.
2. Uncheck the Select All box to rapidly deselect all data.
3. Cheque the boxes next to the data you lot want to brandish, and click OK.

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

Done! The filter is practical to cavalcade A, temporarily hiding any regions other than East and N.

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

Filter multiple columns

To use Excel filter to multiple columns, just echo the above steps for as many columns equally you desire.

For instance, nosotros can narrow downwards our results to merely show Apples for the East and Northward regions. When yous use multiple filters in Excel, the filter push 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 lesser, and as soon as the double-headed arrow appears, drag it down or to the right.

Filter blank / not-blank cells

To filter data in Excel skipping blanks or not-blanks, practise ane of the following:

To filter out blanks, i.east. display not-blank cell, click the auto-filter pointer, brand sure the (Select All) box is checked, so clear (Blanks) at the lesser of the list. This will display only those rows that take whatsoever value in a given cavalcade.

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

Notes:

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

How to use filter in Excel

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

Notes:

• Different Excel filter types are mutually exclusive. For instance, you can filter a given column by value or past prison cell color, merely not by both at a time.
• For correct results, do non mix different 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 will exist added for the data that occurs the most. For case, if y'all store numbers in a sure column merely well-nigh of the numbers are formatted equally text, Text Filters will appear for that cavalcade simply non Number Filters.

And at present, let's take a closer wait at each option and run into how you tin 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 tin can leverage a number of advanced options provided past Excel Text Filters such equally:

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

As soon as yous add 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, exercise the post-obit:

1. Click the drop-downward arrow in the column heading, and point to Text Filters.
2. In the drop-down bill of fare, select the desired filter (Does Not Contain… in this instance).
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 detail from the dropdown list.
4. Click OK.

Equally the result, all of the Bananas rows, including Green bananas and Goldfinger bananas, will be hidden.

Filter column with 2 criteria

To filter information in Excel with two text criteria, perform the higher up steps to configure the first criteria, and and so practise the following:

• 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 instance, this is how you lot can filter rows that comprise either Bananas or Lemons:

How to create filter in Excel with wildcard characters

If you don't remember exact search or want to filter rows with similar information, you can create a filter with ane the following wildcard characters:

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

Tip. In many cases, y'all can apply the Contains operator instead of wildcards. For instance, to filter cells containing all sorts of Bananas, you can either select the Equals operator and type *bananas*, or apply the Contains operator and simply type bananas.

How to filter numbers in Excel

Excel'south Number Filters allow you to manipulate numeric data in a variety 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 x or lesser x numbers.
• Filter cells with numbers that are above average or below average.

The following screenshot shows the whole list of number filters available in Excel.

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

1. Click the autofilter arrow in the column header, and point to Number Filters.
2. Choose an appropriate comparison operator from the list, Between… in this example.
3. In the Custom AutoFilter dialog box, enter the lower jump and upper spring values. By default, Excel suggests using "Greater than or equal to" and "Less than or equal to" comparison operators. You can change them to "Greater than" and "Less than' if you lot don't want the boundary values to be included.
4. Click OK.
   

Every bit the result, only orders between $250 and $300 are visible:

How to filter dates in Excel

Excel Appointment Filters provide the greatest variety of choices that allow yous filter records for a sure time catamenia quickly and easily.

By default, Excel AutoFilter groups all dates in a given cavalcade past a hierarchy of years, months, and days. Y'all can aggrandize or collapse 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 instance, if you articulate the box side by side to 2022, all dates within the twelvemonth 2022 will be hidden.

In addition, Date Filters permit you lot to display or hide data for a item mean solar day, week, month, quarter, twelvemonth, before or after a specified engagement, or between two dates. The screenshot beneath demonstrates all available appointment filters:

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

If you select the Equals, Before, Later, 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 start 10 days of April 2022, click Between… and configure the filter in this way:

How to filter by color in Excel

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

Clicking the autofilter drop-down pointer volition display Filter by Color with one or more options, depending on which formatting is applied to a cavalcade:

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

For example, if yous formatted cells in a given column with iii unlike background colors (light-green, red and orange) and you want to display but orange cells, you can get information technology 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 encounter How to filter and sort past 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 data sets enabling you lot to swiftly filter rows containing an exact text, number, or date.

Suppose you want to view the records for all "eastward" regions. Simply click the autofilter dropdown, and start typing the word "east" in the search box. Excel Filter will immediately testify you all items that lucifer the search. To display only those rows, either click OK in the Excel AutoFilter card, or printing the Enter key on your keyboard.

To filter multiple searches, employ a filter according to your first search term as demonstrated above, then blazon the second term, and as presently equally the search results announced, select the Add electric current selection to filter box, and click OK. In this example, nosotros are adding "due west" records to the already filtered "east" items:

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

Filter past selected cell value or format

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

1. Correct click a cell containing the value, color, or icon you desire to filter your data by.
2. In the context menu, point to Filter.
3. Select the desired option: filter by selected prison cell's value, color, font color, or icon.

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

Re-apply a filter later on changing data

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

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

How to copy filtered data 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 press Ctrl + A to select all filtered data including cavalcade headers.

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

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

Note. Ordinarily, when you copy the filtered data elsewhere, filtered-out rows are omitted. In some rare cases, mostly on very large workbooks, Excel may copy hidden rows in add-on to visible rows. To preclude this from happening, select a range of filtered cells, and press Alt + ; to select only visible cells ignoring subconscious rows. If you're non accepted to using keyboard shortcuts, you can utilise the Go To Special feature instead (Home tab > Editing group > Find & Select > Get to Special... > Visible Cells only).

How to clear filter

Afterwards applying a filter to a certain column, you may want to clear it to make all information visible again or filter your information in a different way.

To articulate a filter in a certain column, click the filter button in the column's header, and then click Clear Filter from <Column name>:

How to remove filter in Excel

To remove all filters in a worksheet, exercise ane of the following:

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

Filter non working in Excel

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

Basically, this is how you add, apply and utilise filter in Excel. Just there is much more to it! In the next tutorial, we will explore and capabilities of Advanced Filter and see how to filter data with multiple sets of criteria. Delight stay tuned!

You may also be interested in

How To Set Filter In Excel Sheet,

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

Posted by: bachmanreplivinge.blogspot.com

Share this post