Microsoft Excel Data Analysis (Part 2): Filter Data

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.

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

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.

5. Click OK.

Result. Excel displays only sales in Port Harcourt.

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.

8. Click OK.

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

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

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

Number Filter

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

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.

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

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.

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. 

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.

4. Enter ?a* and click OK.

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

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:

Date Filter

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

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. 

3. Click OK.

Result. Excel displays only the sales in January 2014.

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.

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

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.