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.

No comments

Powered by Blogger.