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. Then, the elements of all the arrays in the same positions are multiplied. The result of each logical expression is an array of Boolean values, where TRUE equates to 1 and FALSE to 0. The multiplication operation processes the arrays with the AND logic, ensuring that only the records that meet all the criteria are returned. To filter data with multiple criteria, you supply two or more logical expressions for the include argument:įILTER(array, ( range1= criteria1) * ( range2= criteria2), "No results") Filter with multiple criteria (AND logic) 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. How to filter in Excel - formula examples If you wish the array to resize automatically, then convert it to an Excel table and build formulas with structured references, or create a dynamic named range. However, the range supplied for the array argument is not updated when new entries are added to the source data. The results of the Excel FILTER function are dynamic, meaning they update automatically when values in the original data set change.So, please make sure you always have enough empty cells down and to the right, otherwise you'll get a #SPILL error. The FILTER function automatically spills the results vertically or horizontally in the worksheet, depending on how your original data is organized.To effectively filter in Excel with formulas, here are a couple of important points to take notice of: 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: In case your data is organized horizontally from left to right like shown in the screenshot below, the FILTER function will work nicely too. If you'd rather return nothing in this case, then supply an empty string ("") for the last argument: If no records match the specified criteria, the formula returns the value you put in the if_empty argument, "No results" in this example: It extracts the filtered records into the so-called spill range (E4:G7 in the screenshot below), beginning in the cell where the formula is entered: Unlike Excel's Filter feature, the function does not make any changes to the original data. F1, and use a cell reference instead of hardcoding the value directly in the formula: In practice, it's more convenient to input the criteria in a separate cell, e.g. To have it done, we supply the expression B2:B13="C" to the include argument, which will produce a required Boolean array, with TRUE corresponding to "C" values. In Excel 2019, Excel 2016 and earlier versions, it is not supported.įor starters, let's discuss a couple of very simple cases just to gain more understanding how an Excel formula to filter data works.įrom the below data set, supposing you want to extract the records with a specific value in the Group, column, say group C. The FILTER function is only available in Excel for Microsoft 365 and Excel 2021.
0 Comments
Leave a Reply. |