How Can You Filter Through Tables Inside Of Tables?

Use AutoFilter or built-in comparison operators like "greater than" and "top 10" in Excel to show the data you desire and hide the remainder. Once yous filter information in a range of cells or table, you can either reapply a filter to get upward-to-date results, or clear a filter to redisplay all of the information.

Use filters to temporarily hide some of the data in a table, and so you lot tin focus on the information you lot 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 pointer .
Select Text Filters or Number Filters, then select a comparison, like Between.
Enter the filter criteria and select OK.

Filter data in a tabular array

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

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

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

Filter data in a tabular array

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

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

Filter a range of data

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

Select the data you want to filter. For best results, the columns should have headings.
On the Data tab, choose Filter.

Filtering options for tables or ranges

You tin can either apply a general Filter selection or a custom filter specific to the data blazon. For case, when filtering numbers, you'll meet Number Filters, for dates you lot'll meet Appointment Filters, and for text y'all'll see Text Filters. The general filter option lets yous select the data you want to run into from a listing of existing data similar this:

Custom number filter option

Number Filters lets you apply a custom filter:

In this example, if you want to run into the regions that had sales below $six,000 in March, you tin apply a custom filter:

apply the custm filter for number values

Hither'south how:

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

Excel for the spider web applies the filter and shows only the regions with sales beneath $6000.

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

To clear a filter from a column

Click the Filter button adjacent to the column heading, so click Articulate Filter from <"Cavalcade Proper noun">.

To remove all the filters from a tabular array or range

Select any 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 bear witness all your data.

Click a cell in the range or table that you want to filter.
On the Data tab, click Filter.
Click the pointer in the cavalcade that contains the content that you want to filter.
Nether Filter, click Cull One, and then enter your filter criteria.

Notes:

You can apply filters to merely one range of cells on a sail at a fourth dimension.
When you apply a filter to a cavalcade, the merely filters available for other columns are the values visible in the currently filtered range.
Only the outset 10,000 unique entries in a list appear in the filter window.

Click a jail cell in the range or table that you 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 enter your filter criteria.
In the box adjacent to the popular-up menu, enter the number that you want to use.
Depending on your selection, y'all may be offered additional criteria to select:

Notes:

You lot tin apply filters to only one range of cells on a sheet 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 starting time 10,000 unique entries in a listing appear in the filter window.
Instead of filtering, you tin use provisional formatting to make the peak or bottom numbers stand out clearly in your data.

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

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

This pick is available simply if the column that yous want to filter contains a blank jail cell.

Click a cell in the range or table that you want to filter.
On the Data toolbar, click Filter.
Click the pointer in the column that contains the content that you want to filter.
In the (Select All) expanse, scroll downwards and select the (Blanks) cheque box.
Notes:
- You can apply filters to merely ane range of cells on a canvas at a time.
- When you use a filter to a column, the just filters available for other columns are the values visible in the currently filtered range.
- Only the first 10,000 unique entries in a list announced in the filter window.

Click a cell in the range or table that you lot want to filter.
On the Data tab, click Filter .
Click the pointer in the cavalcade that contains the content that y'all want to filter.

Under Filter, click Cull Ane, and and then in the pop-upwardly menu, do i 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 Non Contain or Does Non Equal.

In the box next to the pop-up bill of fare, enter the text that you lot desire to use.

Depending on your choice, y'all 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 pick so that both criteria must be true	And.
Filter the table column or selection so that either or both criteria can be truthful	Or.

Click a cell in the range or table that you desire 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 I, 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 comprise text but do not brainstorm with letters	Does Not Brainstorm With.
Cells that contain text but do not end with letters	Does Not End With.

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

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

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

To	Click
Filter the table column or choice and then that both criteria must be truthful	And.
Filter the table column or selection so that either or both criteria can exist true	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 Information toolbar, click Filter.
Click the arrow in the column that contains the content that you want to filter.
Under Filter, click Choose One, and select any choice.
In the text box, blazon your criteria and include a wildcard character.

For example, if you wanted your filter to take hold of both the give-and-take "seat" and "seam", type sea?.

Practise one of the post-obit:

Apply	To find
? (question mark)	Whatever single grapheme For instance, sm?th finds "smith" and "smyth"
* (asterisk)	Any number of characters For example, *east finds "Northeast" and "Southeast"
~ (tilde)	A question marking or an asterisk For example, there~? finds "in that location?"

Apply

To find

? (question mark)

Whatever single grapheme

For instance, sm?th finds "smith" and "smyth"

* (asterisk)

Any number of characters

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

~ (tilde)

A question marking or an asterisk

For example, there~? finds "in that location?"

Do any of the post-obit:

To	Do this
Remove specific filter criteria for a filter	Click the arrow in a column that includes a filter, and then click Articulate 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 have filters applied, and and then on the Data tab, click Filter.

When y'all filter information, only the data that meets your criteria appears. The information that doesn't meet that criteria is hidden. Subsequently you filter data, you can re-create, find, edit, format, chart, and print the subset of filtered information.

Table with Top iv Items filter practical

Filter of top four values

Filters are additive. This means that each additional filter is based on the current filter and farther reduces the subset of data. You can brand circuitous filters by filtering on more than one value, more than i format, or more than one criteria. For example, you tin filter on all numbers greater than 5 that are also below boilerplate. Just some filters (height and bottom ten, above and below boilerplate) 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 summit ten values of the subset of the concluding filter.

In Excel, yous can create iii kinds of filters: past values, by a format, or by criteria. But each of these filter types is mutually sectional. For example, you tin filter by prison cell color or by a listing of numbers, but not by both. You can filter by icon or past a custom filter, but not by both.

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

When you filter, consider the following guidelines:

Only the commencement x,000 unique entries in a listing announced in the filter window.
You lot can filter by more one column. When you lot apply a filter to a column, the just filters bachelor for other columns are the values visible in the currently filtered range.
Yous can use filters to only one range of cells on a canvass at a time.

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

Need more than help?

Yous tin always ask an good in the Excel Tech Customs or go support in the Answers community.