{"id":10434,"date":"2020-11-24T13:00:00","date_gmt":"2020-11-24T12:00:00","guid":{"rendered":"https:\/\/www.xelplus.com\/?p=10434"},"modified":"2023-03-24T09:29:13","modified_gmt":"2023-03-24T08:29:13","slug":"google-sheets-filter-function","status":"publish","type":"post","link":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/","title":{"rendered":"How to Use the Google Sheets FILTER Function"},"content":{"rendered":"\n<p>This post is an excerpt of my online&nbsp;<a href=\"https:\/\/courses.xelplus.com\/p\/google-sheets\" target=\"_blank\" rel=\"noreferrer noopener\">Google Sheets Comprehensive Masterclass<\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/courses.xelplus.com\/p\/google-sheets\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Learn-Google-Sheets-13.png\" alt=\"\" class=\"wp-image-12363 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Learn-Google-Sheets-13.png\" alt=\"\" class=\"wp-image-12363 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<p>This course takes you from a beginner to a Google Sheets expert.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"filter-data-by-criteria\">Filter Data by Criteria<\/h2>\n\n\n\n<p>We are presented with a dataset\u2026<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-01.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-01.png\" alt=\"\" class=\"wp-image-12446 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-01.png\" alt=\"\" class=\"wp-image-12446 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<p>\u2026 and we need to perform the following tasks with requirements:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>create a list of&nbsp;<strong>Apps<\/strong>,&nbsp;<strong>Sales<\/strong>, and&nbsp;<strong>Profit<\/strong><\/li>\n\n\n\n<li>the list is to be restricted to the selected&nbsp;<strong>Region<\/strong>&nbsp;(<em>dropdown selection in cell&nbsp;<strong>H6<\/strong><\/em>)<\/li>\n\n\n\n<li>the dropdown list should be generated automatically using&nbsp;<strong>Data Validation<\/strong><\/li>\n\n\n\n<li>results should be sorted in&nbsp;<strong>Ascending<\/strong>&nbsp;order by&nbsp;<strong>Proft<\/strong>.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-02.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-02.png\" alt=\"\" class=\"wp-image-12447 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-02.png\" alt=\"\" class=\"wp-image-12447 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"creating-the-dropdown-list\">Creating the Dropdown List<\/h2>\n\n\n\n<p>To create the dropdown list by which the user will select a&nbsp;<strong>Region<\/strong>, perform the following steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Select cell&nbsp;<strong>H6<\/strong><\/li>\n\n\n\n<li>Click&nbsp;<strong>Data -&gt; Data Validation<\/strong><\/li>\n\n\n\n<li>In the&nbsp;<strong>Data Validation<\/strong>&nbsp;dialog box, Set the&nbsp;<strong>Criteria<\/strong>&nbsp;to \u201cList from a range\u201d and set the range to&nbsp;<strong>B5:B<\/strong>. Set the Data Validation rule to \u201c<strong>Reject Input<\/strong>\u201d so the user cannot enter any value not found in the dropdown list.<\/li>\n<\/ol>\n\n\n\n<p><em>NOTE: The range reference \u201c<strong>B5:B<\/strong>\u201d is not a typographic error.&nbsp; The row reference for the ending cell is purposely omitted.&nbsp; This allows Sheets to \u201clook\u201d for more items when new data is added to the table.<\/em><\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-03.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-03.png\" alt=\"\" class=\"wp-image-12448 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-03.png\" alt=\"\" class=\"wp-image-12448 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<p>When clicked, cell&nbsp;<strong>H6<\/strong>&nbsp;now presents a dynamically generated list of&nbsp;<strong>Regions<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-04.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-04.png\" alt=\"\" class=\"wp-image-12449 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-04.png\" alt=\"\" class=\"wp-image-12449 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"the-filter-function\">The&nbsp;<strong>FILTER<\/strong>&nbsp;Function<\/h2>\n\n\n\n<p>The&nbsp;<strong>FILTER<\/strong>&nbsp;function is used to produce a table of reduced rows based on an existing table and a set of criteria.<\/p>\n\n\n\n<p>This can be something such as \u201call records that are from the Country \u201cAustria\u201d, or \u201call rows with a Sale greater than 100,000\u201d.<\/p>\n\n\n\n<p>The output of the&nbsp;<strong>FILTER<\/strong>&nbsp;function includes every row from the referenced table that meets the defined criteria.<\/p>\n\n\n\n<p>The syntax for&nbsp;<strong>FILTER<\/strong>&nbsp;is:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-05.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-05.png\" alt=\"\" class=\"wp-image-12450 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-05.png\" alt=\"\" class=\"wp-image-12450 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Range:<\/strong>&nbsp;The Range is the table of data being tested and reduced.<\/li>\n\n\n\n<li><strong>Condition:<\/strong>&nbsp;The Condition is the logical test by which qualifying rows will be retailed while disqualified rows will be ignored.&nbsp; These tests typically refer to a single column in the table followed by some sort of logical test (<em>ex: \u201cequals\u201d, \u201cgreater than\u201d, \u201cless than or equal to\u201d, etc.<\/em>)<\/li>\n<\/ul>\n\n\n\n<p>Multiple condition tests can be executed within a single&nbsp;<strong>FILTER<\/strong>&nbsp;function.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"filter-by-single-criterion-region\">Filter by Single Criterion (<em>Region<\/em>)<\/h2>\n\n\n\n<p>Using the previously presented dataset, we can write the following formula in cell&nbsp;<strong>H8<\/strong>&nbsp;that takes the selected&nbsp;<strong>Region<\/strong>&nbsp;from cell&nbsp;<strong>H6<\/strong>&nbsp;and compares it to the&nbsp;<strong>Region<\/strong>&nbsp;column (<em>column&nbsp;<strong>B<\/strong><\/em>) of the table.&nbsp; If a matching region is discovered, return the data from columns&nbsp;<strong>C-D-E<\/strong>&nbsp;of the corresponding row.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=FILTER(C5:E, B5:B = H6)<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-06.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-06.png\" alt=\"\" class=\"wp-image-12451 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-06.png\" alt=\"\" class=\"wp-image-12451 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<p>The result is as follows:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-07.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-07.png\" alt=\"\" class=\"wp-image-12452 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-07.png\" alt=\"\" class=\"wp-image-12452 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"filter-by-multiple-criteria-region-and-sales\">Filter by Multiple Criteria (Region and Sales)<\/h2>\n\n\n\n<p>If we wished to refine the result to exclude&nbsp;<strong>Apps<\/strong>&nbsp;with zero sales, we can add a second condition to the&nbsp;<strong>FILTER<\/strong>&nbsp;function to include only those&nbsp;<strong>Apps<\/strong>&nbsp;where&nbsp;<strong>Sales<\/strong>&nbsp;is greater than zero.<\/p>\n\n\n\n<p>The updated formula using the second condition would appear as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=FILTER(C5:E, B5:B = H6, D5:D &gt; 0)<\/code><\/pre>\n\n\n\n<p>The result is\u2026<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-08.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-08.png\" alt=\"\" class=\"wp-image-12453 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-08.png\" alt=\"\" class=\"wp-image-12453 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<p><em>NOTE:&nbsp; If you wished to exclude ONLY zero-valued sales while retaining negative-valued sales, the formula would look like this.<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=FILTER(C5:E, B5:B = H6, D5:D &lt;&gt; 0)<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"sort-the-results-by-profit\">Sort the Results by Profit<\/h2>\n\n\n\n<p>To have the results of the&nbsp;<strong>FILTER<\/strong>&nbsp;function sorted from smallest to largest by&nbsp;<strong>Profit<\/strong>, we can wrap the&nbsp;<strong>FILTER<\/strong>&nbsp;function within a&nbsp;<strong>SORT<\/strong>&nbsp;function.<\/p>\n\n\n\n<p>The&nbsp;<strong>SORT<\/strong>&nbsp;function allows us to dynamically sort the provided values in either ascending or descending order, and by which columns and in which order.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-09.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-09.png\" alt=\"\" class=\"wp-image-12454 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-09.png\" alt=\"\" class=\"wp-image-12454 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<p>For our purposes, we want to sort the data by&nbsp;<strong>Profit<\/strong>&nbsp;(<em>3<sup>rd<\/sup>&nbsp;column of the output<\/em>) in&nbsp;<strong>Ascending<\/strong>&nbsp;order.&nbsp; We will use a \u201c3\u201d to indicate the sort should occur on the 3<sup>rd<\/sup>&nbsp;column of the output and a \u201cTRUE\u201d to indicate \u201cAscending\u201d order.&nbsp; Using \u201cFALSE\u201d would invoke a descending sort.<\/p>\n\n\n\n<p>The updated formula would look like so\u2026<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=SORT(FILTER(C5:E, B5:B = H6, D5:D &gt; 0), 3, TRUE)<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-10.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-10.png\" alt=\"\" class=\"wp-image-12455 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-10.png\" alt=\"\" class=\"wp-image-12455 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<p>The updated, sorted result is\u2026<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-11.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-11.png\" alt=\"\" class=\"wp-image-12456 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-11.png\" alt=\"\" class=\"wp-image-12456 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"testing-the-results\">Testing the Results<\/h2>\n\n\n\n<p>To ensure we have a fully dynamic solution, select different&nbsp;<strong>Regions<\/strong>&nbsp;from the dropdown list to see all&nbsp;<strong>Apps<\/strong>&nbsp;that were sold to the selected&nbsp;<strong>Region<\/strong>&nbsp;that are greater than zero and sorted smallest to largest by&nbsp;<strong>Profit<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/FilterTest.gif\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/FilterTest.gif\" alt=\"\" class=\"wp-image-12459 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/FilterTest.gif\" alt=\"\" class=\"wp-image-12459 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<p>You can also add, delete, or edit records to the data source and see the changes automatically in the&nbsp;<strong>FILTER<\/strong>&nbsp;results.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"note-to-excel-365-users\">Note to Excel 365 Users<\/h2>\n\n\n\n<p>For Excel 365 users who wish to use the Excel equivalent of Google Sheets\u2019&nbsp;<strong>FILTER<\/strong>&nbsp;function, be aware that the argument arrangement is different between Sheets and Excel.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-12.png\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-12.png\" alt=\"\" class=\"wp-image-12457 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-12.png\" alt=\"\" class=\"wp-image-12457 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<p>The syntax is mostly identical, but where the&nbsp;<strong>FILTER<\/strong>&nbsp;function in Sheets is for additional criteria, Excel\u2019s&nbsp;<strong>FILTER<\/strong>&nbsp;function has an option for dealing with filters that return no matching records.<\/p>\n\n\n\n<p>It may appear as if the Excel&nbsp;<strong>FILTER<\/strong>&nbsp;function cannot handle more than one criterion, there is a way to have Excel\u2019s&nbsp;<strong>FILTER<\/strong>&nbsp;function handle multiple criteria.&nbsp; To see how this is performed, check out this post that shows both in written and video form how to achieve this behavior.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"excel-365-filter-function\"><a href=\"https:\/\/www.xelplus.com\/excel-filter-function\/\" target=\"_blank\" rel=\"noreferrer noopener\">Excel 365 \u2013 FILTER Function<\/a><\/h2>\n\n\n\n<figure class=\"wp-block-image is-style-gray-boxed\"><a href=\"https:\/\/www.xelplus.com\/excel-filter-function\/\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/filterfunction_blogicon-300x169.jpg\" alt=\"\" class=\"wp-image-12468 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/filterfunction_blogicon-300x169.jpg\" alt=\"\" class=\"wp-image-12468 lazyload\"\/><\/noscript><\/a><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"practice-workbook\">Practice Workbook<\/h3>\n\n\n\n<p>Feel free to Download the Workbook&nbsp;<a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1iDDUc9lKpP9_8o2NSkOuh1_T0tk8btUq07-v5AcXGbM\/copy\" target=\"_blank\" rel=\"noreferrer noopener\">HERE.<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1iDDUc9lKpP9_8o2NSkOuh1_T0tk8btUq07-v5AcXGbM\/copy\"><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" data-src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/GS_downloadButton.png\" alt=\"\" class=\"wp-image-12460 lazyload\"\/><noscript><img decoding=\"async\" src=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/GS_downloadButton.png\" alt=\"\" class=\"wp-image-12460 lazyload\"\/><\/noscript><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to properly use the FILTER Function in Google Sheets. You can use the Filter function to check for one condition or apply it to multiple columns. You can also sort the filtered results by wrapping FILTER inside the SORT function.<\/p>\n","protected":false},"author":3,"featured_media":3450,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"om_disable_all_campaigns":false,"footnotes":""},"categories":[23],"tags":[156,57,380,253,67,108,120,255],"class_list":["post-10434","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets","tag-drop-down","tag-dropdown","tag-dropdown-list","tag-dynamic-arrays","tag-filter-function","tag-google-sheets","tag-sheets","tag-sort-function"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v26.2 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to Use the Google Sheets FILTER Function - Xelplus - Leila Gharani<\/title>\n<meta name=\"description\" content=\"Learn how to properly use the FILTER Function in Google Sheets. You can use the Filter function to check for one condition or apply it to multiple columns. You can also sort the filtered results by wrapping FILTER inside the SORT function.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Use the Google Sheets FILTER Function\" \/>\n<meta property=\"og:description\" content=\"Learn how to properly use the FILTER Function in Google Sheets. You can use the Filter function to check for one condition or apply it to multiple columns. You can also sort the filtered results by wrapping FILTER inside the SORT function.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/\" \/>\n<meta property=\"og:site_name\" content=\"Xelplus - Leila Gharani\" \/>\n<meta property=\"article:published_time\" content=\"2020-11-24T12:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-03-24T08:29:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-13.png\" \/>\n\t<meta property=\"og:image:width\" content=\"700\" \/>\n\t<meta property=\"og:image:height\" content=\"393\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Leila Gharani\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@leilagharani\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Leila Gharani\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/\"},\"author\":{\"name\":\"Leila Gharani\",\"@id\":\"https:\/\/www.xelplus.com\/#\/schema\/person\/d8f19bb3303402013d3281b8979e4618\"},\"headline\":\"How to Use the Google Sheets FILTER Function\",\"datePublished\":\"2020-11-24T12:00:00+00:00\",\"dateModified\":\"2023-03-24T08:29:13+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/\"},\"wordCount\":891,\"publisher\":{\"@id\":\"https:\/\/www.xelplus.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-13.png\",\"keywords\":[\"Drop-down\",\"Dropdown\",\"Dropdown List\",\"Dynamic Arrays\",\"FILTER function\",\"Google Sheets\",\"Sheets\",\"SORT function\"],\"articleSection\":[\"Google Sheets\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/\",\"url\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/\",\"name\":\"How to Use the Google Sheets FILTER Function - Xelplus - Leila Gharani\",\"isPartOf\":{\"@id\":\"https:\/\/www.xelplus.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-13.png\",\"datePublished\":\"2020-11-24T12:00:00+00:00\",\"dateModified\":\"2023-03-24T08:29:13+00:00\",\"description\":\"Learn how to properly use the FILTER Function in Google Sheets. You can use the Filter function to check for one condition or apply it to multiple columns. You can also sort the filtered results by wrapping FILTER inside the SORT function.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#primaryimage\",\"url\":\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-13.png\",\"contentUrl\":\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-13.png\",\"width\":700,\"height\":393},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.xelplus.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Use the Google Sheets FILTER Function\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.xelplus.com\/#website\",\"url\":\"https:\/\/www.xelplus.com\/\",\"name\":\"XelPlus - Leila Gharani\",\"description\":\"Bestselling Excel and Power BI Courses\",\"publisher\":{\"@id\":\"https:\/\/www.xelplus.com\/#organization\"},\"alternateName\":\"XelPlus\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.xelplus.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.xelplus.com\/#organization\",\"name\":\"XelPlus\",\"alternateName\":\"Leila Gharani\",\"url\":\"https:\/\/www.xelplus.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.xelplus.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2019\/12\/XelPlus-favicon-114px.png\",\"contentUrl\":\"https:\/\/www.xelplus.com\/wp-content\/uploads\/2019\/12\/XelPlus-favicon-114px.png\",\"width\":114,\"height\":114,\"caption\":\"XelPlus\"},\"image\":{\"@id\":\"https:\/\/www.xelplus.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.xelplus.com\/#\/schema\/person\/d8f19bb3303402013d3281b8979e4618\",\"name\":\"Leila Gharani\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.xelplus.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/42e34befbcef0b5f8b2bb0e81567af28bbe25f74996b704194aaed8a474589ab?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/42e34befbcef0b5f8b2bb0e81567af28bbe25f74996b704194aaed8a474589ab?s=96&d=mm&r=g\",\"caption\":\"Leila Gharani\"},\"description\":\"I\u2019ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT\u2014managing SAP and Oracle projects. As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI. I love making complex tech topics easy to understand. There\u2019s nothing better than helping someone realize they can do it themselves. I\u2019m always learning new things too and finding better ways to help others succeed.\",\"sameAs\":[\"https:\/\/www.instagram.com\/lgharani\/\",\"https:\/\/www.linkedin.com\/in\/leila-gharani-6b12506\/?ppe=1\",\"https:\/\/x.com\/leilagharani\",\"https:\/\/www.youtube.com\/channel\/UCJtUOos_MwJa_Ewii-R3cJA\"],\"url\":\"https:\/\/www.xelplus.com\/author\/xelplus\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to Use the Google Sheets FILTER Function - Xelplus - Leila Gharani","description":"Learn how to properly use the FILTER Function in Google Sheets. You can use the Filter function to check for one condition or apply it to multiple columns. You can also sort the filtered results by wrapping FILTER inside the SORT function.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/","og_locale":"en_US","og_type":"article","og_title":"How to Use the Google Sheets FILTER Function","og_description":"Learn how to properly use the FILTER Function in Google Sheets. You can use the Filter function to check for one condition or apply it to multiple columns. You can also sort the filtered results by wrapping FILTER inside the SORT function.","og_url":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/","og_site_name":"Xelplus - Leila Gharani","article_published_time":"2020-11-24T12:00:00+00:00","article_modified_time":"2023-03-24T08:29:13+00:00","og_image":[{"width":700,"height":393,"url":"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-13.png","type":"image\/png"}],"author":"Leila Gharani","twitter_card":"summary_large_image","twitter_creator":"@leilagharani","twitter_misc":{"Written by":"Leila Gharani","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#article","isPartOf":{"@id":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/"},"author":{"name":"Leila Gharani","@id":"https:\/\/www.xelplus.com\/#\/schema\/person\/d8f19bb3303402013d3281b8979e4618"},"headline":"How to Use the Google Sheets FILTER Function","datePublished":"2020-11-24T12:00:00+00:00","dateModified":"2023-03-24T08:29:13+00:00","mainEntityOfPage":{"@id":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/"},"wordCount":891,"publisher":{"@id":"https:\/\/www.xelplus.com\/#organization"},"image":{"@id":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#primaryimage"},"thumbnailUrl":"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-13.png","keywords":["Drop-down","Dropdown","Dropdown List","Dynamic Arrays","FILTER function","Google Sheets","Sheets","SORT function"],"articleSection":["Google Sheets"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/","url":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/","name":"How to Use the Google Sheets FILTER Function - Xelplus - Leila Gharani","isPartOf":{"@id":"https:\/\/www.xelplus.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#primaryimage"},"image":{"@id":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#primaryimage"},"thumbnailUrl":"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-13.png","datePublished":"2020-11-24T12:00:00+00:00","dateModified":"2023-03-24T08:29:13+00:00","description":"Learn how to properly use the FILTER Function in Google Sheets. You can use the Filter function to check for one condition or apply it to multiple columns. You can also sort the filtered results by wrapping FILTER inside the SORT function.","breadcrumb":{"@id":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.xelplus.com\/google-sheets-filter-function\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#primaryimage","url":"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-13.png","contentUrl":"https:\/\/www.xelplus.com\/wp-content\/uploads\/2020\/11\/Google-Sheets-Filter-13.png","width":700,"height":393},{"@type":"BreadcrumbList","@id":"https:\/\/www.xelplus.com\/google-sheets-filter-function\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.xelplus.com\/"},{"@type":"ListItem","position":2,"name":"How to Use the Google Sheets FILTER Function"}]},{"@type":"WebSite","@id":"https:\/\/www.xelplus.com\/#website","url":"https:\/\/www.xelplus.com\/","name":"XelPlus - Leila Gharani","description":"Bestselling Excel and Power BI Courses","publisher":{"@id":"https:\/\/www.xelplus.com\/#organization"},"alternateName":"XelPlus","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.xelplus.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.xelplus.com\/#organization","name":"XelPlus","alternateName":"Leila Gharani","url":"https:\/\/www.xelplus.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.xelplus.com\/#\/schema\/logo\/image\/","url":"https:\/\/www.xelplus.com\/wp-content\/uploads\/2019\/12\/XelPlus-favicon-114px.png","contentUrl":"https:\/\/www.xelplus.com\/wp-content\/uploads\/2019\/12\/XelPlus-favicon-114px.png","width":114,"height":114,"caption":"XelPlus"},"image":{"@id":"https:\/\/www.xelplus.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.xelplus.com\/#\/schema\/person\/d8f19bb3303402013d3281b8979e4618","name":"Leila Gharani","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.xelplus.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/42e34befbcef0b5f8b2bb0e81567af28bbe25f74996b704194aaed8a474589ab?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/42e34befbcef0b5f8b2bb0e81567af28bbe25f74996b704194aaed8a474589ab?s=96&d=mm&r=g","caption":"Leila Gharani"},"description":"I\u2019ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT\u2014managing SAP and Oracle projects. As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI. I love making complex tech topics easy to understand. There\u2019s nothing better than helping someone realize they can do it themselves. I\u2019m always learning new things too and finding better ways to help others succeed.","sameAs":["https:\/\/www.instagram.com\/lgharani\/","https:\/\/www.linkedin.com\/in\/leila-gharani-6b12506\/?ppe=1","https:\/\/x.com\/leilagharani","https:\/\/www.youtube.com\/channel\/UCJtUOos_MwJa_Ewii-R3cJA"],"url":"https:\/\/www.xelplus.com\/author\/xelplus\/"}]}},"_links":{"self":[{"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/posts\/10434","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/comments?post=10434"}],"version-history":[{"count":5,"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/posts\/10434\/revisions"}],"predecessor-version":[{"id":16741,"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/posts\/10434\/revisions\/16741"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/media\/3450"}],"wp:attachment":[{"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/media?parent=10434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/categories?post=10434"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.xelplus.com\/wp-json\/wp\/v2\/tags?post=10434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}