Filtering Data

Microsoft Excel 2010

Filtering data means to show only those rows/columns that meet specified criteria.  For example, if you have a list of all sales made in the month and the list also indicates the salesperson for each sale, you can filter the list to only show one particular salesperson’s sales.

Excel automatically applies standard filters when you insert a Table.

Adding Filters  

Filters allow you to select the criteria you wish to filter by from a convenient drop-down list.

  1. Click inside your data area. You do not need to select the data area first, if it is presented in a proper list, i.e. no blank rows or columns. Refer to the Data Lists article for more guidelines.
  2. Display the Data Ribbon and click on the Filter button. Drop list buttons are inserted to the right of each column header. e.g.
    1. To filter by a specific heading, click on the drop list button then select the element to filter by.

You can apply more than one filter – just click on another drop-down filter button and select additional criteria. Columns with filters applies will show a filter symbol in the drop list button, e.g. in the following example, both City and Amount columns have filters applied.

Alternatively, in step 2 above, click on the Sort and Filter button at the right side of the Home ribbon to display the following. Choose Filter to quickly apply filters to your header row or Clear to remove the filters.

Remove Filtering

If you just want to unfilter an area (e.g. if you filtered by City and now wish to display all Cities), click on the filter button in the column header and choose Clear Filter from.

If you wish to remove the filtering feature, simply click on the Filter button again on the Data ribbon.

Sorting in Filters

To sort a list where filters are applied:

  1. Apply filters, as described above.
  2. Click on the filter button of the column you wish to sort by. The choices available depend on the type of data selected, i.e. numbers or text. The following displays choices available in a text column:
  3. [sortfilter]

  4. Choose a sort type:
    1. Sort A to Z for ascending sort order.
    2. Sort Z to A for descending sort order.
    3. Sort by Color - if your data if formatted using color coding of some kind.
    4. Clear Filter From [column name] to clear filters in the current column.
    5. Filter by Color - to filter based on color applied to cells
    6. Text Filters or Number Filters - see headings below.

Text Filters

Text Filters allow you to filter based on text or values. For example, if a column contains a list of employee titles, you could filter the list to show only those entries that contain the word 'manager'.

  1. Apply filters, as described above.
  2. Click on the filter button of the column you wish to sort by.
  3. Select Text Filters. The following options appear:
  4. Select your condition, as desired. A subsequent window will appear allowing you to specify your criteria.

Number Filters

Number Filters allow you to filter based on text or values. For example, if a column contains a list of employees' salaries, you could filter the list to show only those entries that are greater than a specified amount.

  1. Apply filters, as described above.
  2. Click on the filter button of the column you wish to sort by.
  3. Select Number Filters. The following options appear:
  4. Select your condition, as desired. A subsequent window will appear allowing you to specify your criteria.

Custom Filter

Custom filtering allows you more filtering choices and a maximum of two conditions.

  1. Apply filters, as described above.
  2. Click on the filter button of the column you wish to sort by.
  3. Select Number Filters or Text Filters.
  4. Select Custom Filter. The following window appears:
    1. Use the drop lists to select the conditions and specify your criteria. Up to two conditions may be used.
    2. Choose OK to apply the filter.

Additional References