Tables

Filter data in a range or table

Filter data in a range or table

Using AutoFilter or built-in comparison operators such as "greater than" and “top 10” in Excel can show the data you want and hide the rest. Once you have filtered data in a range of cells or table, you can either reapply a filter to get up-to-date results, or clear a filter to redisplay all of the data.

Use filters to temporarily hide some of the data in a table, so you can focus on the data you want to see.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Filter a range of data

  1. Select any cell within the range.

  2. Select Data > Filter.

    Filter button
  3. Select the column header arrow Filter arrow .

  4. Select Text Filters or Number Filters, and then select a comparison, like Between.

    Number Filters Between
  5. Enter the filter criteria and select OK.

    Custom AutoFilter dialog box

Filter data in a table

When you put your data in a table, filter controls are automatically added to the table headers.

An Excel table showing built-in filters
  1. Select the column header arrow Filter drop-down arrow for the column you want to filter.

  2. Uncheck (Select All)  and select the boxes you want to show.

    Filter Gallery
  3. Click OK.

    The column header arrow Filter drop-down arrow changes to a Applied filter icon Filter  icon. Select this icon to change or clear the filter.

Related Topics

Excel Training: Filter data in a table

Guidelines and examples for sorting and filtering data by color

Filter data in a PivotTable

Filter by using advanced criteria

Remove a filter

Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want displayed. 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.

Note: When you use the Find dialog box to search filtered data, only the data that is displayed is searched; data that is not displayed is not searched. To search all the data, clear all filters.

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 Filter 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 Applied filter icon 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 .

Filter data in a table

When you put your data in a table, filtering controls are added to the table headers automatically.

  1. Select the data you want to filter. On the Home tab, click Format as Table, and then pick Format as Table.

    Button to format data as a table

  2. In the Create Table dialog box, you can choose whether your table has headers.

    • Select My table has headers to turn the top row of your data into table headers. The data in this row won't be filtered.

    • Don't select the check box if you want Excel Online to add placeholder headers (that you can rename) above your table data.

      Dialog box for converting data range into a table

  3. Click OK.

  4. To apply a filter, click the arrow in the column header, and pick a filter option.

Filter a range of data

If you don't want to format your data as a table, you can also apply filters to a range of data.

  1. Select the data you want to filter. For best results, the columns should have headings.

  2. On the Data tab, choose Filter.

Filtering options for tables or ranges

You can either apply a general Filter option or a custom filter specific to the data type. For example, when filtering numbers, you’ll see Number Filters, for dates you'll see Date Filters, and for text you'll see Text Filters. The general filter option lets you select the data you want to see from a list of existing data like this:

Custom number filter option

Number Filters lets you apply a custom filter:

the custom filtering options available for number values.

In this example, if you want to see the regions that had sales below $6,000 in March, you can apply a custom filter:

apply the custm filter for number values

Here’s how:

  1. Click the filter arrow next to March > Number Filters > Less Than and enter 6000.

    applying a custom filter to show values below a certain criteria

  2. Click OK.

    Excel Online applies the filter and shows only the regions with sales below $6000.

    Results of applying a custom number filter

You can apply custom Date Filters and Text Filters in a similar manner.

To clear a filter from a column

  • Click the Filter Applied filter icon button next to the column heading, and then click Clear Filter from <"Column Name">.

To remove all the filters from a table or range

  • Select any cell inside your table or range and, on the Data tab, click the Filter button.

    This will remove the filters from all the columns in your table or range and show all your data.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×