Excel: Filtering a range of data

Filter a range of data for windows

Use AutoFilter or built-in comparison operators like “greater than” and “top 10” in Excel to show the data you want and hide the rest. Once you filter 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.

1. Select any cell within the range.
2. Select Data > Filter.
3. Select the column header arrow .
4. Select Text Filters or Number Filters, and then select a comparison, like Between.
5. Enter the filter criteria and select OK.

Filter data in a table

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

1. Select the column header arrow  for the column you want to filter.
2. Uncheck (Select All)  and select the boxes you want to show.
3. Click OK.The column header arrow  changes to a Filter  icon. Select this icon to change or clear the filter.

Filter a range of data for Excel web

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.

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.
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 for the web to add placeholder headers (that you can rename) above your table data.
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:

Number Filters lets you apply a custom filter:

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

Here’s how:

1. Click the filter arrow next to March > Number Filters > Less Than and enter 6000.
2. Click OK.Excel for the web applies the filter and shows only the regions with sales below $6000.

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

To clear a filter from a column

• Click the Filter  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.

Filter a range of data for MAC

1. Click a cell in the range or table that you want to filter.
2. On the Data tab, click Filter.
3. Click the arrow  in the column that contains the content that you want to filter.
4. Under Filter, click 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:

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.
• Instead of filtering, you can use conditional formatting to make the top or bottom numbers stand out clearly in your data.