{"id":3180,"date":"2021-03-29T16:50:00","date_gmt":"2021-03-29T23:50:00","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3180"},"modified":"2021-03-30T08:48:22","modified_gmt":"2021-03-30T15:48:22","slug":"how-to-use-filter-function-in-excel","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3180","title":{"rendered":"How to Use FILTER function in Excel"},"content":{"rendered":"\n<p>Filtering is a common everyday action for most Excel users.&nbsp; Whether using AutoFilter or a Table, it is a convenient way to view a subset of data quickly.&nbsp; Until the FILTER function came to Excel, there was no easy way to achieve this with formulas.&nbsp; When Microsoft announced the changes to Excel\u2019s&nbsp;calculation engine, they also introduced a host of new functions.&nbsp; One of those new functions is FILTER, which returns all the cells from a range that meet specific criteria. At the time of writing, the FILTER function is only available to those on a Microsoft 365 subscription.&nbsp; It will not be available in Excel 2019 or earlier versions.<\/p>\n\n\n\n<p><strong>Download the example file<\/strong><\/p>\n\n\n\n<p>I recommend you download the example file for this post.&nbsp; Then you\u2019ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.<\/p>\n\n\n\n<p><a href=\"https:\/\/exceloffthegrid.com\/download\/10052\/\" target=\"_blank\" rel=\"noreferrer noopener\"><\/a><br>Download the file:&nbsp;<a href=\"https:\/\/exceloffthegrid.com\/download\/10052\/\" target=\"_blank\" rel=\"noreferrer noopener\">0035 FILTER function in Excel.zip<\/a><\/p>\n\n\n\n<p><strong>Watch the video:<\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/youtu.be\/23wnQNpFZho\" target=\"_blank\" rel=\"noreferrer noopener\">Watch the video on YouTube<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Arguments of the FILTER function<\/h2>\n\n\n\n<p>Before we look at the arguments required for the FILTER function, let\u2019s look at a basic example to appreciate what it does.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/07\/FILTER-Function-Basic-Usage.gif\" alt=\"FILTER Function Basic Usage\" class=\"wp-image-10009\"\/><\/figure>\n\n\n\n<p>Here the FILTER function returns all the values in cells B3-B10 where the number of characters is greater than 15.&nbsp; Not a scenario that many of us will need, but it demonstrates the power of the new FILTER function.<\/p>\n\n\n\n<p>FILTER has three arguments:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=FILTER(array, include, [if_empty])<\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>array:<\/strong>&nbsp;The range of cells, or array of values to filter.<\/li><li><strong>include:<\/strong>&nbsp;An array of TRUE\/FALSE results, where the TRUE values will be retained in the filter.<\/li><li><strong>[if_empty]:<\/strong>&nbsp;The value to display if no rows are returned.<\/li><\/ul>\n\n\n\n<p><strong>Generate accurate VBA code in seconds with AutoMacro<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/www.automateexcel.com\/vba-code-generator\/?utm_source=200287236\" target=\"_blank\" rel=\"noreferrer noopener\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2019\/11\/AutoMacroExample.gif\" alt=\"AutoMacroExample\" class=\"wp-image-9260\"\/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Examples of using the FILTER function<\/h2>\n\n\n\n<p>The following examples illustrate how to use the FILTER function.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example 1 \u2013 FILTER returns an array of rows and columns<\/h3>\n\n\n\n<p>In this example, cell F3 contains a single formula, but this formula returns an array of values into the neighboring rows and columns.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/07\/FILTER-Function-retuns-array-of-rows-and-columns.png?ezimgfmt=rs:800x333\/rscb2\/ng:webp\/ngcb2\" alt=\"FILTER Function retuns array of rows and columns\" class=\"wp-image-10010\"\/><\/figure>\n\n\n\n<p>The formula in cell F3 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=FILTER(B3:D10,C3:C10&gt;100)<\/pre>\n\n\n\n<p>This single formula is returning 2 rows and 3 columns of data where the values in C3-C10 are higher than 100.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example 2 \u2013 #CALC! error caused by the FILTER function<\/h3>\n\n\n\n<p>The screenshot below displays what happens when the result of the FILTER function has zero results \u2013 we get the new #CALC! error.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/07\/FILTER-CALC-Error.png?ezimgfmt=rs:800x339\/rscb2\/ng:webp\/ngcb2\" alt=\"FILTER #CALC! Error\" class=\"wp-image-10012\"\/><\/figure>\n\n\n\n<p>The formula in cell F3 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=FILTER(B3:D10,C3:C10&gt;200)<\/pre>\n\n\n\n<p>As no rows meet the criteria of Invoice Value being higher than 200), the FILTER cannot return a value, so the #CALC! error is displayed.<\/p>\n\n\n\n<p>Thankfully, Microsoft has given us the&nbsp;<em>if_empty<\/em>&nbsp;argument, which displays a message if there are no rows returned.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/07\/FILTER-Function-with-if_empty-argument.png?ezimgfmt=rs:800x335\/rscb2\/ng:webp\/ngcb2\" alt=\"FILTER Function with if_empty argument\" class=\"wp-image-10011\"\/><\/figure>\n\n\n\n<p>The formula in cell F3 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=FILTER(B3:D10,C3:C10&gt;200,\"No Results\")<\/pre>\n\n\n\n<p>In the screenshot above, \u201c<em>No Results\u201d<\/em>&nbsp;displays instead of the #CALC! error.<\/p>\n\n\n\n<p>If we wanted to display a result in each column, we could include a constant array within the&nbsp;<em>if_empty<\/em>&nbsp;argument.&nbsp; The following would show&nbsp;<em>n\/a<\/em>&nbsp;in the Invoice Value and Days Due columns.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=FILTER(B3:D10,C3:C10&gt;200,{\"No Results\",\"n\/a\",\"n\/a\"})<\/pre>\n\n\n\n<p>This formula would result in the following:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/07\/FILTER-Function-no-results-multiple-if_empty-values.png?ezimgfmt=rs:800x333\/rscb2\/ng:webp\/ngcb2\" alt=\"nction no results, multiple if_empty values\" class=\"wp-image-10013\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Example 3 \u2013 FILTER expands automatically when linked to a table<\/h3>\n\n\n\n<p>This example shows how the FILTER function responds when linked to an Excel table.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/07\/FILTER-expands-automatically-with-table.gif\" alt=\"FILTER expands automatically with table\" class=\"wp-image-10014\"\/><\/figure>\n\n\n\n<p>New records added to the table which meet the criteria are automatically added to the spill range of the function.&nbsp; Amazing stuff!https:\/\/be4aa64affb7f0c0054ae308f78a0cbd.safeframe.googlesyndication.com\/safeframe\/1-0-38\/html\/container.html<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example 4 \u2013 Using FILTER with multiple criteria.<\/h3>\n\n\n\n<p>Example 4 shows how to apply FILTER with multiple criteria.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/07\/FILTER-with-multiple-AND-conditions.png?ezimgfmt=rs:800x338\/rscb2\/ng:webp\/ngcb2\" alt=\"FILTER with multiple AND conditions\" class=\"wp-image-10015\"\/><\/figure>\n\n\n\n<p>The formula in cell F3 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=FILTER(B3:D10,(C3:C10&gt;50)*(D3:D10&gt;30))<\/pre>\n\n\n\n<p>For anybody who has used the SUMPRODUCT function, this method of applying multiple conditions will be familiar.&nbsp; Multiplication creates AND logic (i.e., all the criteria must be TRUE).&nbsp; The example above shows where the Invoice Value is greater than 50&nbsp;<strong>and<\/strong>&nbsp;the Days Due is greater than 30.<\/p>\n\n\n\n<p>Addition creates OR logic (i.e., any individual condition can be TRUE).<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/07\/FILTER-with-multiple-OR-conditions.png?ezimgfmt=rs:800x323\/rscb2\/ng:webp\/ngcb2\" alt=\"FILTER with multiple OR conditions\" class=\"wp-image-10016\"\/><\/figure>\n\n\n\n<p>The formula in cell G3 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=FILTER(B3:D10,(C3:C10&gt;50)+(D3:D10&gt;30))<\/pre>\n\n\n\n<p>The example above shows where the Invoice Value is greater than 50&nbsp;<strong>or<\/strong>&nbsp;the Days Due is greater than 30.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example 5 \u2013 Using FILTER for dependent dynamic drop-down lists<\/h3>\n\n\n\n<p>Drop-down lists are a data validation technique.&nbsp; Dependent drop-down lists are an advanced technique where the lists change depending on the result of another cell.&nbsp; For example, if the first drop-down list displays country names, the second drop-down list should only display cities that exist in that country.&nbsp; There are currently very tedious methods to achieve this effect, but the new FILTER makes this super easy.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/07\/FILTER-Dependent-drop-down-lists.gif\" alt=\"FILTER - Dependent drop-down lists\" class=\"wp-image-10018\"\/><\/figure>\n\n\n\n<p>The formula in cell H3 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=UNIQUE(B3:B10)<\/pre>\n\n\n\n<p>The&nbsp;UNIQUE&nbsp;function creates a unique list to populate the drop-down in cell F4.<\/p>\n\n\n\n<p>The formula in cell I3 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=FILTER(C3:C10,B3:B10=F4)<\/pre>\n\n\n\n<p>Depending on the value in cell F4, the values returned by the FILTER function change.&nbsp; The second drop-down in cell F6 changes dynamically based on the value in Cell F4.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example 6 \u2013 Using FILTER with other functions<\/h3>\n\n\n\n<p>In this final example, FILTER is nested inside the SORT function.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/07\/FILTER-and-SORT-together.png?ezimgfmt=rs:800x323\/rscb2\/ng:webp\/ngcb2\" alt=\"FILTER and SORT together\" class=\"wp-image-10019\"\/><\/figure>\n\n\n\n<p>The formula in cell F3 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=SORT(FILTER(B3:D10,D3:D10&lt;=30))<\/pre>\n\n\n\n<p>First, the FILTER function returns the cells based on the Days Due being less than or equal to 30.&nbsp; The SORT function then puts the Customers into ascending alphabetical order.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Filtering is a common everyday action for most Excel users.&nbsp; Whether using AutoFilter or a Table, it is a convenient way to view a subset of data quickly.&nbsp; Until the FILTER function came to Excel, there was no easy way to achieve this with formulas.&nbsp; When Microsoft announced the changes to Excel\u2019s&nbsp;calculation engine, they also <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3180\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[661],"class_list":["post-3180","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-filter-in-excel"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3180","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3180"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3180\/revisions"}],"predecessor-version":[{"id":3182,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3180\/revisions\/3182"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}