Sorting and Filtering

Sorting a List

It is easy to organize your list in alphabetical, numerical, or chronological order
The order can be either ascending or descending.

Sorting (single column)

The quickest way to sort a table of data is to use the two sorting buttons on the Standard toolbar. Select a cell in the column you wish to sort, then click the appropriate button:

Ascending Descending

In this example, sorting students by their quiz grade:

• Click on any cell in the column quiz
• Click sort Ascending
• Excel identifies the column headers by comparing the formatting. If the top row is different (bold type, capitalized or text instead of numbers), Excel will identify that row as a column heading.
  Excel will automatically recognize the word quiz as a column header
  
  
  
  
• Result: all columns will be sorted by quiz grades
• 

Cautionary Note:

This sort works well if all the cells are touching. If you have any non-contiguous cells, the sort will be carried out only on the one column and your data will be scrambled. Example:

• The same sort was run again (below), but there is a row inserted between assignment two & quiz
  With this sort, only the quiz column was sorted, not all the columns.
  George Jones has the grade of 44%, not Melissa Kent.
  

Solution: select Undo from the Edit menu or the undo tool button immediately after the sort.

Sort using the Sort Dialog Box

The the sort dialog box will allow you to sort by multiple rows or multiple criteria.

Select the rows desired go to Toolbar >> Data >> Sort This opens the sort dialog box. You are given the option to sort by three criteria Using the drop-down boxes, select from the headers. For example, you could sort by quiz grade; then sort by assignment one grade. The options window will offer specific options based on your data. There is also the option to sort left to right. This will allow you to sort rows, instead of columns.

Filtering:

Filtering allows you to temporarily data, and view only relevant data. With filtering, you are essentially working with a subset of your data. Examples:

• finding all transactions done on a certain date.
• Comparing sales data to make a chart.
• Finding all books, priced over $10
• reducing the amount of visible data so it is not so overwhelming

There are two types of filtering: AutoFilter and Advanced Filter.

AutoFilter

AutoFilter - for very simple filtering, you can use AutoFilter.
Select a cell within the column to be filtered Go to the Toolbar >>Data >> Filter This will add a drop down box arrow to the header of each column Click on the drop-down arrow. Select an appropriate filtering criteria. or select custom to define your own filter. The options in parentheses offer additional options, which will be available in a dialog box, such as Custom AutoFilter.
Remove AutoFilter by going to Toolbar Data Filter Uncheck, AutoFilter

Video tutorial on AutoFiltering
Note: this tutorial may require Internet Explorer to play properly.

AutoFilter Options

1. Click a cell in the range you want to filter - or, if you want to filter multiple columns sequentially, select the entire range. 2. On the Data menu, point to Filter, and then click AutoFilter. 3. Using the drop-down box at the top of the data range, select (Custom...)

Filter for the smallest or largest number

1. Click the arrow Field arrow in the column that contains the numbers, and click (Top 10...).
2. In the box on the left, click Top, or Bottom.
3. In the box in the middle, enter a number.
4. In the box on the right, click Items.

Example: in poverty06.xls find the top ten and bottom ten states for individual poverty (use rank); sort ascending.

Filter for the top or bottom numbers by percent

1. Click the arrow Field arrow in the column that contains the numbers, and click (Top 10...).
2. In the box on the left, click Top or Bottom.
3. In the box in the middle, enter a number.
4. In the box on the right, click Percent.

Example:: in poverty06.xls find the top and bottom ten percent of states for individual poverty (use rank); sort ascending.

Filter a range for rows that contain specific text

1. Click the arrow Field arrow in the column that contains the numbers, and click (Custom).
2. In the box on the left, click equals, or does not equal, contains, or does not contain.
3. In the box on the right, enter the text you want.
4. If you need to find text values that share some characters but not others, use a wildcard character.
5. To add another criteria - click And or Or, and repeat the previous step

	Filter results

Filter for the beginning or end of a text string

1. Click the arrow Field arrow in the column that contains the numbers, and click (Custom).
2. In the box on the left, click begins with, or does not begin with, or ends with, or does not end with.
3. In the box on the right, enter the text you want.
4. If you need to find text values that share some characters but not others, use a wildcard character.

The example below returns all states that end with the letter a

Filter for blank (null)or non-blank cells Click the arrow Field arrow in the column that contains the numbers, then click (Blanks) or (NonBlanks). Note The Blanks and NonBlanks options are available only if the column you want to filter contains a blank cell.

AutoFilter Options excerpted and edited from Microsoft's Filter a range> Filtering and Sorting Data