Power Point Presentation to Accompany GO with Microsoft

Objectives • Create and Expand a Table and Insert a Calculated Column • Create

Objectives • Import Data to Excel • Create Lookup Tables in Another Workbook •

Create and Expand a Table and Insert a Calculated Column • A table is

Create and Expand a Table and Insert a Calculated Column • Characteristics of a

Create and Expand a Table and Insert a Calculated Column • To create a

Create and Expand a Table and Insert a Calculated Column • Table headers identify

Create and Expand a Table and Insert a Calculated Column • A calculated column

Create and Sort a Custom List • A custom list sorts data in a

Create and Sort a Custom List Entries Selected List with Excel 2007 Comprehensive 1

Filter by Using Advanced Criteria • The Advanced command is used to filter a

Evaluate Data with Database Functions • Database functions are identified by the letter D—each

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

Evaluate Data with Database Functions • Structured reference uses formulas that reference a table

Evaluate Data with Database Functions • The column specifier refers to column data. •

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

Import Data to Excel • Data can be copied and pasted or imported from

Import Data to Excel • To import from Access: – Click on the Data

Import Data to Excel • Excel cannot import a Word file but can import

Import Data to Excel • Convert to Range – Connection between database and worksheet

Create Lookup Tables in Another Workbook • External lookup uses a lookup function to

Enter Subtotals and Outline the Worksheet • Subtotals provide a total of a portion

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

Enter Subtotals and Outline the Worksheet • The Outline bar displays at the left

Link and Embed a Worksheet and Chart into Word Documents • Object Linking and

Covered Objectives • Create and Expand a Table and Insert a Calculated Column •

Covered Objectives • Import Data to Excel • Create Lookup Tables in Another Workbook

Slides: 27

Download presentation

Power. Point Presentation to Accompany GO! with Microsoft Excel ® 2007 Comprehensive 1 e Chapter 7 Importing Data, Expanding a Table, and Utilizing Database Features with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 1

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 with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 2

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 with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 3

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 4

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 5

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 6

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 7

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 8

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 9

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 11

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 with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 12

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 14

Evaluate Data with Database Functions • The column specifier refers to column data. • The special item specifier refers to specific parts of the table. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 15

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 17

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 18

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 20

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 21

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 22

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 24

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. with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 25

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 with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 26

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 with Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 27