Excel File Management: Sorting

 

Objectives:

 

Using Excel help (F1):

In the Search For box, type Sort, and press the Enter key.

In the Search Results list, click on Sort a Range, and read the topics.

 

Excel Bible: Chapter 19

Sorting

 

Sample data file: Advising.txt. One of the most common applications of Excel is not to do lots of calculations (spreadsheet stuff), but to do sorting and filtering (database, or "list" stuff).

 

Sorting: putting a list in order based on a specific field called a key.

 

Filtering: selecting records from a list based on whether they match some filtering rule (criterion).

 

Frequently, this data that needs to be sorted or filtered originates from somewhere outside of Excel. If the data are already used by some other program, the data can probably be imported into Excel. If the data is on paper and not in computer-readable form, you have two choices: (1) enter the data yourself, or (2) use OCR (optical character recognition) software and a scanner to import the data into a text file and then import the data into Excel.

 

Import: to bring data into Excel that was not originally created using Excel. Note that in order to import data into Excel (which is made up of rows and columns) the original data must be divided into rows and columns. The end of a row in non-Excel files is often indicated by a carriage return character. The end of a column in non-Excel files is often indicated by a tab (called a tab-delimited file) or a comma (called a comma-delimited file), but there are other possibilities as well. It is also possible to import a fixed-width file in which blanks have been appended to the end of short data items to make all data items the same length.

 

Example: Import a text file

Open the Advising.txt file (see hyperlink above). This is a tab-delimited text file. This means that the columns of data are separated by tab characters, and when the file gets imported into Excel, every time Excel sees a tab character, it will put the data following the tab into a new column.

To import a file into Excel:

·                     Choose "File" | "Open"

·                     In the "Files of type" drop-down box, choose the type of file (e.g. text files)

·                     Select the desired file (in this case "Advising.txt") and click on the "Open" command button

·                     The File Import wizard will be invoked:

·                     Wizard Step 1: Select the appropriate type of file, either delimited or fixed width. Note that you can tell which type of file is by looking – if it looks like a mess, it's a delimited file. If it already is nicely lined up in columns, then it is a fixed-width file.

·                     Wizard Step 1: Select the starting row.

·                     Wizard Step 1: Select the file origin (this will almost always be 437: OEM United States).

·                     Wizard Step 1: Click on "Next". This will take you to step 2 of the wizard:

·                     Wizard Step 2: Choose your delimiter character.

·                     Wizard Step 2: Click on "Next". This will take you to the third and final step of the wizard:

 

·                     Wizard Step 3: Check the data preview window and make sure that the appropriate data type (usually "General") is selected for each column. Usually the only changes you will need to make are if Excel fails to recognize a date column (or it recognizes a date column, but fails to recognize the correct date format)

·                     Wizard Step 3: Click on "Finish"

 

The "Advising.txt" file has now been imported into Excel.

Each row represents a record and each column (or cell – you should be able to tell from the context whether the speaker is referring to a single cell or a column of cells) represents a field.

 

Field: the smallest unit of meaningful data (e.g. "First Name" or "Zip code"). In Excel, a field is represented by a single cell. The yellow cell below is an example of a field.

 

Sometimes, the term “field” refers to an entire column, for example the Major field:

 

Record: a collection of related fields. In Excel, all of the fields making up a record are on the same row. The yellow cells below make up a single record.

 

File: a collection of related records. In Excel, a file is usually stored on a single worksheet. Sometimes the word table is used instead of file.

 

Field names are usually given in the top row of the file. In the "Advising" file, field names are "Last Name", "First Name", "Major", "Advisor", "Credits", and 'ID Number". Note that column headings should be in a single cell. For example, do not put "Last" in row 1 and "Name" in row 2 when the field name is "Last Name".

 

Assigning names to ranges.

It is easier for people to work with meaningful names like "DataTable" or "FirstName" rather than cell references like C23 or AB123. Excel allows users to assign names to cells and ranges. To assign a name to a range:

·                     Select the range that you wish to assign a name to.

·                     In the name box (right above cell A1), enter a descriptive name. Note that Excel does not allow blanks in a name.

In the Advising file, select cells A1:F44 and name them AdvisingTable.

Select A1:A44 and name it FirstName.

Select B1:B44 and name it LastName.

Select C1:C44 and name it Major.

Select D1:D44 and name it Advisor.

Select E1:E44 and name it Credits.

Select F1:F44 and name it IDNumber.

 

Now that we have imported the data, given it meaningful names, and provided a column that can be used to return the data to its original order, we are ready to begin doing "database things". Database things are:

·         Sorting the data

·         Filtering the data

·         Calculating group statistics on the data

 

Sorting

Ascending Sort: When using numbers, an ascending sort puts the data in increasing order. When using character string data, an ascending sort will put the data in alphabetical order. When using date data, an ascending sort will put the data in chronological order (earliest events at the top of the list, most recent at the bottom).

 

Descending Sort: When using numbers, a descending sort puts the data in decreasing order. When using character string data, a descending sort puts the data in reverse alphabetical order. When using date data, a descending sort puts the data in reverse chronological order (most recent events at the top of the list, earliest events at the bottom).

 

Example: Sort a list in ascending order using the toolbar:

To sort data:

·         Place the cursor in the column you wish to use as a sort key.

·         Click on the Sort Ascending button on the toolbar:

 

Example: Sort a list in descending order using the toolbar:

To sort data:

·         Place the cursor in the column you wish to use as a sort key.

·         Click on the Sort Descending button on the toolbar:

 

Warning! Never make a selection before clicking on the sort button. If you do, only the data that are selected will be sorted! This can make a mess of your data in a hurry (but if you click the Undo button right away, the mess can be un-done). If no range is selected at the time the sort button is clicked, Excel will try to guess the data range that you wish to sort. If the cursor is in the list of records that you wish to sort, Excel will probably guess correctly. However, if the cursor is outside of the list of records you wish to sort, you will have to tell Excel where the data are by highlighting the appropriate range.

 

Sorting with multiple keys

Sort the data based on last name. Note that there are several students who have the same last name. When two people have the same last name, they should be further sorted using their first name as a secondary key. However, the buttons on the toolbar only allow us to sort using a single key – the column that the cursor is in at the time the sort button is clicked. If we want to use secondary keys, we must use the "Data" | "Sort" command.

 

Example: Sort a list using multiple keys:

·         Place the cursor somewhere in the data.

·         Sort the data by selecting "Data" | "Sort".

·         Choose up to 3 keys and the sequence (ascending or descending) to use for each key.

·         If there are header rows (field names), turn on the "Header Row" option button.

·         Click on OK.

 

Updated 2007.04.05