{"id":4954,"date":"2025-11-14T10:18:00","date_gmt":"2025-11-14T04:48:00","guid":{"rendered":"https:\/\/toolswift.com\/blog\/?p=4954"},"modified":"2025-11-14T10:18:00","modified_gmt":"2025-11-14T04:48:00","slug":"how-to-use-filter-function-in-google-sheets","status":"publish","type":"post","link":"https:\/\/toolswift.com\/blog\/how-to-use-filter-function-in-google-sheets\/","title":{"rendered":"How to Use FILTER Function in Google Sheets"},"content":{"rendered":"<p>Working with large sets of data can be overwhelming, but Google Sheets offers tools that make it much easier. One of the most useful tools is the FILTER function. Instead of scrolling through rows or adjusting built-in filter buttons, this function instantly shows only the information you need based on the conditions you set. It creates a clean, dynamic view of your data without changing your original table.<\/p>\n<h2>What the FILTER Function Does<\/h2>\n<p>At its core, the FILTER function checks your data range and returns only the rows that match your conditions. It doesn\u2019t delete or hide anything\u2014your original data stays untouched. Instead, it simply displays a new, filtered version wherever you place the formula.<\/p>\n<p>Here\u2019s the basic structure:<\/p>\n<p><code>=FILTER(range, condition1, [condition2, ...])<\/code><\/p>\n<ul>\n<li>range: The data you want to filter<\/li>\n<li>condition1, condition2, \u2026: Rules that decide what stays and what gets removed<\/li>\n<\/ul>\n<p>Each condition must line up with the rows or columns you\u2019re filtering. You can add as many conditions as you want, but they must all follow the same direction (all row-based or all column-based).<\/p>\n<h2>Filtering With Simple Conditions<\/h2>\n<h3>1. Filter by a Single Condition<\/h3>\n<p>If you want to see only rows that contain a certain text, number, or date, you can use one condition.<\/p>\n<p>Example: Show only rows where column A says \u201cVegetables\u201d:<\/p>\n<p><code>=FILTER(A2:D10, A2:A10=\"Vegetables\")<\/code><\/p>\n<p>This instantly pulls out all the matching rows.<\/p>\n<h3>2. Filter With Two Conditions (AND condition)<\/h3>\n<p>If you want rows that meet more than one requirement, simply add more conditions.<\/p>\n<p>Example: Show only \u201cVegetables\u201d and calories greater than 20:<\/p>\n<p><code>=FILTER(A2:D10, A2:A10=\"Vegetables\", C2:C10&gt;20)<\/code><\/p>\n<p>All conditions must be true.<\/p>\n<h3>3. Filter With Multiple Conditions<\/h3>\n<p>You can continue adding conditions to narrow your results further.<\/p>\n<p>Example: Vegetables, calories &gt; 20, and fat &lt; 0.3:<\/p>\n<p><code>=FILTER(A2:D10, A2:A10=\"Vegetables\", C2:C10&gt;20, D2:D10&lt;0.3)<\/code><\/p>\n<h3>4. Filter Using OR Logic<\/h3>\n<p>If you want results that meet any one of several conditions, you can add them using a + (plus sign).<\/p>\n<p>Example: Vegetables OR items with fat under 0.3:<\/p>\n<p><code>=FILTER(A2:D10, (A2:A10=\"Vegetables\") + (D2:D10&lt;0.3))<\/code><\/p>\n<p>When any condition is true, the row is included.<\/p>\n<h2>Filtering Numbers, Dates &amp; Text<\/h2>\n<h3>1. Filter Largest Values<\/h3>\n<p>To find the highest values (like top 3 results):<\/p>\n<p><code>=FILTER(A2:D10, C2:C10 &gt;= LARGE(C2:C10, 3))<\/code><\/p>\n<p>This pulls all rows with values in the top three.<\/p>\n<h3>2. Sort Filtered Results<\/h3>\n<p>If you want your filtered output sorted at the same time:<\/p>\n<p><code>=SORT(FILTER(A2:D10, C2:C10&gt;=LARGE(C2:C10,3)), 3, FALSE)<\/code><\/p>\n<p>Here, column 3 is sorted in descending order.<\/p>\n<h3>3. Filter by Exact Text Match<\/h3>\n<p>To get rows where a column matches a specific word:<\/p>\n<p><code>=FILTER(A2:E15, E2:E15=\"Late\")<\/code><\/p>\n<h3>4. Filter by Text That Contains Certain Letters<\/h3>\n<p>For partial text matches, use SEARCH or FIND.<\/p>\n<p>Example: Find rows where column A contains \u201cCA\u201d starting from the 8th character:<\/p>\n<p><code>=FILTER(A2:E15, SEARCH(\"CA\", A2:A15, 8))<\/code><\/p>\n<h3>5. Filter by Date<\/h3>\n<p>To filter by an exact date:<\/p>\n<p><code>=FILTER(A2:E15, C2:C15 = DATE(2020, 1, 9))<\/code><\/p>\n<p>Find all dates in January:<\/p>\n<p><code>=FILTER(A2:E15, MONTH(C2:C15)=1)<\/code><\/p>\n<p>Filter rows after a specific date:<\/p>\n<p><code>=FILTER(A2:E15, C2:C15 &gt;= DATE(2020,1,1))<\/code><\/p>\n<h3>6. Filter by Time<\/h3>\n<p>Example: Times after 2 PM:<\/p>\n<p><code>=FILTER(A2:B10, A2:A10 &gt; TIME(14,0,0))<\/code><\/p>\n<h2>Filtering With Multiple Columns<\/h2>\n<p>You can combine conditions across several columns.<\/p>\n<p>Example:<\/p>\n<ul>\n<li>Amount between 200 and 400<\/li>\n<li>Arriving in January<\/li>\n<li>Status = \u201con the way\u201d<\/li>\n<\/ul>\n<p><code>=FILTER(A2:E15, B2:B15&gt;=200, B2:B15&lt;=400, MONTH(C2:C15)=1, E2:E15=\"on the way\")<\/code><\/p>\n<h2>Filtering Across Sheets or Files<\/h2>\n<h3>1. Filter From Another Sheet<\/h3>\n<p>Just include the sheet name:<\/p>\n<p><code>=FILTER(Orders!A2:E15, Orders!C2:C15=\"Late\")<\/code><\/p>\n<p>If the sheet name has spaces:<\/p>\n<p><code>=FILTER('May Orders'!A2:E15, 'May Orders'!C2:C15=\"Late\")<\/code><\/p>\n<h3>2. Filter From Another Google Sheets File<\/h3>\n<p>Use IMPORTRANGE.<\/p>\n<p><code>=FILTER(IMPORTRANGE(\"FILE_ID\",\"Orders!A2:E15\"), IMPORTRANGE(\"FILE_ID\",\"Orders!E2:E15\")=\"Late\")<\/code><\/p>\n<p>After running it, click Allow Access when prompted.<\/p>\n<h2>Combining FILTER With Other Functions<\/h2>\n<h3>1. FILTER + SORT<\/h3>\n<p>Sort filtered data:<\/p>\n<p><code>=SORT(FILTER(B3:E17, B3:B17=\"John Doe\"), 4, FALSE)<\/code><\/p>\n<h3>2. FILTER + UNIQUE<\/h3>\n<p>Remove duplicate names:<\/p>\n<p><code>=UNIQUE(FILTER(B3:B17, B3:B17&lt;&gt;\"\"))<\/code><\/p>\n<h3>3. FILTER + SUM<\/h3>\n<p>Sum values for rows matching a condition:<\/p>\n<p><code>=SUM(FILTER(E3:E17, B3:B17=\"John Doe\"))<\/code><\/p>\n<h3>4. FILTER + LARGE<\/h3>\n<p>Largest value for certain customer:<\/p>\n<p><code>=LARGE(FILTER(E3:E17, B3:B17=\"John Doe\"), 1)<\/code><\/p>\n<p>Top 3 values:<\/p>\n<p><code>=FILTER(B3:E17, E3:E17 &gt;= LARGE(E3:E17, 3))<\/code><\/p>\n<h2>Common FILTER Issues (And Easy Fixes)<\/h2>\n<h3>1. \u201cNo matches found\u201d (#N\/A)<\/h3>\n<p>Happens when no row meets your conditions.<\/p>\n<p>Fix with a friendly message:<\/p>\n<p><code>=IFERROR(FILTER(A2:E15, E2:E15=\"Arrived\"), \"No matches found\")<\/code><\/p>\n<h3>2. Mismatched Range Sizes<\/h3>\n<p>All ranges in your conditions must have the same height\/width as the main range.<\/p>\n<h3>3. #REF! Error<\/h3>\n<p>This appears when filtered results try to fill cells that already contain data.<br \/>\nClear the output area.<\/p>\n<h3>4. Can\u2019t Edit Filtered Output<\/h3>\n<p>FILTER results are dynamic and uneditable.<br \/>\nIf you need to edit them:<\/p>\n<ul>\n<li>Copy \u2192 Paste as values<\/li>\n<li>Or change your FILTER conditions<\/li>\n<\/ul>\n<h2>Practical Tips<\/h2>\n<ul>\n<li>Keep your data clean (no merged cells, consistent formatting).<\/li>\n<li>Use cell references to avoid hardcoding conditions.<\/li>\n<li>Leave enough empty space where the results will appear.<\/li>\n<li>Try combining FILTER with other formulas to build deeper insights.<\/li>\n<\/ul>\n<h2>Conclusion<\/h2>\n<p>The FILTER function is one of the most flexible tools in Google Sheets. Once you understand the basics\u2014range, conditions, and how to combine them\u2014you can build powerful custom filters for any kind of data. From simple text checks to complex multi-column rules, FILTER helps you extract the exact information you need without touching the original sheet.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Working with large sets of data can be overwhelming, but Google Sheets offers tools that make it much easier. One of the most useful&hellip;<\/p>\n","protected":false},"author":1,"featured_media":4955,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-4954","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-how-to"],"_links":{"self":[{"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/posts\/4954","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/comments?post=4954"}],"version-history":[{"count":1,"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/posts\/4954\/revisions"}],"predecessor-version":[{"id":4956,"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/posts\/4954\/revisions\/4956"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/media\/4955"}],"wp:attachment":[{"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/media?parent=4954"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/categories?post=4954"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/toolswift.com\/blog\/wp-json\/wp\/v2\/tags?post=4954"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}