Microsoft Excel Data Analysis (Part 2): Filter Data

Excel Data Filter

We shall continue our Microsoft Excel Data Analysis lesson by looking at Excel Data Filter.

In Excel, you can use the Filter feature to display only the records that meet certain criteria.

You can apply Number Filter, Text Filter and Date Filter. 

There’s also Advanced Filter, which we shall cover as a topic on its own in the nearest future


Excel Data Filter:



1. Click any single cell inside your Excel data set.

2. On the Data tab, click Filter.


Excel Data Filter

Result: Arrows will appear in the column headers, as follows:


Excel Data Filter

3. Click the arrow next to City (for instance).

4. Click on Select All to clear all the check boxes, and click the check box next to Port Harcourt.


Excel Data Filter

5. Click OK.

Result. Excel displays only sales in Port Harcourt.


Excel Data Filter

6. Click the arrow next to Month.

7. Click on Select All to clear all the check boxes, and click the check box next to Feb.


Excel Data Filter


8. Click OK.

Result. Excel displays only the sales in Port Harcourt in the month of Feb.


Excel Data Filter


9. To remove the Filter, go to Data tab, and click Clear. 


Excel Data Filter


10. To completely remove the filter and arrows, go to Data tab, and click Filter.


Excel Data Filter

Number Filter


You can apply number filter to a column with numeric data, like sales column below:


Excel Data Filter


Example: to apply a number filter to see all the sales below 20,000, follow these steps:

1. Click the arrow next to Sales column.

2. Click Number Filters (this option is available because the Sales column contains numeric data) and select Less Than from the list.


Excel Data Filter


3. In the Custom AutoFilter box, Enter 20000 and click OK.


Excel Data Filter


Result. Excel only displays the records where Sales is less than 20,000; and you can easily see the city and months where those sales were made.


Excel Data Filter


Note: you can also display records equal to a value, greater than a value, between two values, the top x records, records that are above average, etc… Just about anything you want.


Text Filter



Text Filter is used on text data like Sales Person or City column. 


Excel Data Filter


To apply Text Filter on Sales Person data, follow below steps:

1. Click the arrow next to Sales Person.

3. Click Text Filters (this option is available because the Sales Person column contains text data) and select Equals from the list.


Excel Data Filter


4. Enter ?a* and click OK.


Excel Data Filter


The question mark (?) represents any single character. The asterisk (*) represents any series of characters. 

So, ?a* means words with single character before letter “a” and series of characters after letter “a”. In other words, words with letter "a" as second character.

Result. Excel only displays the records where the second character of Sales Person is “a” i.e. Maria

Excel Data Filter


Note: you can also display records that begin with a specific character, end with a specific character, contain or do not contain a specific character, etc… See below:

Excel Data Filter


Date Filter


You can use date filter to filter a column with dates, like the Date column below:

Excel Data Filter


To apply date filter:

1. Click the arrow next to Date.

2. Click on Select All to clear all the check boxes, click the + sign next to 2014, and click the check box next to January. 

Excel Data Filter



3. Click OK.

Result. Excel displays only the sales in January 2014.

Excel Data Filter


4. Click the arrow next to Date.

5. Click on Select All to select all the check boxes.

6. Click Date Filters (this option is available because the Date column contains dates) and select Last Month from the list.

Excel Data Filter



Result. Excel only displays the sales of last month (September 2016).

Excel Data Filter


Note: many date filters depend on today’s date

Please post your questions and comments below. Remember to subscribe or follow me on social media to keep updated with the lesson series.

8 comments:

  1. It can help with longer-term atmosphere deciding to help ranchers' arrangement for a considerable length of time with bigger creepy crawlies masses or dry years. Data Analytics Course in Bangalore

    ReplyDelete
  2. Thanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for. what is data analysis

    ReplyDelete
  3. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    360DigiTMG business analytics certification
    data science courses
    data analytics courses

    ReplyDelete
  4. Hey, thanks for this great article I really like this post and I love your blog and also Check aws training in hyderabad at 360DIGITMG.
    360Digitmg aws training in hyderabad

    ReplyDelete
  5. Just the way I have expected. Your website really is interesting.
    learn360digitmg artificial intelligence course

    ReplyDelete
  6. Super site! I am Loving it!! Will return once more, Im taking your food likewise, Thanks.
    360digitmg artificial intelligence course

    ReplyDelete
  7. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
    data science institute in hyderabad
    data analytics training in hyderabad
    business analytics course in hyderabad

    ReplyDelete

Powered by Blogger.