Database Features

1. Database Features

2. Lists • An Excel worksheet can be used like a table in a relational database. • In Excel, such a table is called a list. • Each row of the list represents a record. • Each column contains the values of the different fields in the database.

3. Excel treats any rectangular array of nonempty cells surrounded by empty rows and columns as a list. • The top row of the list contains the field names. • Throughout, we consider this example:

5. To add, modify, or delete data, you could simply work in the worksheet itself. • Or, you could do Data => Form…, which brings up a dialog box from which you could do any of these:

7. Note that the Service value (meaning years of service) cannot be modified. • This is because it is computed from the hire date using a formula like =(TODAY()-E2)/365

8. Another useful feature is the ability to sort the list according to values in from one to three fields. • Here we do Data => Sort… and fill out the resulting dialog box as follows:

10. What we get has all the Atlanta employees, then all the Boston employees, etc. • And within each location, the employees appear in alphabetical order of their names.

12. Filtering • If we want to see only a subset of the records satisfying some criteria, we can apply an appropriate filter. • If we do Data => Filter => AutoFilter, a drop-down menu button appears at each column heading. • Here we click the button on the Title field:

14. If we select Manager from this drop-down menu, our worksheet displays only the records for managers:

16. To see all the records, select (All) from the drop-down menu on the Title column heading. • If we want to see only those records for employees making at least $50,000, we can do this by selecting (Custom…) from the Salary drop-down menu.

17. Criteria: Advanced Filter and database functions • The Advanced Filter feature and the database functions like DSUM, DAVERAGE, DCOUNT, DMAX, and DMIN use criteria specified in a range of cells on the worksheet itself. • In this example, we will use A16:F17 as our criteria range:

19. We can compute the average salary of Chicago account reps and store it in cell C19 by first clicking in that cell, then clicking the Paste Function button and selecting the DAVERAGE function:

22. This brings up the Formula Palette for the DAVERAGE function. • We can enter its first and third arguments by clicking and dragging in the worksheet.

25. Subtotals • Suppose we would like to see the average salary for each category of employee. • We first sort according to the Title field to group records together according to their title. • Then we do Data => Subtotals… and specify in the resulting dialog box that we want to use the AVERAGE function on the Salary field. • This is what we get:

27. Pivot tables and pivot charts • A pivot table displays summarized values (sums, counts, or averages) of one field in a list, grouped by one set of field values along the rows and another set of field values along the columns of the worksheet. • A pivot chart displays the same information graphically.

28. To create a pivot table and/or a pivot chart, do Data => PivotTable and PivotChart Report… and use the PivotTable Wizard. • Here is an example of a pivot table and corresponding pivot chart:

31. Importing data from other sources • You can import data in text form and make it an Excel list by using the Text Import Wizard. • To run this, either open the file (a .txt file) or do Data => Get External Data => Import Text File… • You can also convert an Excel list to a text file by doing File => Save As… and specifying the type of file as text.

32. You can also import data from an Access database and make it into an Excel list by doing Data => Get External Data => New Database Query… • This brings up the Query Wizard.