PowerPoint Presentation to Accompany GO! with Microsoft Excel ® 2007 Comprehensive 1 e

1. PowerPoint Presentation to Accompany GO! with Microsoft Excel ®2007 Comprehensive 1e Chapter 7 Importing Data, Expanding a Table, and Utilizing Database Features

2. Objectives • Create and Expand a Table and Insert a Calculated Column • Create and Sort a Custom List • Filter by Using Advanced Criteria • Evaluate Data with Database Functions

3. Objectives • Import Data to Excel • Create Lookup Tables in Another Workbook • Enter Subtotals and Outline the Worksheet • Link and Embed a Worksheet and Chart into Word Documents

4. Create and Expand a Table and Insert a Calculated Column • A table is a collected block of organized data. • A row contains information about a single item. • A column contains one category of information about that item.

5. Create and Expand a Table and Insert a Calculated Column • Characteristics of a table: • Each column has a unique title. • Each column contains the same kind of data. • Each cell contains a single value. • Each category of data can be sorted, searched, or manipulated individually.

6. Create and Expand a Table and Insert a Calculated Column • To create a table: • Start Excel. • Click on the Insert tab. • Click the Table button.

7. Create and Expand a Table and Insert a Calculated Column • Table headers identify each category of data in a table. • Auto expansion is when a table range adjusts to include newly added columns and rows.

8. Create and Expand a Table and Insert a Calculated Column • A calculated column uses a single formula and adjusts for each row in the table. • A structured reference uses the table name and column titles in the formula. • Formula replication is when a formula is automatically filled through the range of a table.

9. Create and Sort a Custom List • A custom list sorts data in a pattern other than alphabetical or numerical. • Excel provides built-in custom lists. • You can create your own custom list. • Lists can be displayed in a column or row.

10. Create and Sort a Custom List List Entries Selected List

11. Filter by Using Advanced Criteria • The Advanced command is used to filter a range of cells using complex criteria. • Filter criteria are the conditions that limit the records displayed. • The list range is the range of the table that contains the data. • The criteria range is the location of the criteria that have been entered.

12. Evaluate Data with Database Functions • Database functions are identified by the letter D—each function starts with a D. • There are 12 database functions that can be used to evaluate data. • Examples include: • DAVERAGE • DCOUNT • DSUM

13. Evaluate Data with Database Functions Insert Function dialog box DAVERAGE function selected Description of the function

14. Evaluate Data with Database Functions • Structured reference uses formulas that reference a table and/or portions of a table. • The table specifier is the outer portion of the reference. • It is enclosed in square brackets following the table name.

15. Evaluate Data with Database Functions • The column specifierrefers to column data. • The special item specifierrefers to specific parts of the table.

16. Evaluate Data with Database Functions Column specifier Name of header—special item specifier Table name Table specifier

17. Import Data to Excel • Data can be copied and pasted or imported from other Microsoft applications. • To import means to make a permanent connection to data that can be refreshed.

18. Import Data to Excel • To import from Access: • Click on the Data tab. • In the Get External Data Group click the From Access button. • Locate your file and click Open.

19. Import Data to Excel • Excel cannot import a Word file but can import a text file. • Convert Word files to text files for importing.

20. Import Data to Excel • Convert to Range • Connection between database and worksheet is broken. • Remove Duplicates button • Used to remove records that have identical values.

21. Create Lookup Tables in Another Workbook • External lookup uses a lookup function to retrieve data from a table array in a different workbook. • In a table array, text must be sorted in alphabetical or numerical order. • The exclamation mark (!) separates the worksheet name from the name of the table array.

22. Enter Subtotals and Outline the Worksheet • Subtotals provide a total of a portion of the worksheet data. • Several functions can be used in the subtotal command: • Count, Average, Max, Min • The subtotal command also outlines the worksheet.

23. Enter Subtotals and Outline the Worksheet Subtotal dialog box Fields available for subtotal

24. Enter Subtotals and Outline the Worksheet • The Outline bar displays at the left of an outlined worksheet. • Expand/Collapse data buttons display or hide details in the worksheet rows. • Outline Level buttons collapse or expand the entire worksheet, leaving only subtotals or a grand total for the worksheet.

25. Link and Embed a Worksheet and Chart into Word Documents • Object Linking and Embedding (OLE) allows content created and updated in one application to be available in other applications. • Embedded documents are not updated. • Linked documents are updated when changes are made.

26. Covered Objectives • Create and Expand a Table and Insert a Calculated Column • Create and Sort a Custom List • Filter by Using Advanced Criteria • Evaluate Data with Database Functions

27. Covered Objectives • Import Data to Excel • Create Lookup Tables in Another Workbook • Enter Subtotals and Outline the Worksheet • Link and Embed a Worksheet and Chart into Word Documents