Filter Data | Technology Training and Education | MSU Denver

You are here: MSU Denver Home > Technology Training and Education > Document Center > Microsoft Office 2010 (PC) > Microsoft Excel > Filter Data

Technology Training and Education

Filter Data

Filter by texts, numbers, date or times

Select a column of alphanumeric/numeric/dates/times data in a range of cell, or make sure that the active cell is in a table column containing the data
Go to Home tab > Editing group > click on Sort & Filter > select Filter

Click the down arrow in each column header

Do one of the following:

In the Search box, enter criteria
Point to Text Filters/Number Filters/Date Filters > select either one of the comparison commands or Custom Filter

In the Custom AutoFilter dialog box, in the box on the right, do one of the following:

Enter text/number
Click the down arrow > select the text/number/date or time value from the list

For filtering dates and times, click the Calendar button to find and enter a date

Filter for top or bottom numbers

Select a column of alphanumeric/numeric/dates/times data in a range of cell, or make sure that the active cell is in a table column containing the data
Go to Home tab > Editing group > click on Sort & Filter > select Filter

Click the down arrow in each column header > point to Number Filters > select Top 10

In the Top 10 AutoFilter dialog box, do the following:

In the box on the left, click the down arrow > select either Top or Bottom.
In the box in the middle, enter the number of top list
In the box on the right, do one of the following:

To filter by number, click the down arrow > select Items
To filter by percentage, click the down arrow > select Percent

Filter for the above or below average number

Select a column of alphanumeric/numeric/dates/times data in a range of cell, or make sure that the active cell is in a table column containing the data
Go to Home tab > Editing group > click on Sort & Filter > select Filter

Click the down arrow in each column header > point to Number Filters

Select one or more of the following:

To filter by numbers that are above the average, click Above Average
To filter by numbers that are below the average, click Below Average

Filter for blanks or nonblanks

Select a column of alphanumeric/numeric/dates/times data in a range of cell, or make sure that the active cell is in a table column containing the data
Go to Home tab > Editing group > click on Sort & Filter > select Filter

Click the down arrow in each column header
Do one of the following:

To filter for nonblanks, select the (Select All) check box > clear the (Blanks) check box.
To filter for blanks, clear the (Select All) check box > select the (Blanks) check box

Note: The (Blanks) check box is available only if the range of cells or table column contains at least one blank cell.

Filter for selection

In a range of cells or table column, right click a cell containing the value, color, font color, or icon to filter by.
Go to Home tab > Editing group > click on Sort & Filter > click Filter

Click the down arrow in each column header
Point to Filter by Color > select either Filter by Cell Color, Filter by Font Color, or Filter by Cell Icon depending on the type of format

Filter for cell color, font color or icon set

In a range of cells or table column, right click a cell containing the value, color, font color, or icon to filter by.
Go to Home tab > Editing group > click on Sort & Filter > click Filter

Do one of the following:

To filter by text, number, or date or time, click Filter by Cell's Value.
To filter by cell color, click Filter by Cell's Color.
To filter by font color, click Filter by Cell's Font Color.
To filter by icon, click Filter by Cell's Icon

Reapply a filter after changing the data

Click a cell in the range or table
Go to Data tab > Sort & Filter group > click on Reapply

Clear A Filter

To clear a filter for one column in a multicolumn range of cells or table, click the Filter button on the column heading > click Clear Filter from <"Column Name">

To clear all filters in a worksheet and redisplay all rows, go to Home tab > Editing group ð click Sort & Filter > click Clear