Filter function excel

Filter function excel DEFAULT

Excel FILTER Function

The Excel FILTER function returns a range filtered on criteria you define. It can also handle multiple AND/OR criteria.

FILTER Function Syntax

=FILTER(array, include, [if_empty])

array is the range or array containing the values you want filtered.

include is the logical test that returns a Boolean array (TRUE/FALSE)  the same height or width as the array.

if_empty is an optional value to return if the included array are empty i.e. if the filter results in no records.

Note: The FILTER function is part of the new Excel Dynamic Arrays family. At the time of writing, Dynamic Arrays are only available in Microsoft 365. Excel 2019 will not have the Dynamic Array functions.

Watch the Video

Subscribe YouTube

Download the Workbook

Excel FILTER Function Examples

Let’s say we want to filter the table in cells B14:F22 for the Sales Department and if there are no matching records return the text ‘No Records’. You can see the results of the FILTER function has ‘spilled’ creating a new table in cells B27:F29 below:

Excel FILTER function example

The formula; =FILTER(B14:F22,B14:B22="Sales","No Records") reads in English:

Filter the cells B14:F22, where the values in cells B14:B22 contain "Sales", if no matches are found then return the text “No Records.

Excel FILTER Functions using AND Criteria

We can add multiple criteria to the include argument by surrounding them in parentheses and using the multiplication operator as shown below:

filtered data using AND criteria example 1

The formula in B35; =FILTER(B14:F22,((B14:B22="Sales")*(C14:C22="Stamps"))) evaluates like so:

=FILTER(B14:F22, ( {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE} * {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} ) )

Two sets of TRUE and FALSE Boolean values are multiplied, which converts them to their numeric equivalents of 1 and 0, resulting in the finalinclude argument array:

=FILTER(B14:F22,({0;0;0;0;0;0;1;0;0}))

Only one record matches both criteria, and this is returned by the FILTER function:

filtered data using AND criteria example 2

Tip: You can add more AND criteria to the ‘include’ argument by following the same pattern of surrounding the criteria in parentheses and multiplying it by the existing criteria.

Excel FILTER Functions using OR Criteria

Similarly, we can filter using OR criteria by using the + operator as shown below:

filtered data using OR criteria example 1

The formula in B41; =FILTER(B14:F22,((B14:B22="Sales")+(C14:C22="Stamps"))) evaluates like so:

=FILTER(B14:F22, ( {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE} + {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} ) )

The two sets of TRUE and FALSE Boolean values are added together, which converts them to their numeric equivalents of 1 and 0, resulting in the final include argument array:

=FILTER(B14:F22,( {1;0;0;1;0;0;2;1;1}))

The rows with a zero are excluded from the Filter:

filtered data using OR criteria example 2

FILTER Function with Multiple If_Empty Criteria

The standard way to use the if_empty argument is to return a single value, like in the example below which returns the text ‘No Records’:

filtered data with multiple if_empty example 1

However, you can return a different result for each column by adding them with a comma separator and surrounding them in curly braces, as shown below:

filtered data with multiple if_empty example 2

Thanks to Bill Jelen, aka MrExcel for that last tip.

FILTER and Rearrange Non-contiguous Columns

We can use the CHOOSE function to specify the columns we want to filter and rearrange their order:

filter non-contiguous columns

We can also link the criteria to a data validation list (see cell C53) making it dynamically update

Related Tutorials


Sours: https://www.myonlinetraininghub.com/excel-functions/excel-filter-function

Excel 2010: Filtering Data

Lesson 13: Filtering Data

/en/excel2010/outlining-data/content/

Introduction

Lesson 13

Filters can be used to narrow down the data in your worksheet and hide parts of it from view. While it may sound a little like grouping, filtering is different because it allows you to qualify and display only the data that interests you. For example, you could filter a list of survey participants to view only those who are between the ages of 25 and 34. You could also filter an inventory of paint colors to view anything that contains the word blue, such as bluebell or robin's egg blue.

In this lesson, you'll learn how to filter the data in your worksheet to display only the information you need.

Filtering data

Filters can be applied in different ways to improve the performance of your worksheet. You can filter text, dates, and numbers. You can even use more than one filter to further narrow your results.

Optional: You can download this example for extra practice.

To filter data:

In this example, we'll filter the contents of an equipment log at a technology company. We'll display only the laptops and projectors that are available for checkout.

  1. Begin with a worksheet that identifies each column using a header row.
    Worksheet with header rowWorksheet with header row
  2. Select the Data tab, then locate the Sort & Filter group.
  3. Click the Filter command.
    Clicking the filter commandClicking the filter command
  4. Drop-down arrows will appear in the header of each column.
  5. Click the drop-down arrow for the column you want to filter. In this example, we'll filter the Type column to view only certain types of equipment.
    Filtering a columnFiltering a column
  6. The Filter menu appears.
  7. Uncheck the boxes next to the data you don't want to view, or uncheck the box next to Select All to quickly uncheck all.
  8. Check the boxes next to the data you do want to view. In this example, we'll check Laptop and Projector to view only these types of equipment.
    Selecting filter dataSelecting filter data
  9. Click OK. All other data will be filtered, or temporarily hidden. Only laptops and projectors will be visible.
    Filtered worksheetFiltered worksheet

Filtering options can also be found on the Home tab, condensed into the Sort & Filter command.

To add another filter:

Filters are additive, meaning you can use as many as you need to narrow your results. In this example, we'll work with a spreadsheet that has already been filtered to display only laptops and projectors. Now we'll display only laptops and projectors that were checked out during the month of August.

  1. Click the drop-down arrow where you want to add a filter. In this example, we'll add a filter to the Checked Out column to view information by date.
  2. Uncheck the boxes next to the data you don't want to view. Check the boxes next to the data you do want to view. In this example, we'll check the box next to August.
    Selecting filter dataSelecting filter data
  3. Click OK. In addition to the original filter, the new filter will be applied. The worksheet will be narrowed down even further.
    Worksheet with two filtersWorksheet with two filters

To clear a filter:

  1. Click the drop-down arrow in the column from which you want to clear the filter.
  2. Choose Clear Filter From.
    Clearing a filterClearing a filter
  3. The filter will be cleared from the column. The data that was previously hidden will be on display once again.

To instantly clear all filters from your worksheet, click the Filter command on the Data tab.

Advanced filtering

To filter using search:

Searching for data is a convenient alternative to checking or unchecking data from the list. You can search for data that contains an exact phrase, number, date, or simple fragment. For example, searching for the exact phrase Saris X-10 Laptop will display only Saris X-10 laptops. Searching for the word Saris, however, will display Saris X-10 laptops and any other Saris equipment, including projectors and digital cameras.

  1. From the Data tab, click the Filter command.
  2. Click the drop-down arrow in the column you want to filter. In this example, we'll filter the Equipment Detail column to view only a specific brand.
  3. Enter the data you want to view in the Search box. We'll enter the word Saris to find all Saris brand equipment. The search results will appear automatically.
    Entering a searchEntering a search
  4. Check the boxes next to the data you want to display. We'll display all of the data that includes the brand name Saris.
  5. Click OK. The worksheet will be filtered according to your search term.
    Worksheet filtered using SearchWorksheet filtered using Search

Using advanced text filters

Advanced text filters can be used to display more specific information, such as cells that contain a certain number of characters or data that does not contain a word you specify. In this example, we'll use advanced text filters to hide any equipment that is related to cameras, including digital cameras and camcorders.

  1. From the Data tab, click the Filter command.
  2. Click the drop-down arrow in the column of text you want to filter. In this example, we'll filter the Equipment Detail column to view only certain types of equipment.
  3. Choose Text Filters to open the advanced filtering menu.
  4. Choose a filter. In this example, we will choose Does Not Contain to view data that does not contain the text we specify.
    Selecting a text filterSelecting a text filter
  5. The Custom AutoFilter dialog box appears.
  6. Enter your text to the right of your filter. In this example, we'll enter cam to view data that does not contain these letters. That will exclude any equipment related to cameras, such as digital cameras, camcorders, camera bags, and the digicam printer.
    Entering filter textEntering filter text
  7. Click OK. The data will be filtered according to the filter you chose and the text you specified.

Using advanced date filters

Advanced date filters can be used to view information from a certain time period, such as last year, next quarter, or between two dates. Excel automatically knows your current date and time, making this tool easy to use. In this example, we'll use advanced date filters to view only the equipment that has been checked out this week.

  1. From the Data tab, click the Filter command.
  2. Click the drop-down arrow in the column of dates you want to filter. In this example, we'll filter the Checked Out column to view only a certain range of dates.
  3. Choose Date Filters to open the advanced filtering menu.
  4. Click a filter. We'll choose This Week to view equipment that has been checked out this week.
    Selecting a date filterSelecting a date filter
  5. The worksheet will be filtered according to the date filter you chose.
    Worksheet filtered by dateWorksheet filtered by date

If you're working along with the example file, your results will be different from the images above. If you want, you can change some of the dates so the filter will give more results.

Using advanced number filters

Advanced number filters allow you to manipulate numbered data in different ways. For example, in a worksheet of exam grades you could display the top and bottom numbers to view the highest and lowest scores. In this example, we'll display only certain types of equipment based on the range of ID #s that have been assigned to them.

  1. From the Data tab, click the Filter command.
  2. Click the drop-down arrow in the column of numbers you want to filter. In this example, we'll filter the ID # column to view only a certain range of ID #s.
  3. Choose Number Filters to open the advanced filtering menu.
  4. Choose a filter. In this example, we'll choose Between to view ID #s between the numbers we specify.
    Selecting a number filterSelecting a number filter
  5. Enter a number to the right of each filter. In this example, we'll view ID #s greater than or equal to 3000 but less than or equal to 4000. This will display ID #s in the 3000-4000 range.
    Entering filter numbersEntering filter numbers
  6. Click OK. The data will be filtered according to the filter you chose and the numbers you specified.
    Worksheet filtered by numberWorksheet filtered by number

Challenge!

  1. Open an existing Excel 2010 workbook. If you want, you can use this example.
  2. Filter a column of data. If you are using the example, filter the Type column so it displays only laptops and other equipment.
  3. Add another filter by searching for the data you want. If you are using the example, search for EDI brand equipment in the Item Description column.
  4. Clear both filters.
  5. Use an advanced text filter to view data that does not contain a certain word or phrase. If you are using the example, display data that does not contain the word cam. This should exclude any camera-related equipment, such as digital cameras and camcorders.
  6. Use an advanced date filter to view data from a certain time period. If you are using the example, display only the equipment that was checked out in September2010.
  7. Use an advanced number filter to view numbers less than a certain amount. If you are using the example, display all ID #s less than 3000.

/en/excel2010/formatting-tables/content/

Sours: https://edu.gcfglobal.org/en/excel2010/filtering-data/1/
  1. Solo prophecy dungeon warlock
  2. Pisces most compatible partner
  3. Cvs tea light candles
  4. Ncert book class 6
  5. Jun and ivy dress

The FILTER function allows you to filter a range of data based on criteria you define.

Your browser does not support video.

Note: This function is currently available only to Microsoft 365 subscribers.

In the following example we used the formula =FILTER(A5:D20,C5:C20=H2,"") to return all records for Apple, as selected in cell H2, and if there are no apples, return an empty string ("").

FILTER function - Filter Region by Product (Apple)

The FILTER function filters an array based on a Boolean (True/False) array.

=FILTER(array,include,[if_empty])

Argument

Description

array

Required

The array, or range to filter

include

Required

A Boolean array whose height or width is the same as the array

[if_empty]

Optional

The value to return if all values in the included array are empty (filter returns nothing)

Notes: 

  • An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the source array for our FILTER formula is range A5:D20.

  • The FILTER function will return an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel table, then the array will automatically resize as you add or remove data from your array range if you're using structured references. For more details, see this article on spilled array behavior.

  • If your dataset has the potential of returning an empty value, then use the 3rd argument ([if_empty]). Otherwise, a #CALC! error will result, as Excel does not currently support empty arrays.

  • If any value of the include argument is an error (#N/A, #VALUE, etc.) or cannot be converted to a Boolean, the FILTER function will return an error.  

  • Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

Examples

FILTER used to return multiple criteria

In this case, we're using the multiplication operator (*) to return all values in our array range (A5:D20) that have Apples AND are in the East region: =FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"").

Using FILTER with the multiplication operator (*) to return all values in our array range (A5:D20) that have Apples AND are in the East region.

FILTER used to return multiple criteria and sort

In this case, we're using the previous FILTER function with the SORT function to return all values in our array range (A5:D20) that have Apples AND are in the East region, and then sort Units in descending order: =SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1)

Using FILTER with the SORT function to return all values in our array range (A5:D20) that have Apples AND are in the East region, and then sort Units in descending order.

In this case, we're using the FILTER function with the addition operator (+) to return all values in our array range (A5:D20) that have Apples OR are in the East region, and then sort Units in descending order: =SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),""),4,-1).

FILTER and SORT together - Filter by Product (Apple) OR by Region (East)

Notice that none of the functions require absolute references, since they only exist in one cell, and spill their results to neighboring cells.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

RANDARRAY function

SEQUENCE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Dynamic arrays and spilled array behavior

Implicit intersection operator: @

Sours: https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759

Excel FILTER Function

The FILTER function "filters" a range of data based on supplied criteria. The result is an array of matching values from the original range. In plain language, the FILTER function will extract matching records from a set of data by applying one or more logical tests. Logical tests are supplied as the include argument and can include many kinds of formula criteria. For example, FILTER can match data in a certain year or month, data that contains specific text, or values greater than a certain threshold.

The FILTER function takes three arguments: array, include, and if_empty. Arrayis the range or array to filter. The include argument should consist of one or more logical tests. These tests should return TRUE or FALSE based on the evaluation of values from array. The last argument, if_empty, is the result to return when FILTER finds no matching values. Typically this is a message like "No records found", but other values can be returned as well. Supply an empty string ("") to display nothing.

The results from FILTER are dynamic. When values in the source data change, or the source data array is resized, the results from FILTER will update automatically. Results from FILTER will "spill" onto the worksheet into multiple cells.

Video: FILTER function basic example

Basic example

To extract values in A1:A5 that are greater than 100:

To extract rows in A1:C5 where the value in A1:A5 is greater than 100:

Notice the only difference in the above formulas is that the second formula provides a multi-column range for array. The logical test used for the include argument is the same.

Filter for Red group

Filter on red group example

In the example shown above, the formula in F5 is:

=FILTER(B5:D14,D5:D14=H2,"No results")

Since the value in H2 is "red", the FILTER function extracts data from array where the Group column contains "red". All matching records are returned to the worksheet starting from cell F5, where the formula exists.

Values can be hardcoded as well. The formula below has the same result as above with "red" hardcoded into the criteria:

=FILTER(B5:D14,D5:D14="red","No results")

No matching data

The value for is_empty is returned when FILTER does not find matching results. If a value for if_empty is not provided, FILTER will return a #CALC! error if no matching data is found:

=FILTER(range,logic)// #CALC! error

Often, is_empty is configured to provide a text message to the user:

=FILTER(range,logic,"No results")// display message

To display nothing when no matching data is found, supply an empty string ("") for if_empty:

=FILTER(range,logic,"")// display nothing

Values that contain text

To extract data based on a logical test for values that contain specific text, you can use a formula like this:

In this formula, the SEARCH function is used to look for "txt" in rng2, which would typically be a column in rng1. The ISNUMBER function is used to convert the result from SEARCH into TRUE or FALSE. Read a full explanation here.

Filter by date

FILTER can be used with dates by constructing logical tests appropriate for Excel dates. For example, to extract records from rng1 where the date in rng2 is in July you can use a generic formula like this:

This formula relies on the MONTH function to compare the month of dates in rng2 to 7.  See full explanation here.

More than one criteria

The include argument can be extended with boolean logic. For example, to extract only data where the group is "red" and score is greater than 80, you can use a formula like this:

=FILTER(range,(color="red")*(score>80),"No results")

where color and score are columns in range.

Complex criteria

To filter and extract data based on multiple complex criteria, you can use the FILTER function with a chain of expressions that use boolean logic. For example, the generic formula below filters based on three separate conditions: account begins with "x" AND region is "east", and month is NOT April.

See this page for a full explanation. Building criteria with logical expressions is an elegant and flexible approach that can be extended to handle many complex scenarios. See below for more examples.

Notes

  1. FILTER can work with both vertical and horizontal arrays.
  2. The include argument must have dimensions compatible with the array argument, otherwise FILTER will return #VALUE!
  3. If the include array includes any errors, FILTER will return an error.

FILTER is a new function available in Excel 365 only.

Sours: https://exceljet.net/excel-functions/excel-filter-function

Function excel filter

A lesson that teaches how to use the Microsoft Excel FILTER function- by SpreadsheetClass.com

The FILTER function in Excel is a very useful and frequently used function, that you will likely find the need for in many situations. Note that the FILTER function is only available in Microsoft Office 365, and Microsoft Office Online.

To filter by using the FILTER function in Excel, follow these steps:

  1. Type =FILTER( to begin your filter formula
  2. Type the address for the range of cells that contains the data that you want to filter, such as B1:C50
  3. Type a comma, and then type the condition for the filter, such as C3:C50>3 (To set a condition, first type the address of the "criteria column" such as B1:B, then type an operator symbol such as greater than (>), and then type the criteria, such as the number 3.
  4. Type a closing parenthesis and then press enter on the keyboard. Your entire formula will look like this: =FILTER(B1:C50,C1:C50>3)

In this article I will start with the basics of using the FILTER function (examples included), and then also show you some more involved ways of using the FILTER function. This article focuses specifically on the FILTER function that is typed into the spreadsheet cells as a formula, and not the filter command available from the toolbar and pop-up menus.

Using the FILTER function in Excel is almost the same as using it in Google Sheets, but there are slight differences between the two. Click here to read the Google Sheets version of this article

Here are the Excel Filters formulas:

Filter by a number

  • =FILTER(A3:B12, B3:B12>0.7)

Filter by a cell value

  • =FILTER(A3:B12, B3:B12<F1)

Filter by a text string

  • =FILTER(A3:B12, B3:B12="Late")

Filter where NOT equal to

  • =FILTER(A3:E1000, B3:B1000<>"Bob")

Filter by date

  • =FILTER(A3:C12,C3:C12<G1)   (Date entered in cell G1)
  • =FILTER(A3:C12,C3:C12<DATE(2019,6,1))

Filter by multiple conditions

  • =FILTER(A3:C12,(B3:B12="Late")*(C3:C12="Active"))   (AND logic)
  • =FILTER(A3:C12, (B3:B12="Late")+(C3:C12="Active"))   (OR logic)

Filter from another sheet

  • =FILTER('Sheet Name'!A3:B12,'Sheet Name'!B3:B12="Full Time")

The Excel FILTER function:

=FILTER(A3:B12, B3:B12=F1)

(Copy/Paste the formula above into your sheet and modify as needed)

A diagram and detailed formula breakdown for the Microsoft Excel FILTER function

The FILTER function in Excel allows you to filter a range of data by a specified condition, so that a new set of data will be displayed which only shows the rows/columns from the original data set that meets the criteria/condition set in the formula.

Excel description for FILTER function:

Syntax:
=FILTER(array,include,[if_empty])

Formula summary: “The FILTER function filters an array based on a Boolean (True/False) array.”

array (Required): The array, or range to filter

include (Required): A Boolean array whose height or width is the same as the array

[if_empty] (Optional): The value to return if all values in the included array are empty (filter returns nothing)

The source range that you want to filter, can be a single column or multiple columns.

The range that is used to check against the criteria that you set, must be a single column (later I will show you how to filter by multiple conditions, but don’t worry about that for now).

The criteria that set in the condition can be manually typed into the formula as a number or text, or it can also be a cell reference.

*Note that the source range and the single column range for the condition, must be the same size (must contain the same number of rows), or the cell will display an error.

Filtering by a single condition in Excel

First let's go over using the FILTER function in Excel in its simplest form, with a single condition/criteria.

I will show you how to filter by a number, a cell value, a text string, a date… and I will also show you how to use varying "operators" (Less than, Equal to, etc…) in the filter condition.

Part 1: How to filter by a number

In this first example on how to use the filter function in Excel, the scenario is that we have a list of students and their grades, and that we want to make a filtered list of only students who have a perfect grade.

The task: Show a list of students and their scores, but only those that have a perfect grade

The logic: Filter the range A3:B12, where the column B3:B12 is greater than 0.7 (70%)

The formula: The formula below, is entered in the blue cell (D3), for this example

=FILTER(A3:B12, B3:B12>0.7)

An example of filtering by a number in Excel by using the FILTER function

Operators that can be used in the FILTER function:

In this example we are using the operator "=" (Equal To) for the filter condition/criteria, but you can also use any of the following:

"=" (Equals)
">" (Greater than)
"<" (Less than)
"<>" (Not equal to)
">=" (Greater than or equal to)
"<=" (Less than or equal to)

Part 2: How to filter by a cell value in Excel

In this example, we want to achieve the same goal as discussed above, but rather than typing the condition that we want to filter by directly into the formula, we are using a cell reference.

When you filter by a cell value in Excel, your sheet will be setup so that you can change the value in the cell at any time, which will automatically update the value that the filter criteria it attached to.

In this example, you will notice that instead of directly typing the number “0.9” into the formula itself, the filter criteria is set as cell G1, where the “0.9” value is entered.

The task: Show a list of students and their scores, but only those that have a score below 90%

The logic: Filter the range A3:B12, where B3:B12 is less than the value that is entered in the cell F1 (0.9)

The formula: The formula below, is entered in the blue cell (D3), for this example

=FILTER(A3:B12, B3:B12<F1)

An example of filtering based on a cell value in Excel by using the FILTER function

Part 3: How to filter by text in Excel

In this example, we are going to use a text string as the criteria for the filter formula. This is very similar to using a number, except that you must put the text that you want to filter by inside of quotation marks.

In this scenario we are filtering a list that shows customers and their payment status, and we want to display only customers that have a payment status of “Late”.

The task: Show a list of customers who are past due on their payments

The logic: Filter the range A3:B12, where B3:B12 equals the text, “Late”

The formula: The formula below, is entered in the blue cell (D3), for this example

=FILTER(A3:B12, B3:B12="Late")

Example of filtering by text string in Excel, by using the FILTER function

Part 4: Using NOT EQUAL TO in the Excel FILTER function

Now that you have got a basic understanding of how to use the filter function in Excel, here is another example of filtering by a string of text, but in this example we will use the "not equal" operator (<>), so that you can learn how to filter a range and output data that is NOT equal to criteria that you specify.

In this example we will also use a larger data set to demonstrate a more extensive application of the FILTER function in the real world.

You may be surprised at how often a situation comes up when you need to filter data where it is “not equal to” a certain number or piece of text that you specify.

In this example let's say that we have a report/spreadsheet that shows data from sales calls that occur at your company, and we want to filter the data so that a specified sales rep (Bob) is NOT included in the filter output.

The task: Show sales call data for all sales reps, except Bob

The logic: Filter the range A2:E1000, where B2:B1000 DOES NOT equal the text, “Bob”

The formula: The formula below, is entered in the blue cell (G3), for this example

=FILTER(A3:E1000, B3:B1000<>"Bob")

An example of filtering where not equal to in Excel, by using the FILTER function

Notice that the filtered data on the right side of the image above does not contain any of the rows/calls that Bob was involved in.

Part 4: How to filter by date in Excel

Filtering by a date in Excel can be done in a couple of ways, which I will show you below. If you try to type a date into the FILTER function like you normally would type into a cell… the formula will not work correctly.

So you can either type the date that you want to filter by into a cell, and then use that cell as a reference in your formula… or you can use the DATE function.

When filtering by date you can use the same operators (>, <, =, etc…) as in other FILTER function applications. In Excel each different day/date is simply a number that is put into a special visual format. For example, in Excel, the date "06/01/2019" is simply the number "43,617", but displayed in date format. When you add one day to the date, this number increments by one each time… i.e "43,618"   "43,619"   "43,620"

So, one date can be considered to be "greater than" another date, if it is further in the future. Conversely, one date can be said to be "less than" another date, if it is further in the past.

In this first example we will filter by a date by using a cell reference. This is similar to the example we went over in part 2, but in this example instead of working with percentages, we are dealing with dates.

Let's say that we want to filter a list of students, their test scores, and the dates that the tests were completed… and we want to show only tests that were taken before June (06/01/2019).

Filter by date in Excel example 1:

The task: Show only tests that were taken before June

The logic: Filter the range A3:C12, where C3:C12 is less than the date that is entered in cell G1 (06/01/2019)

The formula: The formula below, is entered in the blue cell (E3), for this example

=FILTER(A3:C12,C3:C12<G1)

An example of filtering by a date in Excel by using a cell reference with the FILTER function- Basic example

Filter by date in Excel example 2:

In this second example on filtering by date in Excel we are using the same data as above, and trying to achieve the same results… but instead of using a cell reference, we will use the DATE function so that you can type enter the date directly into the FILTER function.

When using the DATE function to designate a certain date, you must first enter the year, then the month, and then the day… each separated by commas (shown below).

The task: Show only tests that were taken before June

The logic: Filter the range A3:C12, where C3:C12 is less than the date of (06/01/2019)

The formula: The formula below, is entered in the blue cell (E3), for this example

=FILTER(A3:C12,C3:C12<DATE(2019,6,1))

An example of filtering by date in Excel by using the DATE function with the FILTER function- Basic example (Lesson by SpreadsheetClass.com)

Filter by multiple conditions in Excel

When using the Excel FILTER function you may want to output a set of data that meets more than just one criteria. I will show you two ways to filter by multiple conditions in Excel, depending on the situation that you are in, and depending on how you want to formula to operate.

The normal way of adding another condition to your filter function, (as shown by the formula syntax in Excel), will allow you to set a second condition, where the first AND second condition must be met to be returned in filter output.

However I will also show you how to make a slight modification to the function so that you can choose to set a second condition where EITHER condition can be met to return/display in the filter function's output/destination. (Separate the conditions with an asterisk to use AND logic, or separate the conditions with a plus sign to use AND logic.)

Part 5: Filtering by 2 conditions where BOTH MUST BE TRUE

In this example, we are going to filter a set of data, and only display rows where BOTH the first condition AND the second condition are met/true.

To use a second condition in this way (with AND logic), simply enter the second condition into the formula after the first condition, separated by an asterisk (*). Each condition must be inside of its own set of parenthesis (shown below).

When using the filter formula with multiple conditions like this, the columns that are referenced in each condition must be different.

In this scenario we want to filter a list that shows customers, their payment status, and their membership status… and to show only customers who have an active membership AND who are also late on their payment.

This will make sure that customers with an inactive membership who are still designated as being late on payment in the system… are not shown in the filter results, and not put on the list for being sent a "late payment" notice.

The task: Show a list of customers who are late on their payments, but only those with active memberships

The logic: Filter the range A3:C12, where B3:B12 equals the text “Late”, AND where C3:C12 equals the text “Active”

The formula: The formula below, is entered in the blue cell (E3), for this example

=FILTER(A3:C12,(B3:B12="Late")*(C3:C12="Active"))

Example of filtering by multiple conditions in Excel by using the FILTER function- Basic example (AND Logic)

Part 6: Filtering by 2 conditions where EITHER ARE TRUE, not necessarily both

In this example we are going to filter a set of data and only display rows where EITHER the first condition OR the second condition are met/true.

To use a second condition in this way (with OR logic), simply enter the second condition into the formula after the first condition, separated by a plus sign. Each condition must be inside of its own set of parenthesis (shown below).

When using the FILTER formula in this way, you can choose criteria from the same or different columns.

In this scenario we want to filter the same customer data as shown in the previous example, but this time we want to show a list of customers who EITHER have an active membership OR who are late on their payment. This will give a list of customers who can be sent a notice for payment… including active members, or/also inactive members who are late on their final payment.

The task: Show a list of customers who are active members, and include customers who are late on payment even if they are not active members

The logic: Filter the range A3:C12, where B3:B12 equals the text “Late”, OR where C3:C12 equals the text “Active”

The formula: The formula below, is entered in the blue cell (E3), for this example

=FILTER(A3:C12, (B3:B12="Late")+(C3:C12="Active"))

An example of filtering by multiple conditions in Excel by using the FILTER function- Basic example (OR Logic)

How to filter from another sheet in Excel

You may often find situations where you need to filter from another sheet in Excel, where your raw unfiltered data is on one tab, and your filter formula / filter output is on another tab.

This can be done by simply referring to a certain tab name when specifying the ranges in the filter. So where you would normally set a range like "A3:B", when referencing another sheet while filtering you specify the tab name by adding the tab name and an exclamation mark before the column/row portion of the range, like "TabName!A3:B"

However when the tab name has a space in it, it is necessary to use an apostrophe before and after the tab name, like 'Tab Name'!A3:B.

Here is an example of how to filter data from another tab in Excel, where your filter formula will be on a different tab than the source range.

Let's say that you have a list of employees and their schedule type (Full Time / Part Time) on one tab, and that you want to display a filtered list of full time employees on another tab.

The task: Filter the list of employees on the tab labeled "Filter List", and show a list of employees who have a full time schedule, on a separate tab

The logic: Filter the range 'Filter List'!A3:B12, where the range 'Filter List'!B3:B12 is equal to the text "Full Time"

The formula: The formula below, is entered in the blue cell (A3), for this example

=FILTER('Filter List'!A3:B12,'Filter List'!B3:B12="Full Time")

Here is a list of employees and their schedules, which is held on a tab labeled "Filter List"

An example of filtering from another sheet in Excel- Raw data list tab

And here is a filtered list of employees who have full time schedules, where the filter formula and output data are held on a separate tab.

Example of filtering from another sheet in Excel by using the FILTER function- Filter formula tab

Pop Quiz: Test your knowledge

Answer the questions below about the Excel FILTER function, to refine your knowledge! Scroll to the very bottom to find the answers to the quiz.

Question #1

Which of the following formulas uses a "cell reference" in the filter condition?

  1. =FILTER(A1:D15, B1:B15<0.6)
  2. =FILTER(A2:C15, C2:C15=F1)
  3. =FILTER(A1:P25, G1:G25="Yes")

Question #2

Which of the following formulas uses the "Not Equal" operator?

  1. =FILTER(C1:T50, J1:J50>100)
  2. =FILTER(A1:B75, B1:B75="No")
  3. =FILTER(S1:Z100, T1:T100<>"True")

Question #3

True or False: If the column(s) in the source range and the column in the filter condition are not the same size (if one has more rows than the other), the formula will not work, and will display an error.

  1. True
  2. False

Question #4

Which of the following formulas uses AND logic, where BOTH conditions must be met to satisfy the filter criteria?

  1. =FILTER(C1:F20, (F1:F20="Yes")+(E1:E20="Active"))
  2. =FILTER(C1:F35, (F1:F35="Yes")*(E1:E35="Active"))

Question #5

Which of the following formulas uses OR logic, where EITHER condition can be met to satisfy the filter criteria?

  1. =FILTER(A1:K10, (K1:K10="Yes")*(J1:J10="Active"))
  2. =FILTER(A3:K33, (K3:K33="Yes")+(J3:J33="Active"))

Answers to the questions above:

Question 1:   2

Question 2:   3

Question 3:   1

Question 4:   2

Question 5:   2

Sours: https://www.spreadsheetclass.com/excel-filter-function/
FILTER function in Excel (with 6 examples) - Best new Excel function in decades - Excel Off The Grid

Excel FILTER Function – How To Use

The FILTER function is an Excel function that lets you fetch or "filter" a data set based on the criteria supplied via an argument. The FILTER function was introduced in Office 365 and will not be accessible in Office 2019 or earlier versions.

FILTER is an in-built worksheet function and belongs to Excel's new Dynamic Arrays function category. The FILTER function returns an array of values that are spilled onto your worksheet unless the function is nested to relay the output to another function.

FILTER is a dynamic function. This means when you alter the values in the source data or resize the source data array, the Excel FILTER function will automatically update the returned values.

Excel FILTER Function

Syntax

The syntax of the Filter function is as follows:

Arguments:

– This is a required argument where you specify the range or array that you want to filter.
– This is a required argument where you supply the filtering criteria as a Boolean array.
– This is an optional argument where you specify a value that the function should return if none of the entries meet the supplied criteria.

Important Characteristics of the FILTER function

  • You can use the FILTER function for filtering both horizontal as well as vertical arrays.
  • When you use the FILTER function between two separate workbooks, ensure that both of them are open. If they are not, the function will return a #REF! error.
  • Since the FILTER function spills the output (assuming your data is so organized), ensure that you have sufficient empty cells to the right and below. If you do not, the function will return a #SPILL error.
  • The FILTER function returns a #VALUE! error if the dimensions supplied in the  argument are incompatible with the  argument.
  • The FILTER function was introduced in Office 365 and is not available in Office 2019 or earlier version.

Examples of the FILTER function

Let's have a look at the examples of the FILTER function:

Example 1 – Plain-vanilla Version of the Filter function

Suppose we want a list of students that have Grade "A" in the data set.

Excel_Filter-Function_Example_001

In order to do this, we will input the entire data set, i.e., A2:C16, in the argument.

In the  argument, we will input C2:C16="A" which will produce a Boolean array that assigns TRUE for all 'A's.

The last argument () is an optional string where you will let the formula know what it should return if it does not find any values. In our case, we use a text string "No matches", but you could also have the formula return nothing by passing an empty string ("").

So the final formula would be –

If you prefer, you could also have a cell reference instead of adding a character or a text string in the argument. For example, let's say you write "A" in cell G6 and then input the argument as C3:C17=G6.

Excel_Filter-Function_Example_002

So the formula would be –

The FILTER function will also work smoothly for horizontally organized data sets. Just ensure that the width of ranges defined in the  and  arguments are the same.

Also, in case of no matching records the formula, returns the string no match as shown.

Excel_Filter-Function_Example_2.1

Since none of the students in the above dataset has Grade "D", the result is the "No matches" string.

Example 2 – FILTER function Used in Combination With the EXACT function

Nesting the EXACT function inside the FILTER function enables you to fetch an exact, case-sensitive match.

Excel_Filter-Function_Example_008

The EXACT function has a simple role here and works as the  argument in the FILTER function. EXACT returns TRUE for an exactly matching string and FALSE otherwise. As such, the EXACT function will return 15 Boolean values (for each cell in the B2:B16 range), which in our case will look something like this:

At position 5, "Bing" is the only match and hence returns TRUE.

This array is relayed to the FILTER function as the  argument. Next, FILTER uses the array returned by EXACT to return only those rows that contain the string "Bing" (i.e., rows that have been assigned a TRUE value by the EXACT function).

Note: Please note that we can also get to the same output by just adding the condition (A2:A16)="Bing" in the argument, but in that case, the search will be case-insensitive.

Example 3 – Filtering with Wildcards Using the FILTER function

Let's discuss some big-boy formulas now. The FILTER function does not in itself support wildcard characters, but we can input a logical test in the  argument to get the output we want.

We will nest the ISNUMBER and SEARCH functions, like so:

Excel_Filter-Function_Example_007

Let's walk through each component of the formula and see what role they play. We will work out the SEARCH function's output first. The search function is looking for any string in column A that has the letter "G". We have 3 last names that contain a G – Bing, Geller, and Green. Remember, that search function only returns the position of the searched character.

Therefore, wherever the SEARCH function finds a G, it will return a number (i.e., the position of G), while for others, you will get a #VALUE! error. If you want to perform a case-sensitive SEARCH, you can replace the SEARCH function with the FIND function. Let's replace the SEARCH function's return in our formula:

Next, we have the ISNUMBER function. ISNUMBER function has a simple role to play. It will look at the SEARCH function's return and assign TRUE for results that are a number and FALSE for results that are not a number (i.e., are a #VALUE! error in our case).

If you follow what is happening in the formula so far, the remaining part is a cakewalk. The ISNUMBER function relays this output (i.e., 3 TRUEs for the numbers and 12 FALSEs for the #VALUE! errors) to the FILTER function. The FILTER function will then let only those data points make it to the final output, for which the result was TRUE.

So, as our final output, we should have 3 names – Bing, Geller, and Green.

Example 4 – FILTER function With Multiple Criteria (AND/OR operator)

We have so far not used the Scores column from our data set. Let's remedy that, shall we?

Excel_Filter-Function_Example_003

So, here we are trying to find the students who have Grade "A" and a score of above 100.

We will first use the AND criteria, with an asterisk (*). To do this, we will extend the  argument using Boolean logic, like so:

Using the '*' multiplies the Boolean values, where True equals 1, and False equals 0. So, when the expressions inside the parentheses are multiplied in the formula, there are two possibilities:

  • You get a FALSE (or a 0): If the expressions inside both the parentheses return False (0*0) or one of them returns FALSE (0*1).
  • You get a TRUE (or a 1): If the expressions inside both the parentheses return TRUE (1*1).

Therefore, when the first parenthesis finds an A and returns TRUE and the second parenthesis finds the score to be greater than 100 and returns TRUE as well, we will have our return (or get the string "No matches"). Using logical expressions like this is an efficient approach to filter criteria.

Much the same way, we can use the OR criteria by using a plus (+) between two parentheses in the  argument, like so:

This will get us all the students who either have Grade "A" or scores greater than 100.

Excel_Filter-Function_Example_004

Similar to what we saw for the AND criteria, we can get two possible outcomes when Boolean values in the  argument are added:

  • You get a FALSE (or a 0): If the expressions inside both parentheses return a FALSE (0+0).
  • You get a TRUE (or a 1): If the expression inside one of the parenthesis returns a TRUE (0+1) or both parentheses return a TRUE (1+1).

Wait, if both parentheses return 1, it will add up to 2, right?

Fortunately, that does not matter. Excel will consider anything that is 0 as FALSE, anything that is not 0 as TRUE.

This is how you can incorporate the AND or OR criteria in your formulas.

Example 5 – Filtering Duplicates Using the FILTER and COUNTIFS functions

If your data set is massive, it is quite likely that you may need to deal with duplicates. When a data point has more than a single instance, we can filter the duplicate instances by using a nested formula, like so:

Excel_Filter-Function_Example_005

Essentially, we are using the COUNTIFS function to count the instances of data points and extracting those data points that occur more than once. By nesting the COUNTIFS function in the  argument, you are instructing the FILTER formula to include only those results that occur more than once (>1).

You can add or remove the number of columns passed to the COUNTIFS function for filtering duplicate data based on your own requirements.

The FILTER function will spill the return with duplicate instances into a range of cells, as shown in the image.

Recommended Reading:How to find duplicates in excel

Example 6 – Filtering Blank Records Using the FILTER function

We will revisit what we learned in a previous example where we discussed the use of AND criteria using an '*'. We will use a <> operator (i.e., not equal to) inside the parenthesis to check if a cell has an empty string (""), like so:

Excel_Filter-Function_Example_006

Here, we are checking the data points contained in the A, B, and C columns and retrieving only those data points that have a value in all three columns. To do this, we instruct Excel to check for empty cells by defining a column and checking for empty cells by adding a '<>' (not equal to) operator and an empty string (""). To add an AND criteria, we will use a '*' between the parenthesis.

If a cell in any of those columns is empty, the return will exclude that particular row entirely.

Example 7 – FILTER function Used in Combination with SUM, MIN, MAX, and AVERAGE functions

The FILTER function packs in a punch. In addition to performing a whole list of tasks discussed above, it is also capable of summarizing the data it returns after filtering.

To accomplish this, we will use the aggregation family of functions – like SUM, MIN, MAX, and AVERAGE.

Excel_Filter-Function_Example_009

It is actually pretty straightforward. All we need to do is relay the return from the FILTER function to any of the mentioned aggregation functions, like so:

Here, the FILTER function will include all rows that have the letter "A" and return 0 for rows that do not match. It will then pick up the values that correspond to "A" from column B. These values will be handed over to the aggregation functions, giving us our final output.

Note: It is important to specify the argument as 0. Using a string like "No matches" will result in a #VALUE! error, since the aggregation functions cannot handle strings in case of non-matching records.

Example 8 – FILTER function to return Non-Adjacent Columns

Let's say we want just the Name and Score columns of students with Grade "A". How must we explain to the FILTER function that we want just these two columns when they are non-contagious?

While there is no separate formula to do this, we can accomplish this by sprinkling some logic into the FILTER function. We will nest one FILTER formula inside another FILTER formula and use Boolean values in the  argument of the outer FILTER formula to exclude the Names column, like so:

Excel_Filter-Function_Example_010

Let's put this formula in words.

The nested (inner) FILTER function is just a simple formula, which we are comfortable with by now.

So, let's jump over to the outer FILTER function.

The nested FILTER function works as the outer FILTER function's  argument, informing the outer FILTER function that this is where I want the returned values to come from. The  argument is where the magic happens. We use "1, 0, 1" (alternatively, we could use TRUE, FALSE, TRUE) and let the outer FILTER function know that we want to include only the first and third columns.

Simple, isn't it?

I hope I unraveled all the mysteries surrounding the brand new FILTER function and covered a little more as well. Incorporate these formulas into your worksheets, and you will become a FILTER function ninja in no time. I will see you soon with another intriguing Excel function, until then… sayonara.

Sours: https://www.exceltrick.com/formulas_macros/excel-filter-function/

You will also like:

Ground when the enemy was about to draw a pistol to finish him off. But Craig, despite the frantic ringing and pain in his head, rushed forward and knocked the man off his feet just as a loud shot tore through the air. They both fell to the ground and tried to beat each other with their hands.

But Craig was on top, and he himself was many times stronger, so he simply began to bludgeon the enemy in the face. Turning him into a bloody mess until the enemy stopped twitching.



13734 13735 13736 13737 13738