{"id":6486,"date":"2023-06-21T15:14:51","date_gmt":"2023-06-21T15:14:51","guid":{"rendered":"https:\/\/www.stl-training.co.uk\/b\/?p=6486"},"modified":"2023-12-30T22:30:05","modified_gmt":"2023-12-30T22:30:05","slug":"be-more-productive-with-excels-filter-function-1-of-2","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/be-more-productive-with-excels-filter-function-1-of-2\/","title":{"rendered":"Be More Productive With Excel\u2019s FILTER function (1 of 4)"},"content":{"rendered":"

How often have you found when you filter Excel data<\/a>, problems arise in getting it to work? This can happen when you are sharing workbooks or the source data changes frequently. The solution lies in Office 365\u2019s FILTER function. This blog is part 1 of a series that explores the amazing functionality of some of the most popular Office 365 Excel functions starting with the FILTER function.<\/p>\n

\"\"<\/p>\n

What is the FILTER function<\/strong><\/h2>\n

The FILTER function is based on the standard way of filtering in Excel. It works by extracting the filtered rows from the source data and populating these rows to another sheet. As the filtering process does not affect the source data, we can share workbooks more efficiently. If you have ever struggled using the VLOOKUP to populate specific data into other sheets think again. Why not use the FILTER function instead? It does the same job but, unlike the VLOOKUP, it can return multiple \u2018filtered\u2019 rows based on an initial search. Plus, it’s easier to use. So, what\u2019s not to like!<\/p>\n

 <\/p>\n

How is the FILTER function different from other functions?<\/strong><\/h2>\n

The FILTER function is one of several Office 365 functions that behaves differently to all other Excel functions. With the FILTER function, the results automatically \u2018spill\u2019 into all available cells below. In contrast, all other functions require you to copy the result down manually.<\/p>\n

How to apply the FILTER function<\/strong><\/h2>\n

Let\u2019s take some financial data where we will need to filter all records relating to \u2018Rent\u2019 (see below)<\/p>\n

\"filter\"<\/p>\n

    \n
  1. Copy the source data headings into another sheet and select the cell below the first heading \u2013 see below:<\/li>\n<\/ol>\n

    \"\"<\/p>\n

    \"filter\"<\/p>\n

      \n
    • The 1st<\/sup> part is the \u2018ARRAY\u2019 or range of source data to be selected<\/li>\n
    • The 2nd<\/sup> part is the criteria TO INCUDE in the filter<\/li>\n
    • The 3rd<\/sup> part (\u2018IF EMPTY\u2019) is optional and returns an alternate answer if the filter criteria in part 2 does not find a \u2018match\u2019 e.g. if there are no records for \u2018Rent\u2019 then \u2018not found\u2019 is returned in the cell \u2013 if this 3rd<\/sup> part was not put in and the criteria was IT equipment, i.e.. not in the list, this would produce an error \u2013 see below:<\/li>\n<\/ul>\n

      \"function\"<\/p>\n

      By inserting the 3rd<\/sup> part \u201cnot found\u201d, the above error will be converted to this text<\/p>\n

      As \u2018Rent\u2019 is in the list in column A, we get the following filtered results:<\/p>\n

      \"\"<\/p>\n

      One problem that could arise is if there are not enough free rows to populate the results. If any data is \u2018blocking\u2019 this space, then you will get a #SPILL error:<\/p>\n

      \"filter\"<\/p>\n

      To remove the spill error, simply delete the data that is blocking the spill. Other Office 365 functions such as UNIQUE and SORT also have this \u2018spill\u2019 feature.<\/p>\n

      Conclusion<\/strong><\/h2>\n

      The Excel FILTER function is a great alternative to the VLOOKUP as it is easier to use and can return multiple results based on an initial search. Consequently, the function can help you to improve your efficiency and productivity when you are handling large sets of data.<\/p>\n

       <\/p>\n","protected":false},"excerpt":{"rendered":"

      How often have you found when you filter Excel data, problems arise in getting it to work? This can happen when you are sharing workbooks or the source data changes frequently. The solution lies in Office 365\u2019s FILTER function. This blog is part 1 of a series that explores the amazing functionality of some of […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[637,4,9],"tags":[67,684],"class_list":["post-6486","post","type-post","status-publish","format-standard","hentry","category-application","category-excel-training","category-microsoft-office","tag-autofilter-and-sort-in-excel","tag-filter"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6486","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=6486"}],"version-history":[{"count":2,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6486\/revisions"}],"predecessor-version":[{"id":6527,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6486\/revisions\/6527"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=6486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=6486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=6486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}