Exploring Special Filters
YOU’VE LEARNED HOW TO PERFORM a simple filter on your data, but Excel provides even more ways to filter your data. Use the AutoFilter feature to find missing data in your worksheet, to display a list of the 10 best sales districts, and even to see which invoices are due to be paid next month.
Searching for Blank Cells
Suppose you are the school administrator in this example and you are responsible for accurately maintaining this database of grades. The three teachers submitted their data to you on Friday, but some of the data was missing. You decide to enter the data you have and leave blanks where the data was missing and plan to fill in the missing data on Monday.
You can scroll through the database searching for the blank cells manually, but using Excel’s AutoFilter would be so much easier. Excel can easily find the missing data in your worksheet, regardless of how big your spreadsheet becomes.
1. | Choose Data > Sort & Filter > Filter to turn on the AutoFilter feature. |
2. | Click the filter arrow in the column header where you want to find blank cells. |
3. | Click the Select All option to remove the check marks from all of your data points. |
4. | Scroll to the bottom of the entry list and click the (Blanks) option and click OK. As shown in Figure 8-13, the (Blanks) option will always appear at the end of the entry list. If there are no blanks in the column, there will be no (Blanks) option to click. |
Excel will display only those records with blank cells in the column you selected (see Figure 8-14).
Figure 8-14. Excel found two records with blank entries in the Student ID column.
Tip
To view only the non-blank data, turn on the AutoFilter, make sure that the Select All option is checked and then scroll to the bottom of the entry list and remove the check mark from the (Blanks) option (see Figure 8-15). Finally, click OK.
Figure 8-15. Add a check mark to all filter options except (Blanks) to find non-blank data.
Filtering by Values
Excel’s AutoFilter recognizes your data as either numbers (values) or as text. If the data in your cells is numerical, the AutoFilter will include special filter options called Number Filters (see Figure 8-16).
Figure 8-16. Number filters in Excel 2010.
1. | To create a number filter, click the filter arrow in the column header containing the data you want to filter and click Number Filters. | |||||||||||||||||||||||||||||||||
2. | Select the filter that best meets your needs. Table 8-1 describes the results of these filters on the GPA worksheet.
| |||||||||||||||||||||||||||||||||
3. | Enter your specific criteria in the resulting dialog box (see Figure 8-17) and click OK to apply the filter. Figure 8-17. Each numbered filter can be further customized in a dialog box.
|
Filtering by Date
Excel provides two types of date filters: common and dynamic. Common filters use the same sort of comparison filters as the number filters do (for example, Equal To, Greater Than). Regardless of the date on which you perform the filter, your filtered results will look the same. For instance, if there are three records that are equal to January 10 today, there will be three records equal to January 10 tomorrow (provided no one changes your data).
Dynamic filters factor in today’s date when performing the filter. For example, performing a filter to find all invoices coming due Next Week will produce a different result today than it will if you perform the same filter sometime next month. Dynamic filters include Today, Last Year, Next Month, and even Quarter 1. Figure 8-18 illustrates the full range of date filters available in Excel.
Tip
In determining the order of dates, Excel prioritizes year, then months, then days. January is always the first month of a year and the first month of a quarter.
1. | To create a date filter, click the filter arrow in the column header containing the data you want to filter and click Date Filters. |
2. | Select the filter that best meets your needs. |
3. | Enter your specific criteria in the resulting dialog box and click OK to apply the filter. |
Filtering by TimeUse the date AutoFilters in Excel to filter by time as well. For example, to filter by an earlier time than 8:00 in the morning, choose the Before filter, enter 8:00 AM, and then click OK. |
Filtering by Color
If you are the type of person who likes to color-code your data, Excel has a filter just for you. If your database includes colored cells or colored fonts, the AutoFilter will include an option to filter by color (see Figure 8-19).
