List and Data Management

Excel is very capable of handling lists or files such as an employee list, a customer list, or a student list.   For example the figure below shows a spreadsheet containing such a list - a list of 13 employees. The first row in this file or list contains the 4 field names or labels used to describe essential facts about each of the employees. (We need to know  their Name, Location, Title, and Salary ) . Each row thereafter represents a unique record or employee in our list. The actual data for each employee is filled in under Name, Location, Title, and Salary.
 

                                                    Figure 1. A list or file

In lists which have a great number of records, we can sort, extract, and use various other database function to get  answers to our questions concerning the file.
To Create a list simply enter the data as shown
To Add records, one can just add it to the end row or add it between other records, just select the row below where you wish the record to be inserted and : Insert --> Insert Rows ---> then add your new record.

You can also use the FORM VIEW by selecting any cell within the list and then: DATA --> Form
        I

The Buttons on the Data Form Command

To Sort these records by either name, location, title, or salary may be desirable. The first key to be sorted on is called the primary key. If in addition another key is used to sort, it is called the secondary key.
To sort this list, click anywhere in the column to be sorted  ---> Data Menu ---> Sort ---> then pick the fields in the sort order you wish. The first is the primary key, second is the secondary key, and so on.
To sort quickly within one field, just click anywhere in the column and click on the A-Z icon in the Standard toolbar.

Data vs Information
The difference is that data is simply facts, whereas information is data arranged for a specific purpose.

Filtering - creating and displaying a subset of the original list according to some criteria
    Autofilter command - select any cell within the list and : Data --> Filter -->Autofilter  Drop down boxes beside each field name appear
        Example: If you wish to display only the account Reps in the list, then  figure 1 shows how to drop down the choices in the title field and select the Account Rep as the criteria.

                                                        Figure 3. Autofilter on title field
 

    Custom Autofilter - uses the Autofilter with additional criteria added.

        For example: If you wish to select all of the employees making more than $50, 000, then select the salary field drop down box --> select custom.. --> fill in the Custom AutoFilter box as shown below:


     Figure 4. Custom AutoFilter  Box and the resulting subset

1. Practice #1 a-d on P. 232. Save as Chapter 5 Practice 1 Solution.xls
2. Using the sort and Autofilter commands, Answer the questions in the Super Bowl Case Study on P. 237 somewhere on the spreadsheet. Save as Super bowl Answers.xls



---