How do I create a custom filter in Excel?

How?

Select the data that you want to filter

1. On the Data tab, in the Sort & Filter group, click Filter.

   

2. Click the arrow

   
   in the column header to display a list in which you can make filter choices.

   NoteDepending on the type of data in the column, Microsoft Excel displays either Number Filters or Text Filters in the list.

Filter by selecting values or searching

Selecting values from a list and searching are the quickest ways to filter. When you click the arrow in a column that has filtering enabled, all values in that column appear in a list.

1. Use the Search box to enter text or numbers on which to search

2. Select and clear the check boxes to show values that are found in the column of data

3. Use advanced criteria to find values that meet specific conditions

1. To select by values, in the list, clear the (Select All) check box. This removes the check marks from all the check boxes. Then, select only the values you want to see, and click OK to see the results.

2. To search on text in the column, enter text or numbers in the Search box. Optionally, you can use wildcard characters, such as the asterisk (*) or the question mark (?). Press ENTER to see the results.

Filter data by specifying conditions

By specifying conditions, you can create custom filters that narrow down the data in the exact way that you want. You do this by building a filter. If you've ever queried data in a database, this will look familiar to you.

1. Point to either Number Filters or Text Filters in the list. A menu appears that allows you to filter on various conditions.

2. Choose a condition and then select or enter criteria. Click the And button to combine criteria (that is, two or more criteria that must both be met), and the Or button to require only one of multiple conditions to be met.

3. Click OK to apply the filter and get the results you expect.

/en/excel2013/sorting-data/content/

Introduction

If your worksheet contains a lot of content, it can be difficult 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.

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

1. 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 example, our worksheet is organized into different columns identified by the header cells in row 1: ID#, Type, Equipment Detail, and so on.
   
   A worksheet with a header row
   

2. Select the Data tab, then click the Filter command.
   
   Clicking the Filter command

3. A drop-down arrow
   
   will appear in the header cell for each column.
4. Click the drop-down arrow for the column you want to filter. In our example, we will filter column B to view only certain types of equipment.
   
   Clicking the drop-down arrow for column B
   

5. The Filter menu will appear.
6. Uncheck the box next to Select All to quickly deselect all data.
   
   Unchecking Select All

7. Check the boxes next to the data you want to filter, then click OK. In this example, we will check Laptop and Tablet to view only those types of equipment.
   
   Choosing data to filter and clicking OK

8. The data will be filtered, temporarily hiding any content that doesn't match the criteria. In our example, only laptops and tablets are visible.
   
   The filtered data

Filtering options can also be accessed from the Sort & Filter command on the Home tab.

To apply multiple filters:

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

1. Click the drop-down arrow for the column you want to filter. In this example, we will add a filter to column D to view information by date.
   
   Clicking the drop-down arrow for column D

2. The Filter menu will appear.
3. Check or uncheck the boxes depending on the data you want to filter, then click OK. In our example, we'll uncheck everything except for August.
   
   Choosing data to filter and clicking OK

4. The new filter will be applied. In our example, the worksheet is now filtered to show only laptops and tablets that were checked out in August.
   
   The filtered data

To clear a filter:

After applying a filter, you may want to remove—or clear—it from your worksheet so you'll be able to filter content in different ways.

1. Click the drop-down arrow for the filter you want to clear. In our example, we'll clear the filter in column D.
   
   Clicking the drop-down arrow for column D
   

2. The Filter menu will appear.
3. Choose Clear Filter From [COLUMN NAME] from the Filter menu. In our example, we'll select Clear Filter From "Checked Out".
   
   Clearing a filter
   

4. The filter will be cleared from the column. The previously hidden data will be displayed.
   
   The cleared filter

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

Advanced filtering

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

To filter with search:

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

1. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.
2. Click the drop-down arrow for the column you want to filter. In our example, we'll filter column C.
   
   Clicking the drop-down arrow for column C

3. 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 type. In our example, we'll type saris to find all Saris brand equipment.
4. When you're done, click OK.
   
   Entering a search term and clicking OK

5. The worksheet will be filtered according to your search term. In our example, the worksheet is now filtered to show only Saris brand equipment.
   
   The worksheet filtered by the search term

To use advanced text filters:

Advanced text filters can be used to display more specific information, such as cells that contain a certain number of characters, or data that excludes a specific word or number. In our example, we've already filtered our worksheet to only show items with Other in the Type column, but we'd like to exclude any item containing the word case.

1. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.
2. Click the drop-down arrow for the column you want to filter. In our example, we'll filter column C.
   
   Clicking the drop-down arrow for column C

3. The Filter menu will appear. Hover the mouse over Text Filters, then select the desired text filter from the drop-down menu. In our example, we'll choose Does Not Contain... to view data that does not contain specific text.
   
   Selecting a text filter

4. The Custom AutoFilter dialog box will appear. Enter the desired text to the right of the filter, then click OK. In our example, we'll type case to exclude any items containing this word.
   
   Applying a text filter

5. The data will be filtered by the selected text filter. In our example, our worksheet now displays items in the Other category that do not contain the word case.
   
   The applied text filter

To use advanced date filters:

Advanced date filters can be used to view information from a certain time period, such as last year, next quarter, or between two dates. In this example, we will use advanced date filters to view only equipment that has been checked out today.

1. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.
2. Click the drop-down arrow for the column you want to filter. In our example, we will filter column D to view only a certain range of dates.
   
   Clicking the drop-down arrow for column D

3. The Filter menu will appear. Hover the mouse over Date Filters, then select the desired date filter from the drop-down menu. In our example, we'll select Today to view equipment that has been checked out on today's date.
   
   Selecting a date filter

4. The worksheet will be filtered by the selected date filter. In our example, we can now see which items have been checked out today.
   
   The applied date filter

If you're working along with the example file, your results will be different from the images above. If you want, you can change some of the dates so the filter will give more results.

To use advanced number filters:

Advanced number filters allow you to manipulate numbered data in different ways. In this example, we will display only certain types of equipment based on the range of ID numbers.

1. Select the Data tab on the Ribbon, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.
2. Click the drop-down arrow for the column you want to filter. In our example, we'll filter column A to view only a certain range of ID numbers.
   
   Clicking the drop-down arrow for column A

3. The Filter menu will appear. Hover the mouse over Number Filters, then select the desired number filter from the drop-down menu. In our example, we will choose Between to view ID numbers between a specific number range.
   
   Selecting a number filter

4. The Custom AutoFilter dialog box will appear. Enter the desired number(s) 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 4000, which will display ID numbers in the 3000-4000 range.
   
   Applying a number filter and clicking OK
   

5. The data will be filtered by the selected number filter. In our example, only items with an ID number between 3000 and 4000 are visible.
   
   The applied number filter

Challenge!

1. Open an existing Excel workbook. If you want, you can use our practice workbook.
2. Apply a filter to a column. If you are using the example, filter the Type column (column B) so it displays only laptops and cameras.
3. Add another filter by searching. If you are using the example, search for EDI brand equipment in the Equipment Detail column (column C).
4. Clear both filters.
5. Use an advanced text filter to view data that does not contain a certain word or phrase. If you are using the example, display data that does not contain the word saris (this should exclude all Saris brand equipment).
6. Use an advanced date filter to view data from a certain time period. If you are using the example, display only the equipment that was checked out in September 2013.
7. Use an advanced number filter to view numbers less than a certain amount. If you are using the example, display all items with an ID# below 3000.

/en/excel2013/groups-and-subtotals/content/