How does filter work in excel

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.

Array is the range or array to filter. The include argument should consist of one or more logical tests. Typically this is a message like "No records found", but other values can be returned as well.

Supply an empty string "" to display nothing. When values in the source data change, or the source data array is resized, the results from FILTER will update automatically. 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. 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:. To extract data based on a logical test for values that contain specific text, you can use a formula like this:. Read a full explanation here. For example, to extract records from rng1 where the date in rng2 is in July you can use a generic formula like this:.

See full explanation here. The include argument can be extended with boolean logic. If you'd rather return nothing in this case, then supply an empty string "" for the last argument:. In case your data is organized horizontally from left to right like shown in the screenshot below, the FILTER function will work nicely too.

Just make sure you define appropriate ranges for the array and include arguments, so that the source array and Boolean array have the same width:.

To effectively filter in Excel with formulas, here are a couple of important points to take notice of:. Now that you know how a basic Excel filter formula works, it's time to get some insights into how it could be extended for solving more complex tasks. To filter data with multiple criteria, you supply two or more logical expressions for the include argument:. The multiplication operation processes the arrays with the AND logic , ensuring that only the records that meet all the criteria are returned.

Technically, it works this way:. Then, the elements of all the arrays in the same positions are multiplied. Since multiplying by zero always gives zero, only the items for which all the criteria are TRUE get into the resulting array, and consequently only those items are extracted.

For this, we set up the following criteria: type the name of the target group in F2 criteria1 and the minimum required number of wins in F3 criteria2. Given that our source data is in A2:C13 array , groups are in B2:B13 range1 and wins are in C2:C13 range2 , the formula takes this form:. As the result, you get a list of players in group A who have secured 2 or more wins:.

First off, it should be noted that it's not possible to make up a generic formula to filter by date in Excel. In different situations, you will need to build criteria differently, depending on whether you want to filter by a specific date, by month, or by year. The purpose of this example is to demonstrate the general approach. To our sample data, we add one more column containing the dates of the last win column D. 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. 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. Worksheet with header row. Clicking the filter command. Filtering a column.

Selecting filter data. Filtered worksheet. Worksheet with two filters. Select personalised content. Create a personalised content profile. Measure ad performance. Select basic ads. Create a personalised ads profile. Select personalised ads.

Apply market research to generate audience insights. Measure content performance. Develop and improve products. List of Partners vendors. Ted French. Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. Updated on February 10, Tweet Share Email. Select a header drop-down menu and choose Number Filters or Text Filters to view filter options.