How To Filter Rows In Excel

Use AutoFilter or built-in comparison operators similar "greater than" and "top 10" in Excel to bear witness the information you want and hibernate the residual. In one case you filter data in a range of cells or tabular array, yous tin can either reapply a filter to get up-to-appointment results, or clear a filter to redisplay all of the data.

Apply filters to temporarily hide some of the data in a table, so you tin can focus on the information y'all want to see.

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

Filter a range of data

Select any cell inside the range.
Select Data > Filter.
Select the column header arrow .
Select Text Filters or Number Filters, and so select a comparison, like Betwixt.
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 tabular array headers.

Select the cavalcade header arrow for the column you lot want to filter.
Uncheck(Select All) and select the boxes you desire to show.
ClickOK.

The cavalcade header arrow changes to a Filter icon. Select this icon to alter or clear the filter.

Filter data in a table

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

Select the data you want to filter. On the Home tab, click Format as Table, and then pick Format as Table.
In the Create Tabular array dialog box, you can choose whether your table has headers.
- Select My table has headers to turn the tiptop row of your information into table headers. The data in this row won't be filtered.
- Don't select the check box if you lot want Excel for the web to add placeholder headers (that y'all can rename) above your table data.
Click OK .
To apply a filter, click the pointer in the cavalcade header, and option a filter choice.

Filter a range of data

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

Select the data you want to filter. For all-time results, the columns should take headings.
On the Data tab, choose Filter.

Filtering options for tables or ranges

You can either utilise a general Filter option or a custom filter specific to the data blazon. For example, when filtering numbers, y'all'll see Number Filters, for dates you'll see Date Filters, and for text you'll run into Text Filters. The general filter option lets y'all select the data y'all desire to see from a list of existing data similar this:

Custom number filter option

Number Filters lets you use a custom filter:

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

apply the custm filter for number values

Here'southward how:

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

Excel for the spider web applies the filter and shows simply the regions with sales below $6000.

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

To clear a filter from a cavalcade

Click the Filter button side by side to the column heading, and so click Articulate Filter from <"Column Name">.

To remove all the filters from a tabular array or range

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

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

Click a jail cell in the range or table that you lot desire to filter.
On the Data tab, click Filter.
Click the arrow in the column that contains the content that yous want to filter.
Under Filter, click Choose 1, and and then enter your filter criteria.

Notes:

You can utilize filters to only one range of cells on a sail at a time.
When yous apply a filter to a column, the simply filters available for other columns are the values visible in the currently filtered range.
Only the kickoff ten,000 unique entries in a list appear in the filter window.

Click a cell in the range or table that you want to filter.
On the Data tab, click Filter.
Click the arrow in the cavalcade that contains the content that yous want to filter.
Under Filter, click Choose I, and then enter your filter criteria.
In the box next to the pop-up menu, enter the number that you want to use.
Depending on your option, you lot may exist offered additional criteria to select:

Notes:

You tin can apply filters to merely 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 commencement 10,000 unique entries in a list appear in the filter window.
Instead of filtering, yous can use conditional formatting to brand the superlative or bottom numbers stand up out clearly in your information.

Yous tin can rapidly filter data based on visual criteria, such as font color, jail cell color, or icon sets. And you can filter whether you take formatted cells, practical cell styles, or used conditional formatting.

In a range of cells or a table column, click a cell that contains the jail cell color, font colour, or icon that you want to filter by.
On the Data tab, click Filter .
Click the pointer in the column that contains the content that you desire to filter.
Nether Filter, in the Past color pop-up bill of fare, select Cell Color, Font Color, or Cell Icon, and and so click a colour.

This option is bachelor but if the column that you lot want to filter contains a blank jail cell.

Click a cell in the range or tabular array that you desire to filter.
On the Information toolbar, click Filter.
Click the arrow in the column that contains the content that you want to filter.
In the (Select All) area, curlicue downward and select the (Blanks) check box.
Notes:
- You tin can use filters to simply one range of cells on a sheet at a time.
- When you apply a filter to a column, the simply filters bachelor for other columns are the values visible in the currently filtered range.
- Simply the showtime x,000 unique entries in a list announced in the filter window.

Click a cell in the range or table that yous want to filter.
On the Data tab, click Filter .
Click the arrow in the column that contains the content that you want to filter.

Under Filter, click Choose One, and then in the pop-upwards menu, practise ane of the post-obit:

To filter the range for	Click
Rows that contain specific text	Contains or Equals.
Rows that do non incorporate specific text	Does Not Incorporate or Does Non Equal.

In the box next to the pop-upward card, enter the text that you want to use.

Depending on your choice, you may exist offered additional criteria to select:

In the Filter box, select And or Or to add more criteria

To	Click
Filter the tabular array cavalcade or choice so that both criteria must exist truthful	And.
Filter the table column or selection so that either or both criteria can exist true	Or.

Click a cell in the range or table that you lot want to filter.
On the Data toolbar, click Filter .
Click the arrow in the column that contains the content that you lot desire to filter.

Nether Filter, click Choose 1, so in the pop-up menu, do one of the post-obit:

To filter for	Click
The start 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 Brainstorm With.
Cells that contain text but practice non end with messages	Does Not Cease With.

In the box next to the popular-upwardly carte, enter the text that you want to use.

Depending on your option, you may be offered additional criteria to select:

In the Filter box, select And or Or to add more criteria

To	Click
Filter the table cavalcade or selection and then that both criteria must be truthful	And.
Filter the table column or selection and then that either or both criteria tin can be truthful	Or.

Wildcard characters can be used to help you build criteria.

Click a cell in the range or table that you want to filter.
On the Data toolbar, click Filter.
Click the arrow in the cavalcade that contains the content that you want to filter.
Under Filter, click Cull One, and select any option.
In the text box, type your criteria and include a wildcard character.

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

Do i of the post-obit:

Use	To find
? (question mark)	Any single character For example, sm?thursday finds "smith" and "smyth"
* (asterisk)	Any number of characters For example, *east finds "Northeast" and "Southeast"
~ (tilde)	A question mark or an asterisk For case, there~? finds "there?"

Use

To find

? (question mark)

Any single character

For example, sm?thursday finds "smith" and "smyth"

* (asterisk)

Any number of characters

For example, *east finds "Northeast" and "Southeast"

~ (tilde)

A question mark or an asterisk

For case, there~? finds "there?"

Do any of the post-obit:

To	Practise this
Remove specific filter criteria for a filter	Click the arrow in a column that includes a filter, and so click Clear Filter.
Remove all filters that are applied to a range or table	Select the columns of the range or table that have filters applied, and then on the Data tab, click Filter.
Remove filter arrows from or reapply filter arrows to a range or table	Select the columns of the range or tabular array that accept filters applied, and then on the Data tab, click Filter.

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

Table with Top four Items filter applied

Filter of top four values

Filters are condiment. This means that each additional filter is based on the electric current filter and farther reduces the subset of data. Yous can make complex filters by filtering on more than than one value, more than than i format, or more i criteria. For example, yous can filter on all numbers greater than 5 that are too below average. Simply some filters (superlative and bottom ten, in a higher place and below average) are based on the original range of cells. For example, when you filter the meridian x values, yous'll see the meridian 10 values of the whole list, not the summit ten values of the subset of the last filter.

In Excel, y'all tin 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, just not by both. You lot can filter by icon or by a custom filter, but not by both.

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

When you lot filter, consider the following guidelines:

Only the first 10,000 unique entries in a listing appear in the filter window.
You can filter by more than than one cavalcade. When y'all utilize a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.
Yous can apply filters to only one range of cells on a sail at a time.

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

Need more assist?

You can always ask an expert in the Excel Tech Community or become support in the Answers community.