{"id":538791,"date":"2023-01-10T20:00:00","date_gmt":"2023-01-10T17:00:00","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-use-the-filter-function-in-microsoft-excel\/"},"modified":"2023-01-10T20:00:00","modified_gmt":"2023-01-10T17:00:00","slug":"how-to-use-the-filter-function-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-filter-function-in-microsoft-excel\/","title":{"rendered":"#How to Use the FILTER Function in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a2dae908aee4\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a2dae908aee4\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-filter-function-in-microsoft-excel\/#%E2%80%9CHow_to_Use_the_FILTER_Function_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Use the FILTER Function in Microsoft Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-filter-function-in-microsoft-excel\/#What_Is_the_FILTER_Function_in_Excel\" >What Is the FILTER Function in Excel?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-filter-function-in-microsoft-excel\/#How_to_Create_a_Basic_Filter_Formula\" >How to Create a Basic Filter Formula<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-filter-function-in-microsoft-excel\/#Filter_Using_Multiple_Criteria_in_the_FILTER_Function\" >Filter Using Multiple Criteria in the FILTER Function<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-filter-function-in-microsoft-excel\/#How_to_Sort_Your_Filtered_Data_in_Excel\" >How to Sort Your Filtered Data in Excel<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Use_the_FILTER_Function_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Use the FILTER Function in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-no-defer=\"\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background\" width=\"1200\" height=\"675\"\/><\/p>\n<div class=\"keytakeaways\">\n<p style=\"font-weight: 400;\">To use the FILTER function, enter simply enter the array and range for your criteria. To avoid an Excel error for empty filter results, use the third optional argument to display a custom indicator.<\/p>\n<\/div>\n<p>Microsoft Excel offers a built-in filter feature along with the option to use an advanced filter. But if you want to filter by multiple criteria and even sort the results, check out the FILTER function in Excel.<\/p>\n<p>Using the FILTER function, you can use operators for \u201cand\u201d and \u201cor\u201d to combine criteria. As a bonus, we\u2019ll show you how to <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>ly the SORT function to the formula to display your results in ascending or descending order by a particular column.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"What_Is_the_FILTER_Function_in_Excel\"><\/span>What Is the FILTER Function in Excel?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The syntax for the formula is <code>FILTER(array, range=criteria, if_empty)<\/code> where only the first two arguments are required. You can use a cell reference, number, or text in quotes for the criteria, depending on your data.<\/p>\n<p>Use the third optional argument if your data set may return an empty result since it\u2019ll display the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/813794\/how-to-fix-common-formula-errors-in-microsoft-excel\/\">#CALC! error by default. To replace the error message, you can include text, a letter, or number in quotes or simply leave the quotes empty for a blank cell.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_to_Create_a_Basic_Filter_Formula\"><\/span>How to Create a Basic Filter Formula<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To get started, we\u2019ll start with a basic filter so that you can <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/761846\/how-to-find-the-function-you-need-in-microsoft-excel\/\">see how the function works. In each screenshot, you\u2019ll see our filter results on the right.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Find the Function You Need in Microsoft Excel<\/em><\/strong><\/p>\n<p>For filtering the data in cells A2 through D13 using the content of cell B2 (Electronics) as criteria, here\u2019s the formula:<\/p>\n<pre>=FILTER(A2:D13,B2:B13=B2)<\/pre>\n<p>To break down the formula, you see the <code>array<\/code> argument is A2:D13 and the <code>range=criteria<\/code> argument is B2:B13=B2. This returns all results containing Electronics.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-855042\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/12\/BasicCellReference-ExcelFILTERFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FILTER function basic formula in Excel\" width=\"650\" height=\"326\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Another way to write the formula is by entering the contents of cell B2 in quotation marks as follows:<\/p>\n<pre>=FILTER(A2:D13,B2:B13=\"Electronics\")<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-855041\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/12\/BasicText-ExcelFILTERFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FILTER function formula using text criteria\" width=\"650\" height=\"327\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can also use criteria from another cell to filter the data in the <code>range=criteria<\/code> area. Here, we\u2019ll use the data in cell B15.<\/p>\n<pre>=FILTER(A2:D13,B2:B13=B15)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-855045 size-full\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/12\/BasicOtherCellReference-ExcelFILTERFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FILTER function formula using a separate cell as criteria\" width=\"650\" height=\"367\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If your data contains a number, you can use this as the criteria without quotation marks. In this example, we\u2019ll use the same cell range, but filter by cells D2 through D13 looking for 10.<\/p>\n<pre>=FILTER(A2:D13,D2:D13=10)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-855044\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/12\/BasicNumber-ExcelFILTERFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FILTER function formula using number criteria\" width=\"650\" height=\"326\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you aren\u2019t receiving any results for your formula or are seeing the #CALC! error, you can use the third argument <code>if_empty<\/code>. For instance, we\u2019ll display None if the result is blank.<\/p>\n<pre>=FILTER(A2:D13,D2:D13=75,\"None\")<\/pre>\n<p>As you can see, the <code>range=criteria<\/code> data doesn\u2019t include 75, therefore, our result is None.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-855043\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/12\/BasicNoResults-ExcelFILTERFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FILTER function formula with no results\" width=\"650\" height=\"370\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Filter_Using_Multiple_Criteria_in_the_FILTER_Function\"><\/span>Filter Using Multiple Criteria in the FILTER Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>An advantage of the FILTER function in Excel is that you can filter by multiple criteria. You\u2019ll include an operator for <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/401998\/how-to-use-logical-functions-in-excel-if-and-or-xor-not\/\">AND (*) or OR (+).<\/p>\n<p>For example, we\u2019ll filter our data set by both A3 (West) and B2 (Electronics) using an asterisk (*) with this formula:<\/p>\n<pre>=FILTER(A2:D13,(A2:A13=A3)*(B2:B13=B2))<\/pre>\n<p>As you can see, we have one result that includes both West and Electronics.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-855046\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/12\/MultipleCriteriaAND-ExcelFILTERFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FILTER function formula for multiple criteria using AND\" width=\"650\" height=\"326\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To use the other operator, we\u2019ll filter for either A3 or B2 using a plus sign (+) as follows:<\/p>\n<pre>=FILTER(A2:D13,(A2:A13=A3)+(B2:B13=B2))<\/pre>\n<p>Now, you can see that our results contain five records with West or Electronics.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-855047\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/12\/MultipleCriteriaOR-ExcelFILTERFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FILTER function formula for multiple criteria using OR\" width=\"650\" height=\"326\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_to_Sort_Your_Filtered_Data_in_Excel\"><\/span>How to Sort Your Filtered Data in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you want to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/847610\/ways-to-sort-data-in-microsoft-excel\/\">sort the results you receive from the FILTER function, you can add the SORT function to the formula. This is simply an alternative to using the Sort feature on the Data tab, but doesn\u2019t require you to reposition your data.<\/p>\n<p>For more information on the SORT function before you try it out, take a look at our how-to for full details.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Use the Microsoft Excel SORT Function<\/em><\/strong><\/p>\n<p>Here, we\u2019ll use our basic filter from the beginning of this tutorial: <code>FILTER(A2:D13,B2:B13=B2)<\/code>. Then, we\u2019ll add SORT with its arguments to sort by the fourth column (Loss) in descending order (-1):<\/p>\n<pre>=SORT(FILTER(A2:D13,B2:B13=B2),4,-1)<\/pre>\n<p>To break down this formula, we have our FILTER formula as the <code>array<\/code> argument for the SORT function. After that, we have <code>4<\/code> to sort by the fourth column in the data set and <code>-1<\/code> to display the results in descending order.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-855049\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/12\/SortDescending-ExcelFILTERFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FILTER function with SORT function in descending order\" width=\"650\" height=\"319\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To display the results in ascending order instead, replace the <code>-1<\/code> with <code>1<\/code>:<\/p>\n<pre>=SORT(FILTER(A2:D13,B2:B13=B2),4,1)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-855048\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/12\/SortAscending-ExcelFILTERFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FILTER function with SORT function in ascending order\" width=\"650\" height=\"319\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Excel\u2019s built-in filter is great for quickly seeing specific records in a data set. And the advanced filter works well for filtering by a criteria range in place or another location. But for using multiple criteria and sorting at the same time, take the FILTER function for a spin.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>13 Essential Excel Functions for Data Entry<\/em><\/strong><\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/855033\/filter-function-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Use the FILTER Function in Microsoft Excel&#8221; To use the FILTER function, enter simply enter the array and range for your criteria. To avoid an Excel error for empty filter results, use the third optional argument to display a custom indicator. Microsoft Excel offers a built-in filter feature along with the option to&#8230;<\/p>\n","protected":false},"author":1,"featured_media":538792,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-538791","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/538791","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=538791"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/538791\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/538792"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=538791"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=538791"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=538791"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}