Chapter 7

1. Chapter 7 Data Management

2. Agenda • Database concept • Import data • Input and edit data • Sort data • Function • Filter data • Create range name • Calculate subtotal • Create a pivot table and corresponding pivot chart

3. Database Concept • List: simple database in a worksheet for similar rows of data • Rows (records): individual information • Columns (fields): unique attribute information in a record • First row contains field names • Need a blank row and a blank column between the list and the rest of the worksheet • Primary key: to determine an unique record • Sort Key: sequence

4. Import Data • Convert an ASCII (text) file into an Excel workbook • File menu, Open, All files • Select the text file to open the Text Import Wizard • Two file formats: fixed width and delimited • Fixed width: same number of positions in each field of every record • Delimited: a specific character (a comma or tab) separating each field of every record • Spell check for field contents

5. Input and Edit Data • Data form: add, edit, and delete records in a list • Data menu, Form • Using tab or curser between fields movement • One row at a time • Add or edit record using regular spreadsheet operations

6. Sort Data • Arrange records in a list according to the value in designated fields (sort keys) • Sort filed: text, numeric, or date fields • Sequence: ascending or descending order • Maximum three fields • Sorting multiple fields: choose most important field as the primary sort key • Need re-sort the list after adding new record

7. Date Function • The Today() function: the current date • The Now() function: the current date and time • Dates stored as integers • Calculate the interval between two dates • Convert the number of days between two dates to weeks, months, or years

8. Database Functions • Parallel arithmetic operations of the same statistical functions • Functions include DSUM, DAVERAGE, DMAX, DMIN, and DCOUNT

9. Filter Data • Filter: a subset of records meeting a specific criteria • Two method: AutoFilter or the Advanced Filter command

10. AutoFilter • Set criteria for fields using the drop-down list for the field • Displaying rows meeting the criteria • Hiding other rows • Set criteria on multiple fields • Displaying the rows meeting all the criteria

11. Advanced Filter • Create more complex criteria (such as OR condition) • Copy the resulting rows to another section on the worksheet • Require the use of a criteria range • At least two rows • First row for field names • One or more rows for values • Text entries assumed to be followed by a wildcard (*) • Relational operators (such as >) for finding a designated range

12. Range Name • Use a range name for a cell or cell range • Use EmployeeList for A1:E15 • Range name adjust for insertions or deletions within the range • Range names always considered as an absolute reference • Range name can be used in formulas

13. Calculate Subtotal • Computes subtotals for data group • Need to sort the list first • Data menu, Subtotals

14. Pivot Tables • Present data in summary form • Divide the records in a list into categories • Compute summary statistics for those categories • Refresh after changing list • Data menu, PivotTable Wizard • Display pivot table with pivot chart • Saved as Web pages with full interactivity

15. Points to Remember • Database concept • Import data • Input and edit data • Sort data • Function • Filter data • Create range name • Calculate subtotal • Create a pivot table and corresponding pivot chart

16. Assignment • Practice exercises 4, 5, and 6 • Due data: