Which One Of The Following Filter Options Is Not Found On The Text Filter Submenu?

Lesson twenty: Filtering Data

/en/excel2016/sorting-data/content/

Introduction

If your worksheet contains a lot of content, it can be difficult to observe information apace. Filters can be used to narrow downwardly the data in your worksheet, allowing you lot to view simply the information you need.

Optional: Download our practice workbook.

Watch the video below to learn more than about filtering data in Excel.

To filter data:

In our example, we'll apply a filter to an equipment log worksheet to display just the laptops and projectors that are available for checkout.

In gild for filtering to work correctly, your worksheet should include a header row, which is used to place the name of each column. In our example, our worksheet is organized into unlike columns identified past the header cells in row 1: ID#, Blazon, Equipment Detail, and so on.
Select the Data tab, then click the Filter command.
A driblet-down pointer will announced in the header jail cell for each column.
Click the drib-down arrow for the cavalcade yous want to filter. In our example, we will filter column B to view only certain types of equipment.
The Filter menu will announced.
Uncheck the box next to Select All to chop-chop deselect all data.
Check the boxes next to the information you want to filter, then click OK. In this case, we will check Laptop and Projector to view just these types of equipment.
The data will exist filtered, temporarily hiding whatever content that doesn't match the criteria. In our example, merely laptops and projectors are visible.

Filtering options can besides be accessed from the Sort & Filter command on the Dwelling house tab.

The Filter menu on the Home tab

To use multiple filters:

Filters are cumulative, which ways you can apply multiple filters to help narrow down your results. In this case, we've already filtered our worksheet to show laptops and projectors, and we'd like to narrow it downward further to only testify laptops and projectors that were checked out in Baronial.

Click the driblet-down arrow for the column you want to filter. In this case, we will add a filter to column D to view information by appointment.
The Filter menu volition appear.
Check or uncheck the boxes depending on the data y'all desire to filter, then click OK. In our instance, we'll uncheck everything except for Baronial.
The new filter will be applied. In our example, the worksheet is now filtered to bear witness only laptops and projectors that were checked out in August.

To clear a filter:

After applying a filter, yous may want to remove—or clear—information technology from your worksheet so yous'll exist able to filter content in different ways.

Click the driblet-down arrow for the filter y'all want to clear. In our example, we'll clear the filter in column D.
The Filter menu will appear.
Cull Clear Filter From [Column NAME] from the Filter carte. In our example, nosotros'll select Clear Filter From "Checked Out".
The filter will be cleared from the cavalcade. The previously subconscious data will exist displayed.

To remove all filters from your worksheet, click the Filter command on the Data tab.

The Filter button on the Data tab

Advanced filtering

If yous need a filter for something specific, basic filtering may non give you enough options. Fortunately, Excel includes many avant-garde filtering tools, including search, text, date, and number filtering, which can narrow your results to help find exactly what yous need.

To filter with search:

Excel allows you lot to search for data that contains an exact phrase, number, engagement, and more. In our example, we'll utilise this feature to show only Saris brand products in our equipment log.

Select the Information tab, then click the Filter command. A drop-downwardly arrow will announced in the header prison cell for each column. Note: If you've already added filters to your worksheet, you tin skip this step.
Click the drop-downwardly arrow for the cavalcade you desire to filter. In our example, we'll filter column C.
The Filter menu will appear. Enter a search term into the search box. Search results will appear automatically below the Text Filters field as you blazon. In our example, we'll type saris to observe all Saris brand equipment. When you're done, click OK.
The worksheet will be filtered according to your search term. In our example, the worksheet is now filtered to prove simply Saris make equipment.

To use advanced text filters:

Advanced text filters tin can be used to display more specific data, like cells that incorporate a certain number of characters or data that excludes a specific word or number. In our example, we'd similar to exclude any particular containing the word laptop.

Select the Data tab, then click the Filter command. A drop-downward arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you lot tin can skip this step.
Click the driblet-down arrow for the column you desire to filter. In our example, we'll filter column C.
The Filter carte du jour will appear. Hover the mouse over Text Filters, then select the desired text filter from the drib-downwards menu. In our example, we'll choose Does Not Contain to view data that does non contain specific text.
The Custom AutoFilter dialog box will appear. Enter the desired text to the right of the filter, so click OK. In our example, nosotros'll type laptop to exclude whatever items containing this give-and-take.
The data will be filtered by the selected text filter. In our instance, our worksheet now displays items that do not contain the word laptop.

To employ advanced number filters:

Advanced number filters allow you to manipulate numbered information in different ways. In this example, we'll brandish merely sure types of equipment based on the range of ID numbers.

Select the Data tab on the Ribbon, and then click the Filter command. A driblet-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, yous can skip this step.
Click the drib-down arrow for the column you desire to filter. In our case, nosotros'll filter column A to view merely a certain range of ID numbers.
The Filter menu volition appear. Hover the mouse over Number Filters, then select the desired number filter from the drop-downwards menu. In our example, we'll choose Between to view ID numbers between a specific number range.
The Custom AutoFilter dialog box volition appear. Enter the desired number(s) to the right of each filter, so click OK. In our example, nosotros want to filter for ID numbers greater than or equal to 3000 but less than or equal to 6000, which will display ID numbers in the 3000-6000 range.
The information will exist filtered by the selected number filter. In our example, only items with an ID number betwixt 3000 and 6000 are visible.

To use avant-garde appointment filters:

Advanced appointment filters can be used to view information from a certain time flow, such every bit last year, next quarter, or betwixt 2 dates. In this example, we'll use advanced date filters to view merely equipment that has been checked out betwixt July 15 and Baronial xv.

Select the Data tab, and then click the Filter command. A driblet-downwardly arrow will appear in the header cell for each column. Note: If yous've already added filters to your worksheet, you tin skip this step.
Click the drop-down arrow for the column you want to filter. In our example, we'll filter cavalcade D to view but a certain range of dates.
The Filter menu volition appear. Hover the mouse over Appointment Filters, then select the desired date filter from the drop-down card. In our example, we'll select Between to view equipment that has been checked out between July fifteen and Baronial 15.
The Custom AutoFilter dialog box will appear. Enter the desired date(due south) to the right of each filter, then click OK. In our example, we want to filter for dates after or equal to July 15, 2015, and before or equal to August 15, 2015, which volition display a range between these dates.
The worksheet will be filtered by the selected date filter. In our example, we can now come across which items have been checked out between July 15 and Baronial 15.

Challenge!

Open our exercise workbook.
Click the Challenge tab in the bottom-left of the workbook.
Apply a filter to prove just Electronics and Instruments.
Use the Search feature to filter item descriptions that comprise the word Sansei. After y'all exercise this, you should accept half-dozen entries showing.
Articulate the Item Description filter.
Using a number filter, show loan amounts greater than or equal to $100.
Filter to evidence only items that accept deadlines in 2016.
When you're finished, your workbook should look similar this:

/en/excel2016/groups-and-subtotals/content/