How to Use a Filter in Excel

Excel 2013 filters help you locate or extract specific records by only displaying data that meets your criteria. This feature does not delete any data; it simply hides irrelevant records until you choose to disable the filter. Unfortunately, simple copy and paste functionality produces errors when selected data that spans discontinuous rows or columns, but advanced filtering options overcome that limitation.

Video of the Day

Enabling/Disabling Filter Controls

A small, triangular drop-down menu appears in any column header that has filter controls enabled. These filter controls automatically appear when you create a data table, but they must be manually enabled for non-table data by selecting any column header and then clicking "Filter" on the Data tab. Excel automatically adds the filter controls to every contiguous header in the data set unless multiple headers were manually selected, in which case, only the selected headers receive filter controls. Repeating that procedure disables the filter controls and returns your data set to its unfiltered state.

Using AutoFilters

Clicking any header's filter control triangle displays the filter options for that column. By selecting or deselecting data listed at the bottom of the drop-down menu, you only display records containing the selected values in that column. If any cell lacks content, "(Blanks)" is listed as a value, enabling you to quickly locate blank cells. If you want to stack filters, simply choose a filter from another column. You can later remove a filter from individual columns by choosing "Clear Filter From..." in the column's filter controls.

Data-Specific Filters

Filter controls also offer filters specific to the type of data in the column, such as Text Filters, Number Filters, Date Filters and Filter by Colors. Clicking any of these sub-menus opens additional options for filtering based on values, such as text beginning with -- or numbers greater than -- a selected value. Right-clicking a data cell and selecting "Filter" also enables filtering by the cell's value, icon or colors.

Advanced Filters

The Advanced Filters utility offers greater control than the basic AutoFilter function. By creating a separate data set containing the filter values, you can specify multiple filter criteria simultaneously. If you want to specify conditions that must all be met, enter values on the same row. If you want to specify either/or type conditions, enter the values on separate rows. For this to work, the second data set must use the exact headers as the primary data set. To ensure the second header mirrors the original even after the original is edited, use the formula "=A1", assuming A1 contains the header you wish to replicate. To use advanced filters, click "Advanced" in the Data tab's Sort & Filter group and enter the List and Criteria ranges.

Operators and Wild Cards

Operators fine-tune filters within the Advanced Filter criteria set. Adding an ampersand between words, such as "John&Doe," forces the filter to find instances where both terms are included. Using an asterisk within the filter criteria means any values be present in place of the asterisk, such as "S" to find all words ending in the letter S. The operators <, >, =, <>, >=, <= can be used to designate less than, greater than, equals to, not equal to, greater than or equal to, or less than or equal to. As an example, "<>T" removes all values that begin with the letter T and ">10" locates values greater than 10. Such text filters are not case-sensitive.

Copying Filtered Data

If you need to copy the filtered results to another location, use the Advanced Filter utility. Choose "Copy to Another Location" from the Advanced Filter window and then enter the cell reference of the first cell where the results should appear. To avoid errors, there must be sufficient blank cells below and to the right of that cell to accept the copied data without overwriting existing data.

Show Comments