How To Set A Filter In Excel

Lesson 20: Filtering Data

/en/excel/sorting-information/content/

Introduction

If your worksheet contains a lot of content, information technology can be hard to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need.

Optional: Download our practice workbook.

Scout the video below to larn more most filtering data in Excel.

To filter data:

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

In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column. In our case, our worksheet is organized into different columns identified by the header cells in row ane: ID#, Blazon, Equipment Item, and and then on.
Select the Data tab, then click the Filter command.
A driblet-down arrow will appear in the header cell for each column.
Click the drop-downwardly pointer for the column you want to filter. In our case, we volition filter column B to view only sure types of equipment.
The Filter menu volition announced.
Uncheck the box side by side to Select All to quickly deselect all information.
Check the boxes next to the information yous desire to filter, and so click OK. In this example, we will check Laptop and Projector to view only these types of equipment.
The data will be filtered, temporarily hiding whatsoever content that doesn't match the criteria. In our example, only laptops and projectors are visible.

Filtering options tin too be accessed from the Sort & Filter control on the Home tab.

The Filter menu on the Home tab

To apply multiple filters:

Filters are cumulative, which means you tin apply multiple filters to help narrow downwards your results. In this example, we've already filtered our worksheet to show laptops and projectors, and we'd like to narrow it downwards farther to only show laptops and projectors that were checked out in August.

Click the drop-down pointer for the column you lot want to filter. In this example, we will add together a filter to column D to view information by date.
The Filter carte du jour will appear.
Check or uncheck the boxes depending on the data you want to filter, then click OK. In our case, we'll uncheck everything except for August.
The new filter will be applied. In our example, the worksheet is now filtered to show only laptops and projectors that were checked out in August.

To clear a filter:

After applying a filter, you may want to remove—or articulate—it from your worksheet and then y'all'll be able to filter content in different ways.

Click the drop-down pointer for the filter you desire to clear. In our example, we'll articulate the filter in column D.
The Filter menu will appear.
Cull Clear Filter From [COLUMN NAME] from the Filter menu. In our example, we'll select Clear Filter From "Checked Out".
The filter will be cleared from the column. The previously hidden data will be 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 y'all need a filter for something specific, bones filtering may non give you enough options. Fortunately, Excel includes several advanced filtering tools, including search, text, date, and number filtering, which can narrow your results to help find exactly what you lot need.

To filter with search:

Excel allows you to search for data that contains an exact phrase, number, date, and more. In our example, nosotros'll apply this characteristic to evidence only Saris brand products in our equipment log.

Select the Information tab, and so click the Filter command. A drib-down pointer will appear in the header cell for each column. Notation: If you've already added filters to your worksheet, y'all tin can skip this step.
Click the drib-down arrow for the column you want to filter. In our case, we'll filter cavalcade C.
The Filter bill of fare will appear. Enter a search term into the search box. Search results will appear automatically beneath the Text Filters field as you type. In our example, we'll type saris to find all Saris make equipment. When you lot're done, click OK.
The worksheet will be filtered according to your search term. In our case, the worksheet is now filtered to show only Saris brand equipment.

To utilize avant-garde text filters:

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

Select the Data tab, so click the Filter control. A drop-downwardly arrow volition appear in the header cell for each column. Note: If you've already added filters to your worksheet, you tin skip this pace.
Click the drop-down arrow for the cavalcade you want to filter. In our case, nosotros'll filter column C.
The Filter carte will appear. Hover the mouse over Text Filters, then select the desired text filter from the drop-downward carte du jour. In our case, we'll choose Does Not Contain... to view data that does not contain specific text.
The Custom AutoFilter dialog box will appear. Enter the desired text to the right of the filter, and so click OK. In our instance, we'll type laptop to exclude any items containing this word.
The data will be filtered by the selected text filter. In our case, our worksheet at present displays items that do not incorporate the give-and-take laptop.

To apply advanced number filters:

Advanced number filters let y'all to dispense numbered data in different ways. In this instance, we'll display only certain types of equipment based on the range of ID numbers.

Select the Data tab on the Ribbon, then click the Filter command. A drop-down arrow volition appear in the header prison cell for each column. Note: If yous've already added filters to your worksheet, yous tin skip this step.
Click the driblet-downwards pointer for the column you want to filter. In our example, nosotros'll filter column A to view merely a certain range of ID numbers.
The Filter carte will announced. Hover the mouse over Number Filters, and then select the desired number filter from the drop-down carte. In our example, nosotros'll cull Between to view ID numbers between a specific number range.
The Custom AutoFilter dialog box will announced. Enter the desired number(due south) to the right of each filter, then click OK. In our example, we 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 be filtered by the selected number filter. In our instance, only items with an ID number between 3000 and 6000 are visible.

To use advanced date filters:

Advanced date filters can exist used to view data from a certain time period, such as last year, next quarter, or between two dates. In this example, we'll use advanced date filters to view only equipment that has been checked out betwixt July xv and August 15.

Select the Data tab, then click the Filter command. A drop-down arrow will announced in the header cell for each column. Note: If you've already added filters to your worksheet, you tin can skip this pace.
Click the drop-down arrow for the cavalcade you want to filter. In our example, we'll filter column D to view just a certain range of dates.
The Filter menu will appear. Hover the mouse over Date Filters, and then select the desired date filter from the drop-down menu. In our case, we'll select Between... to view equipment that has been checked out between July fifteen and Baronial xv.
The Custom AutoFilter dialog box will appear. Enter the desired appointment(south) to the right of each filter, then click OK. In our case, nosotros desire to filter for dates afterwards or equal to July 15, 2015, and before or equal to Baronial 15, 2015, which will display a range between these dates.
The worksheet will be filtered by the selected engagement filter. In our example, nosotros can at present see which items have been checked out betwixt July 15 and August 15.

Challenge!

Open our practice workbook.
Click the Challenge tab in the bottom-left of the workbook.
Use a filter to show only Electronics and Instruments.
Utilize the Search characteristic to filter item descriptions that contain the word Sansei. Later you do this, you should have six entries showing.
Articulate the Particular Description filter.
Using a number filter, testify loan amounts greater than or equal to $100.
Filter to testify only items that have deadlines in 2016.
When you're finished, your workbook should look like this: