Excel Lists

A list in Excel is a collection of related records stored in rows and columns.  Sound familiar?  Yes, a list is a database.  Excel's database features are not as sophisticated as Access; therefore, Microsoft prefers to call a database in Excel a list instead of a database.  For simple database applications, an Excel list is much easier 
to create and to use.  However, the major records of a company (customer records, personnel records, etc.) should be stored in a true database application such as Access.

Let's review a few database terms which you learned in the Access portion of CPT 170.  A record is all the data about one person or one object.  For example, all the data we have on one student enrolled makes up a record.  The individual pieces of data in each record (student's last name, social security number, date of birth) are fields. 

In an Excel list, records are stored in rows and fields are in columns.  At the top of each column, you must have a field name describing the data in the rows below.  To distinguish the field names from the records, make the field names bold or put a border below the field names.  Do not leave a blank row between the field names and the first record.

The list may be on the same worksheet with other data.  If so, the list must be separated from any other data by blank rows and columns.  At least one blank row should be above the header row and at least on blank row should be below the last record.  Also, at least on blank column should be to the right of the last column of the list.

Read pages 5.03 through 5.33 and perform the steps as indicated in the text.
 
 

Quick Check:
Using the Faculty.xls file, sort the data once in this order:  First, sort the list by Department; then when the departments are the same, sort by Salary with the highest salary on top; and when the salaries are the same, sort by last name. Hint:  The first row should contain this record:
Chin Roger Accounting Full 1991 M $59,281

Then, calculate the average salary for each deparment.

Save the file as Sorting Your Name.  Attach the file to an e-mail message with the subject Sorting Quick Check.

Go to Pivot Tables.

sloopj@octech.edu