The Filter Function in Data Analytics: Examples and Use Cases

The Filter Function in Data Analytics: Examples and Use Cases

The Filter function is a newly introduced function in Microsoft Excel 365. It is not available in earlier versions of excel. In earlier versions, Filtering of large data sets was done manually. With the new Filter function, filtering of data can now be done dynamically. This function is very useful for data cleaning as well as analysis.

Filter function can be used to filter a range of data based on defined criteria(s).

Filter function syntax

The filter function has 3 arguments.

=Filter(Array,Include,[if_empty])

The first 2 arguments are compulsory. The third one is optional.

Array is the range we want to filter.

Include is the column or row range to be filtered. We also define the criteria based on which the data has to be filtered.

If Empty is the data to be shown in case the filtered data is not found in the data set.

Let us understand the filter function with the help of a few examples.

Click to learn top 10 financial functions in Excel

Example 1 – Filtering customer names by region

Here is a list of customers by regions and revenues during a particular period. We want to filter the data by regions.

To filter the data by region. Type

=Filter(B5:D14,C5:C14=”India”)

B5:B14 is the array

C5:C14 is the column range for which data has to be filtered. We can filter the data by entering the name of the country in Inverted commas. For Example, “India” as mentioned above.

The third argument is optional. We can leave it blank.

When we press enter. We get the data filtered for India.

We can also use the data validation feature in excel to select the country for which we want to filter the data. In that case, instead of typing the name of the country, we can link the column to the data validation list.

Read more how can you do data automation using excel

 

Example 2 – Filtering customers by revenues

We have the same data set used in our previous example. We want to filter the customers whose revenues is greater than $5,000/-

To filter the name of customers with $5,000+ revenues. Type

=Filter(B5:D14,D5:D14>5000)

B5:B14 is the array

D5:D14 is the column range for which data has to be filtered. We can filter the data by entering the criteria. For Example, >5000 as mentioned above.

The third argument is optional. We can leave it blank.

Press Enter

You get the name of the customers with Revenues greater than $5,000.

Example 3 – Filtering data with multiple conditions

In the given data set, we want to filter the name of customers in US with revenues less than $5,000.

There are two conditions here.

  • The customer is in US
  • Revenues are less than $5,000

To filter the data based on these two criteria’s, Type

=Filter(B5:D14,(C5:C14=“US”)*(D5:D14<5000))

B5:B14 is the array

The second input is divided into two parts.

  • (C5:C14=“US”) to filter the data for customers in US.
  • (D5:D14<5000) to filter the customers in US with revenues less than $5,000.
  • To join the two conditions we have the asterisk (*) sign in between.

The third argument is optional. We can leave it blank.

Press Enter.

You get the filtered data.

There are two customers in US with revenues less than $5,000.

Get to know How to Transpose Data in Excel

Example 4 – Sorting and Filtering customers by revenues

In this example we will use the Sort Function and Filter function together.

We want to filter the data for customers whose revenues is greater than $5,000/-. The data needs to be arranged in alphabetical order.

Like we did in Example 2, to filter the data, Type

=Filter(B5:D14,D5:D14>5000)

B5:B14 is the array

D5:D14 is the column range for which data has to be filtered. We can filter the data by entering the criteria. For Example, >5000 as mentioned above.

The third argument is optional. We can leave it blank.

When we press enter, we get the filtered data.

Now to sort the data in alphabetical order, we will use the sort function.

Type

=Sort(Filter(B5:D14,D5:D14>5000))

Sort function has other arguments as well, but since we have to get this data in alphabetical order, we don’t need any further inputs.

Press Enter.

 

We get the list of customers whose revenue is greater than $5,000/- sorted in alphabetical order.

This is how we can use the Filter function. It can be used for cleaning/ analysing large data sets.

Learn data analysis with Pivot Tables

Learn Excel Shortcuts

Learn XLookup

Let us know in case you have any questions, by posting in the comment section below.

Happy learning.

Read more about this on our LinkedIn page below:

Share This Post:

57 thoughts on “The Filter Function in Data Analytics: Examples and Use Cases”

  1. […] the following example, we have a dataset with monthly sales data from 2012-2015. Excel filter function is useful if you want to see the total sales for the year only, or if you want to know how many […]

  2. Best Dart Drinking Game

    The Filter Function in Data Analytics: Examples and Use Cases – skillfine

  3. regrade yard cost

    The Filter Function in Data Analytics: Examples and Use Cases – skillfine

  4. atn viper night vision goggle

    The Filter Function in Data Analytics: Examples and Use Cases – skillfine

  5. dry or wet cappuccino

    The Filter Function in Data Analytics: Examples and Use Cases – skillfine

  1. 太平洋在线代理 January 22, 2024

    wow, awesome blog article.Really thank you! Awesome.

  2. R编程代做 January 24, 2024

    Wow, great article post.Much thanks again. Great.

  3. Face swap January 24, 2024

    Appreciate you sharing, great article post. Want more.

  4. home theater furniture January 25, 2024

    Thanks-a-mundo for the article post.Much thanks again. Fantastic.

  5. litter box enclosure January 29, 2024

    wow, awesome blog post.Thanks Again. Fantastic.

  6. residential property chicholi nagpur January 30, 2024

    Very informative blog. Cool.

  7. new technology news today January 31, 2024

    I loved your article. Will read on…

  8. home interior stores near me January 31, 2024

    Im thankful for the blog post.Really looking forward to read more. Keep writing.

  9. emirates visa online February 1, 2024

    I really like and appreciate your article.Really thank you! Really Cool.

  10. student visa australia February 2, 2024

    I appreciate you sharing this article post.

  11. ai meetings February 2, 2024

    Awesome post.Thanks Again. Fantastic.

  12. Fortune Tiger February 3, 2024

    Really informative article post.Really looking forward to read more. Much obliged.

  13. hydrofoil board February 5, 2024

    Major thanks for the article.Thanks Again. Keep writing.

  14. エロ ai February 6, 2024

    Appreciate you sharing, great article.Much thanks again. Will read on…

  15. leather bow dog collar February 7, 2024

    A round of applause for your post.Thanks Again. Cool.

  16. budget hotel in Nainital mall road February 8, 2024

    I really like and appreciate your blog.Really looking forward to read more. Really Cool.

  17. I really liked your article.

  18. DIY Handcraft February 21, 2024

    I really like and appreciate your article post.Really looking forward to read more.

  19. Automatic packing line February 24, 2024

    I think this is a real great blog article.Much thanks again. Much obliged.

  20. Capping machine February 25, 2024

    Thanks so much for the blog article.Really thank you! Keep writing.

  21. Very informative post.Much thanks again. Really Great.

  22. quartz calacatta February 29, 2024

    Awesome post.Really looking forward to read more. Will read on…

  23. 意外懷孕點算 February 29, 2024

    Hey, thanks for the blog post. Want more.

  24. casino March 1, 2024

    Thanks again for the blog.Really thank you! Will read on…

  25. nsfw ai March 2, 2024

    Really appreciate you sharing this article.Really looking forward to read more.

  26. janitor ai chat March 2, 2024

    I am so grateful for your article post.Really looking forward to read more. Really Great.

  27. Roleplay AI March 2, 2024

    Muchos Gracias for your post.Thanks Again. Much obliged.

  28. gpt online free March 3, 2024

    I value the blog.Really looking forward to read more.

  29. face swap AI March 3, 2024

    Looking forward to reading more. Great blog.Really thank you! Cool.

  30. Additionally, personalized learning platforms powered by artificial intelligence tailor educational content to individual student needs.

  31. Fantastic blog post. Great.

  32. bonitocase March 7, 2024

    I cannot thank you enough for the post. Really Cool.

  33. 澳洲学生签证 March 8, 2024

    I value the article post.Thanks Again. Really Great.

  34. wow, awesome article.Really thank you!

  35. kobold ai March 12, 2024

    Really enjoyed this article.Really looking forward to read more. Really Cool.

  36. ai girlfriend chat March 13, 2024

    Really informative article post.Really looking forward to read more. Will read on…

  37. kobold ai March 13, 2024

    wow, awesome article.Much thanks again. Cool.

  38. nsfw ai chat March 14, 2024

    Thanks-a-mundo for the post.Much thanks again. Much obliged.

  39. ai sexting March 14, 2024

    Really appreciate you sharing this article.Much thanks again. Keep writing.

  40. 約個工 March 15, 2024

    Appreciate you sharing, great blog.Thanks Again. Want more.

  41. 電擊獸進化條件 March 15, 2024

    Major thankies for the blog article.Really looking forward to read more. Want more.

  42. Temporary Fencing March 16, 2024

    Enjoyed every bit of your article post.Much thanks again. Awesome.

  43. temporary fencing March 17, 2024

    Very good article post.Really looking forward to read more. Awesome.

  44. wire mesh fence March 18, 2024

    Thanks-a-mundo for the post.Really looking forward to read more. Cool.

  45. FM WhatsApp download March 18, 2024

    I value the post.Really looking forward to read more. Cool.

  46. GB WhatsApp March 19, 2024

    I really enjoy the article.Thanks Again. Really Great.

  47. Download GB Whatsapp March 19, 2024

    Thank you for your article.Thanks Again. Great.

  48. devin ai March 20, 2024

    I really like and appreciate your blog article.Much thanks again. Awesome.

  49. Human hair wig store March 20, 2024

    Thanks-a-mundo for the blog. Really Cool.

  50. Cosplay wig for sale March 21, 2024

    Thanks so much for the blog article. Really Cool.

  51. top 10 longest six in cricket history March 24, 2024

    Im obliged for the post.Thanks Again. Much obliged.

  52. anchor fastener manufacturer March 26, 2024

    Really enjoyed this blog.Really thank you! Keep writing.

Add a Comment

Your email address will not be published.