Filter data in a range or table

The two types of filters

Using AutoFilter, you can create two types of filters: by a list value 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 a list of numbers, or a criteria, but not by both; you can filter by icon or by a custom filter, but not by both.

Reapplying a filter

To determine if a filter is applied, note the icon in the column heading:

• A drop-down arrow means that filtering is enabled but not applied.

  When you hover over the heading of a column with filtering enabled but not applied, a screen tip displays "(Showing All)".

• A Filter button means that a filter is applied.

  When you hover over the heading of a filtered column, a screen tip displays the filter applied to that column, such as "Equals a red cell color" or "Larger than 150".

When you reapply a filter, different results appear for the following reasons:

• Data has been added, modified, or deleted to the range of cells or table column.

• Values returned by a formula have changed and the worksheet has been recalculated.

Do not mix data types

For best results, do not mix data types, 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 data types, the command that is displayed is the data type that occurs the most. For example, if the column contains three values stored as number and four as text, the Text Filters command is displayed .

1. Click a cell in the range or table that you want to filter.

2. On the Data tab, select Filter.

   

3. Select the arrow in the column that contains the content that you want to filter.

4. Under Filter, select Choose One, and then enter your filter criteria.

   

1. Click a cell in the range or table that you want to filter.

2. On the Data tab, select Filter.

   

3. Select the arrow in the column that contains the content that you want to filter.

4. Under Filter, select Choose One, and then enter your filter criteria.

   

5. In the box next to the pop-up menu, enter the number that you want to use.

6. Depending on your choice, you may be offered additional criteria to select:

   

You can quickly filter data based on visual criteria, such as font color, cell color, or icon sets. And you can filter whether you have formatted cells, applied cell styles, or used conditional formatting.

1. In a range of cells or a table column, click a cell that contains the cell color, font color, or icon that you want to filter by.

2. On the Data tab, select Filter.

   

3. Select the arrow in the column that contains the content that you want to filter.

4. Under Filter, in the By color pop-up menu, select Cell Color, Font Color, or Cell Icon, and then click a color.

This option is available only if the column that you want to filter contains a blank cell.

1. Click a cell in the range or table that you want to filter.

2. On the Data toolbar, select Filter.

   

3. Select the arrow in the column that contains the content that you want to filter.

4. In the (Select All) area, scroll down and select the (Blanks) check box.

   Notes: 

   • You can apply filters to only one range of cells on a sheet at a time.

   • When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.

   • Only the first 10,000 unique entries in a list appear in the filter window.

1. Click a cell in the range or table that you want to filter.

2. On the Data tab, select Filter.

   

3. Selectthe arrow in the column that contains the content that you want to filter.

4. Under Filter, select Choose One, and then in the pop-up menu, do one of the following:

   To filter the range for	Click
   Rows that contain specific text	Contains or Equals.
   Rows that do not contain specific text	Does Not Contain or Does Not Equal.

5. In the box next to the pop-up menu, enter the text that you want to use.

6. Depending on your choice, you may be offered additional criteria to select:

   

   To	Click
   Filter the table column or selection so that both criteria must be true	And.
   Filter the table column or selection so that either or both criteria can be true	Or.

1. Click a cell in the range or table that you want to filter.

2. On the Data toolbar, select Filter.

   

3. Select the arrow in the column that contains the content that you want to filter.

4. Under Filter, select Choose One, and then in the pop-up menu, do one of the following:

   To filter for	Click
   The beginning of a line of text	Begins With.
   The end of a line of text	Ends With.
   Cells that contain text but do not begin with letters	Does Not Begin With.
   Cells that contain text but do not end with letters	Does Not End With.

5. In the box next to the pop-up menu, enter the text that you want to use.

6. Depending on your choice, you may be offered additional criteria to select:

   

   To	Click
   Filter the table column or selection so that both criteria must be true	And.
   Filter the table column or selection so that either or both criteria can be true	Or.

Wildcard characters can be used to help you build criteria.

1. Click a cell in the range or table that you want to filter.

2. On the Data toolbar, select Filter.

   

3. Select the arrow in the column that contains the content that you want to filter.

4. Under Filter, select Choose One, and select any option.

5. In the text box, type your criteria and include a wildcard character.

   For example, if you wanted your filter to catch both the word "seat" and "seam", type sea?.

6. Do one of the following:

   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)	A question mark or an asterisk For example, there~? finds "there?"

Do any of the following:

When you filter data, only the data that meets your criteria appears. The data that doesn't meet that criteria is hidden. After you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data.

Table with Top 4 Items filter applied

Filters are additive. This means that each additional filter is based on the current filter and further reduces the subset of data. You can make complex filters by filtering on more than one value, more than one format, or more than one criteria. For example, you can filter on all numbers greater than 5 that are also below average. But some filters (top and bottom ten, above and below average) are based on the original range of cells. For example, when you filter the top ten values, you'll see the top ten values of the whole list, not the top ten values of the subset of the last filter.

In Excel, you can create three kinds of filters: by values, by a format, or by criteria. But each of these filter types is mutually exclusive. For example, you can filter by cell color or by a list of numbers, but not by both. You can filter by icon or by a custom filter, but not by both.

Filters hide extraneous data. In this manner, you can concentrate on just what you want to see. In contrast, when you sort data, the data is rearranged into some order. For more information about sorting, see Sort a list of data.

When you filter, consider the following guidelines:

• Only the first 10,000 unique entries in a list appear in the filter window.

• You can filter by more than one column. When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.

• You can apply filters to only one range of cells on a sheet at a time.