Filters

There are two types of filters in Excel, AutoFilter and Advanced Filter. The one we will be discussing here will be AutoFilter. AutoFilter is the filter people use the most and you can think of it as any sort of filtering that doesn’t use the ‘Advanced Filter’ dialog box.

In order to apply any sort of filter on your data, follow these steps:

1. Click on any cell in a range of data (it does not have to be formatted in a table or even a rectangle)

2. Navigate to the Data pane on the top of the Excel ribbon

3. Click ‘Filter’

Your column headers should look as such:

You are now ready to impose filters on your data.

There are four types of filters within Excel AutoFilter:

1. Number Filters: With Number Filters, Excel provides 10 different rules you can impose to hide rows with data that might not conform to them: 

Greater Than & Greater Than Or Equal To, Less Than & Less Than Or Equal To, Between, Top 10, Above Average & Below Average, and, although not unique to Number Filters, Equal & Does Not Equal.

Ex. You have a data table that records the # of apples sold by location through each month of the year:

Say you want to filter out the Locations where there are < 300 apples sold in December. In order to do so, do as follows:

1. Click on the upside down arrow box next to Dec

2. Go to ‘Number Filters’ and click on ‘Less Than…’

3. Type 300 in the right text box and click OK

The table should look as such:

2. Text Filters: With Text Filters, Excel provides 10 different rules you can impose to hide rows with data that might not conform to them: 

Begins With & Ends With, Contains & Does Not Contain, and like ‘Number Filters’, although not unique to either, Equal & Does Not Equal.

Ex.You have a data table that records the # of apples sold by location through each month of the year but this time there’s an included column describing the community they’re in:

Say you want to filter out the table so that it only includes rows that are either Urban or Suburban/Urban. 

In order to do so, do as follows:

1. Click on the upside down arrow box next to Community

2. Go to ‘Text Filters’ and click on ‘Contains…’

3. Type Urban in the right text box and click OK

The table should look as such:

3. Custom Filters: Custom Rules allow you to create filter combinations while letting you choose how multiple rules should be implemented. In the Custom Filters menu, you’re given the option to choose from the 12 different rule types from Number & Text Filters:

Greater Than & Greater Than Or Equal To, Less Than & Less Than Or Equal To, Between, Top 10, Above Average & Below Average, Begins With & Ends With, Contains & Does Not Contain, and Equal & Does Not Equal.

In Custom Filters, you can choose to have two different filters and implement them so that the data must conform to either (Or) or both (And) rules.

Disclaimer: Text Filters do NOT work on numbers and Number Filters do NOT work on text.

Ex. You have a data table that records the # of apples sold by location through each month of the year again:

Say you want to filter out the table so that you only see locations that sold either over 400 apples or sold “TOO MANY”.

In order to do so, do as follows:

1. Click on the upside down arrow box next to Dec

2. Go to ‘Number Filters’ and click on ‘Custom Filter…’ 

3. Select ‘is greater than’ under the first rule and enter 400 in the right textbox

4. Click ‘Or’

5. Select ‘contains’ under the second rule and enter TOO MANY in the right textbox

6. Click OK

The table should look as such:

4. Color Filters: If you color code your data, either through conditional formatting or just formatting it by yourself, you can use ‘Color Filters’ to filter the data along color lines.

Ex. You have a table of tournament scores from teams that competed in multiple events:

Say you want to see how the team that scored first in Protein Modeling did in the rest of their events.

In order to do so, do as follows:

1. Click on the upside down arrow box next to Protein Modeling

2. Click on ‘Filter by Color’ and select the most vibrant orange

The table should look as such:

I want to remind the reader than when using Custom Filters, rules from Text Filters will not be applicable to numbers and vice versa i.e. applying Contains… 4 to 404 will still remove 404.

I also want to add that, in determining whether to provide a ‘Number Filters’ or ‘Text Filters’, Excel will provide whichever has the most corresponding value rows. For example, if there are 11 rows and 6 of them have numbers in them, Excel will provide the ‘Number Filters’ option. However, if there are an equal number of both, Excel will provide the ‘Text Filters’ option.

That’s pretty much all you have to know for AutoFilter. Stay tuned guys and guyettes, the next entry will cover Advanced Filtering