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:

21 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 […]

  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.

Add a Comment

Your email address will not be published.