File Processing with Excel’s List

1. File Processing with Excel’s List

2. List • An Excel list consists of columns and rows of data structured in a specific way: • Each column contains the same category of data in every row in the column(similar to a field in a database) • Each row in the list contains all of the fields of data for one entity (a person,organization, object, etc.) similar to a record in a database, but called sets of data • The first row of the list must contain a unique name at the top of each column. • There can be no blank rows in the list (there can be blank cells in a column, buttheentire row cannot be empty)

3. List Example

4. Key Field & Grouping Field • Key field: A value of the field uniquely determines a record in a file. • Example: CID field • Grouping field: A filed that can be used to group records: • Example: City field, Rating field.

5. Basic List Processing • Sorting: • Data/Sort • Two level sorting: Data/Sort/Add level • Selecting records meeting criteria • Data/Filter • Text Filter • Number Filter • Compute subtotals • Must sort list first • Data/Subtotal • Compute subtotals of two grouping fields

6. Calculated Field • A field whose value is derived from existing fields, for example, Age can be calculated from DateOfBirth.

7. Excel’s Table • Table is similar to List but with better formatting. • Convert List to Table: • Place the cursor inside anywhere in the list • Home/Format as Table; Or Insert/Table • Convert Table to List: • Table Tools/Convert to range

8. Table Tools • Total Row • Table styles • Note: With Table, the Data/Subtotal is not available.