How To Use Filters In Excel

In this tutorial, y'all will learn how to filter information 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 past color or by selected cell'south value. You will besides learn how to remove filters, and how to ready Excel AutoFilter not working.

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

• What is filter in Excel?
• How to add filter in Excel
• How to use filter to one or multiple columns
• Filter out blanks / non-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 past selected prison cell's value or format
• Re-apply filter after changing information
• How to re-create 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 data 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, yous can copy, edit, nautical 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 Colour.
• 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 as follows:

• When yous sort data in Excel, the unabridged table is rearranged, for case alphabetically or from the everyman to the highest value. Yet, sorting does not hide any entries, it only puts the information into a new social club.
• When you filter data in Excel, only the entries you really desire to run into 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 set should include a header row with the column names like shown in the screenshot below:

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

3 ways to add filter in Excel

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

Any method you utilize, the drop-down arrows will appear in each of the header cells:

How to apply filter in Excel

A drop-down pointer in the column heading means that filtering is added, but not practical yet. When y'all hover over the arrow, a screen tip displays (Showing All).

To filter data in Excel, do the following:

1. Click the drop-downwardly pointer for the column you want to filter.
2. Uncheck the Select All box to quickly deselect all data.
3. Cheque the boxes next to the data you want to display, and click OK.

For example, this is how nosotros can filter data in the Region cavalcade to view sales only for East and North:

Done! The filter is applied to column A, temporarily hiding any regions other than East and Due north.

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

Filter multiple columns

To apply Excel filter to multiple columns, only repeat the above steps for as many columns every bit you want.

For case, we tin narrow down our results to only prove Apples for the East and N regions. When you apply 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 soon as the double-headed arrow appears, drag it down or to the right.

Filter blank / non-blank cells

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

To filter out blanks, i.east. brandish non-blank cell, click the auto-filter arrow, make sure the (Select All) box is checked, and so clear (Blanks) at the bottom of the listing. This will display merely those rows that accept whatsoever value in a given column.

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

Notes:

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

How to use filter in Excel

Apart from basic filtering options discussed higher up, AutoFilter in Excel provides a number of advanced tools that can help you filter specific information types such equally text, numbers and dates exactly the fashion you want.

Notes:

• Different Excel filter types are mutually exclusive. For instance, you can filter a given column by value or past cell color, but not past both at a time.
• For right results, practice non mix unlike value types in a single cavalcade considering only one filter type is available for each column. If a column contains several types of values, the filter volition be added for the information that occurs the most. For example, if you store numbers in a certain column but most of the numbers are formatted as text, Text Filters will appear for that column just not Number Filters.

And now, let'due south take a closer look at each option and see how you can create a filter most suited for your information type.

Filter text data

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

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

Every bit soon as you add together a filter to a cavalcade containing text values, Text Filters volition announced automatically in the AutoFilter card:

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

1. Click the drop-down arrow in the column heading, and indicate to Text Filters.
2. In the drib-down menu, select the desired filter (Does Non Contain… in this example).
3. The Custom AutoFilter dialog box will evidence 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 event, all of the Bananas rows, including Green bananas and Goldfinger bananas, will exist hidden.

Filter column with 2 criteria

To filter data in Excel with two text criteria, perform the in a higher place steps to configure the first criteria, and so do the following:

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

For instance, this is how you tin can filter rows that incorporate either Bananas or Lemons:

How to create filter in Excel with wildcard characters

If yous don't call back exact search or want to filter rows with similar information, y'all tin can create a filter with i the post-obit wildcard characters:

Wildcard character	Description	Case
? (question mark)	Matches whatever single character	Gr?y finds "grey" and "greyness"
* (asterisk)	Matches whatever sequence of characters	Mid* finds "Mideast" and "Midwest"
~ (tilde) followed by *, ?, or ~	Allows filtering cells that contain a real question mark, asterisk, or tilde.	What~? finds "what?"

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

How to filter numbers in Excel

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

• Filter numbers equal or non equal to a certain number.
• Filter numbers, greater than, less than or between the specified numbers.
• Filter top 10 or bottom 10 numbers.
• Filter cells with numbers that are above boilerplate or beneath boilerplate.

The post-obit 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 indicate 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 bound and upper leap values. By 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.
   

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

How to filter dates in Excel

Excel Date Filters provide the greatest diverseness of choices that let y'all filter records for a certain time menses quickly and easily.

Past default, Excel AutoFilter groups all dates in a given column past a bureaucracy of years, months, and days. You can aggrandize or collapse different levels by clicking the plus or minus signs next to a given group. Selecting or clearing a higher level group selects or clears data in all nested levels. For instance, if you clear the box next to 2016, all dates within the twelvemonth 2016 will be hidden.

In addition, Date Filters permit yous to brandish or hide information for a item day, week, month, quarter, year, before or after a specified date, or between two dates. The screenshot beneath demonstrates all available date filters:

In nigh cases, Excel filter by date works in a single click. For case, to filter rows containing records for the current week, you simply betoken to Engagement Filters and click This Calendar week.

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

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

How to filter past colour in Excel

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

Clicking the autofilter drop-downwards arrow will display Filter by Colour with i or more options, depending on which formatting is applied to a column:

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

For example, if you formatted cells in a given column with iii dissimilar background colors (green, red and orange) and you lot want to brandish but orange cells, you lot can become it washed in this way:

1. Click the filter arrow in the header cell, and bespeak to Filter by Color.
2. Click the desired color - orangish in this example.
   

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

For more data, delight see How to filter and sort by cell colour in Excel.

How to filter in Excel with search

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

Suppose y'all desire to view the records for all "eastward" regions. Only click the autofilter dropdown, and start typing the word "e" in the search box. Excel Filter volition immediately bear witness you all items that match the search. To display only those rows, either click OK in the Excel AutoFilter carte du jour, or press the Enter key on your keyboard.

To filter multiple searches, utilise a filter according to your first search term as demonstrated above, then type the 2d term, and as soon as the search results announced, select the Add electric current selection to filter box, and click OK. In this example, we are adding "west" records to the already filtered "eastward" items:

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

Filter past selected prison cell value or format

1 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 y'all want to filter your data past.
2. In the context menu, signal to Filter.
3. Select the desired choice: filter by selected cell's value, color, font color, or icon.

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

Re-apply a filter after changing data

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

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

How to copy filtered information in Excel

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

1. Select any filtered prison cell, and so printing Ctrl + A to select all filtered information including cavalcade headers.

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

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

Note. Normally, when you lot 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 addition to visible rows. To prevent this from happening, select a range of filtered cells, and press Alt + ; to select just visible cells ignoring hidden rows. If you lot're non accustomed to using keyboard shortcuts, you tin can use the Become To Special characteristic instead (Home tab > Editing grouping > Find & Select > Go to Special... > Visible Cells but).

How to clear filter

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

To clear a filter in a certain column, click the filter button in the column's header, and and so click Clear Filter from <Cavalcade name>:

How to remove filter in Excel

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

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

Filter not working in Excel

If Excel's AutoFilter stopped working partway down a worksheet, nearly likely it's because some new data has been entered exterior the range of filtered cells. To fix this, just re-utilise filter. If that does non assist and your Excel filters are all the same not working, clear all filters in a spreadsheet, and then apply them afresh. If your dataset contains any blank rows, manually select the entire range using the mouse, and then use autofilter. As soon every bit you do this, the new information will be added to the range of filtered cells.

Basically, this is how you add, apply and use filter in Excel. But in that location is much more to it! In the next tutorial, we will explore and capabilities of Advanced Filter and see how to filter information with multiple sets of criteria. Please stay tuned!

You may also exist interested in

How To Use Filters In Excel,

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

Posted by: kimafor1996.blogspot.com

Share this post