How can i filter data in excel

Unchecking Select All. Choosing data to filter and clicking OK. The filtered data. Accessing Filter options from the Home tab. Clicking the drop-down arrow for column D.

Clearing a filter. The cleared filter. Clicking the Filter command to remove filters. Clicking the drop-down arrow for column C. Entering a search term and clicking OK. The worksheet filtered by the search term. Selecting a text filter. Applying a text filter. The applied text filter. 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.

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. 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 item containing the word laptop. Advanced number filters allow you to manipulate numbered data in different ways. But some filters top and bottom ten, above and below average 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 top ten values of the subset of the last filter.

In Excel, you 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, but not by both. You can filter by icon or by a custom filter, but not by both. Filters hide extraneous data. In this manner, you can concentrate on just what you want to see. In contrast, when you sort data, the data is rearranged into some order. For more information about sorting, see Sort a list of data.

You can filter by more than one column. Note: When you use Find to search filtered data, only the data that is displayed is searched; data that is not displayed is not searched. You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Table of contents. Next: Charts. Table of contents Tables. The two types of filters Using AutoFilter, you can create two types of filters: by a list value or by criteria. Reapplying a filter To determine if a filter is applied, note the icon in the column heading: A drop-down arrow means that filtering is enabled but not applied. When you reapply a filter, different results appear for the following reasons: Data has been added, modified, or deleted to the range of cells or table column.

Values returned by a formula have changed and the worksheet has been recalculated. Do not mix data types For best results, do not mix data types, such as text and number, or number and date in the same column, because only one type of filter command is available for each column. Click a cell in the range or table that you want to filter. On the Data tab, click Filter.

Notes: You can apply filters to only one range of cells on a sheet at a time. Only the first 10, unique entries in a list appear in the filter window. In the box next to the pop-up menu, enter the number that you want to use.

Depending on your choice, you may be offered additional criteria to select: Notes: You can apply filters to only one range of cells on a sheet at a time. This option is available only if the column that you want to filter contains a blank cell.

On the Data toolbar, click Filter. Under Filter , click Choose One , and then in the pop-up menu, do one of the following: To filter the range for Click Rows that contain specific text Contains or Equals. In the box next to the pop-up menu, enter the text that you want to use.

Depending on your choice, you may be offered additional criteria to select: To Click Filter the table column or selection so that both criteria must be true And. Filter the table column or selection so that either or both criteria can be true Or. Under Filter , click Choose One , 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 contain text but do not begin with letters Does Not Begin With. Cells that contain text but do not end with letters Does Not End With. Wildcard characters can be used to help you build criteria.

Under Filter , click Choose One , and select any option. In the text box, type your criteria and include a wildcard character. Do one of the following: Use To find? Do any of the following: To Do this Remove specific filter criteria for a filter Click the arrow in a column that includes a filter, and then 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 table that have filters applied, and then on the Data tab, click Filter. Table with Top 4 Items filter applied Filters are additive.