Filtering Data in Microsoft Excel

Filter Data

Using AutoFilter to filter data is a quick and easy way to find and work with a subset of data in a range of cells or table column. Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want displayed. Criteria are conditions you specify to limit which records are included in the result set of a query or filter. After you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data without rearranging or moving it. You can also filter by more than one column. Filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data.

Using AutoFilter, you can create three types of filters: by a list values, by a format, or by criteria. Each of these filter types is mutually exclusive for each range of cells or column table. For example, you can filter by cell colour or by a list of numbers, but not by both; you can filter by icon or by a custom filter, but not by both.

Important.

For best results, do not mix storage formats, such as text and number or number and date, in the same column because only one type of filter command is available for each column. If there is a mix of storage formats, the command that is displayed is the storage format that occurs the most. For example, if the column contains three values stored as number and four as text, the filter command that is displayed is Text Filters.

Filtering Text

Range of cells

1. Select a range of cells containing alphanumeric data.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Filter buttons appear beside each column heading as shown below:

3. Click the arrow in the column header and do one of the following:
To select from a list of text values
1. In the list of text values, select or clear one or more text values to filter by.
2. The list of text values can be up to 10,000. If the list is large, clear Select All at the top, and then select the specific text values to filter by.
To create criteria
1. Point to Text Filters and then click one of the comparison operator such as =, >, <, >=, <=, and <> commands, or click Custom Filter.
2. For example, to filter by text that begins with a specific character, select Begins With, or to filter by text that has specific characters anywhere in the text, select Contains.
3. In the Custom AutoFilter dialog box, in the box on the right, enter text or select the text value from the list.

For example, to filter by text that begins with the letter J, enter "J" or to filter by text that has "bell" anywhere in the text, enter bell.

If you need to find text that shares some characters but not others, use a wildcard character. `Wild characters and their usage are shown in the table below:

Use	To find
? (question mark)	Any single character. For example, sm?th finds "smith" and "smyth".
* (asterisk)	Any number of characters. For example, *east finds "Northeast" and "Southeast".
~ (tilde) followed by ?, *, or ~	A question mark, asterisk, or tilde. For example, fy06~? finds "fy06?".

4. Optionally, filter by one more criteria by doing one of the following:
1. To filter the table column or selection so that both criteria must be true, select And.
2. To filter the table column or selection so that either or both criteria can be true, select Or.
3. In the second entry, select a comparison operator, and then in the box on the right, enter text or select a text value from the list.
5. Click Ok.

Filtering Numbers

Range of cells

1. Select a range of cells containing numeric data.
2. Repeat step 3, 3a and 3b above.
Create criteria
1. Point to Number Filters and then click one of the comparison operator commands or click Custom Filter.
For example, to filter by a lower and upper number limit, select Between.
2. In the Custom AutoFilter dialog box, in the box or boxes on the right, enter numbers or select numbers from the list.
For example, to filter by a lower number of 25 and an upper number of 50, enter 25 and 50.
3. Optionally, filter by one more criteria by repeating step four above.

Filtering Dates or Times

Range of cells

1. Select a range of cells containing numeric data.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.
3. Click the arrow in the column header.
Select from a list of dates or times
1. In the list of dates or times, select or clear one or more dates or times to filter by.
2. By default, all dates in the range of cells or table column are grouped by a hierarchy of years, months, and days. Selecting or clearing a higher level in the hierarchy selects or clears all nested dates below that level. For example, if you select 2006, months are listed below 2006, and days are listed below each month.
Create criteria
3. Point to Date Filters and then do one of the following:
For a common filter based on a comparison operator;
4. Click one of the comparison operator commands (Equals, Before, After, or Between) or click Custom Filter.
5. In the Custom AutoFilter dialog box, in the box on the right, enter a date or time, select a date or time from the list, or click the Calendar button to find and enter a date. For example, to filter by a lower and upper date or time, select Between.
6. For example, to filter by an earlier date of "3/6/2007" and a later date of "6/12/2007", enter 3/6/2007 and 6/12/2007. Dynamic filter where the criteria can change when you reapply the filter;
7. Click one of the pre-defined date commands. For example, to filter all dates by the current date, select Today, or by the following month, select Next Month.
4. Click Ok.

Filtering for Top or Bottom Numbers

Range of cells

1. Select a range of cells containing numeric data.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.
3. Click the arrow in the column header.
4. Point to Number Filters and then select Top 10 and in the Top 10 AutoFilter dialog box, do the following:
1. In the box on the left, click Top or Bottom.
2. In the box in the middle, enter a number between 1 and 500.
3. In the box on the right, do one of the following:
1. To filter by number, click Items.
2. To filter by percentage, click Percent.
5. Click Ok.

Filtering by Selection

You can quickly filter data with criteria that is equal to the contents of the active cell.

1. In a range of cells or table column, right click a cell containing the value, colour, font colour, or icon you want to filter by.
2. Click Filter, and then do one of the following:
1. To filter by text, number, or date or time, click Filter by Selected Cell's Value.
2. To filter by cell colour, click Filter by Selected Cell's Color.
3. To filter by font colour, click Filter by Selected Cell's Font Color.
4. To filter by icon, click Filter by Selected Cell's Icon.