Filter data in a range or table


Filtered data presents merely the rows which match your defined criteria and hides rows which you don’t want to be shown. When you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data without editing its state.

Filtering can also be done with several columns. Filters are additive, that is, every additional filter is associated with the existing filter and greatly constricts the subset of data.

Note: When you use the Find dialogue box to search filtered data, only the data that is presented is checked; non-displayed data is excluded. To search each element of data, clear all filters.

Learn more about filtering

The two types of filters

AutoFilter enables you to insert two kinds of filters: by a list value or by criteria. All of these filter types is mutually exclusive for every cell range or column table. For instance, 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 indicates that filtering is activated but not used.

Once you hover over the heading of a column with filtering on but not actually used, a screen tip shows “(Showing All)”.

  • A Filter button Applied filter icon symbolises an applied filter.

After you hover over the heading of a filtered column, a screen tip says the filter applied to that column, such as “Equals a red cell colour” or “Larger than 150”.

When you reapply a filter, various results emerge for the specific reasons:

  • Data has been created, altered, or erased to the cell ranges or table column.
  • Values generated by a formula have updated and the worksheet has been recalculated.

Do not mix data types

For optimum results, avoid mixing data types, like text and number, or number and date in the same column, because simply one variant of filter command can be chosen for columns independently. If there is a mix of data types, the command that appears will be the most frequent data type. For example, if the column includes three values retained as number and four as text, the Text Filters command is presented.

Filter data in a table

After you add your data in a table, filtering controls are inserted to the table headers intuitively.

  1. Choose the data you want to filter. On the Home tab, select Format as Table, and then click Format as Table.
Button to format data as a table
  1. In the Create Table dialogue box, you can pick whether your table contains headers.
  • Choose My table has headers to convert the top row of your data into table headers. This row will be excluded from any filters.
  • Don’t click the check box if you want Excel for the web to create placeholder headers (that you can rename) above your table data.
Dialog box for converting data range into a table
  1. Press OK.
  1. To apply a filter, select the arrow in the column header, and choose a filter option.

Filter a range of data

If you seldom formatting your data as a table, another option you have is to use filters to a range of data.

  1. Pick your preferred data to filter. For excellent results, the columns must include headings.
  1. On the Data tab, select Filter.

Filtering options for tables or ranges

You can either set a general Filter option or a custom filter pertinent to the data type. Specifically, when filtering numbers, you’ll notice Number Filters, for dates you’ll view Date Filters, and for text you’ll observe Text Filters. The standard filter option allows you to choose the data you want to check from a list of established data like this:

Custom number filter option

Number Filters enables you to create a custom filter:

the custom filtering options available for number values.

In this example, if you want to highlight the regions that had sales under $6,000 in March, you can set a custom filter:

apply the custm filter for number values

Here’s how:

  1. Press the filter arrow beside March > Number Filters > Less Than and type 6000.
applying a custom filter to show values below a certain criteria
  1. Press OK. Excel for the web defines the filter and displays solely the regions with sales below $6000.
Results of applying a custom number filter

You can set custom Date Filters and Text Filters in a consistent way.

To clear a filter from a column

  • Select the Filter Applied filter icon button adjacent to the column heading, and then press Clear Filter from <“Column Name”>.

To remove all the filters from a table or range

  • Pick any cell within your table or range and, on the Data tab, select the Filter button. This will delete the filters from each column in your table or range and display your entire 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.


Leave a Reply

%d bloggers like this: