PowerPoint Presentation to Accompany
This presentation is the property of its rightful owner.
Sponsored Links
1 / 27

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


  • 76 Views
  • Uploaded on
  • Presentation posted in: General

PowerPoint Presentation to Accompany GO! with Microsoft Excel ® 2007 Comprehensive 1 e Chapter 7 Importing Data, Expanding a Table, and Utilizing Database Features. Objectives. Create and Expand a Table and Insert a Calculated Column Create and Sort a Custom List

Download Presentation

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

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


PowerPoint Presentation to Accompany

GO! with Microsoft Excel ®2007 Comprehensive 1e

Chapter 7

Importing Data, Expanding a Table, and Utilizing

Database Features


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


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


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.


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.


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.


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.


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.


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.


Create and Sort a Custom List

List Entries

Selected List


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.


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


Evaluate Data with Database Functions

Insert Function dialog box

DAVERAGE

function selected

Description of the

function


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.


Evaluate Data with Database Functions

  • The column specifierrefers to column data.

  • The special item specifierrefers to specific parts of the table.


Evaluate Data with Database Functions

Column

specifier

Name of header—special item specifier

Table name

Table

specifier


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.


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.


Import Data to Excel

  • Excel cannot import a Word file but can import a text file.

  • Convert Word files to text files for importing.


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.


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.


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.


Enter Subtotals and Outline the Worksheet

Subtotal dialog box

Fields available for subtotal


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.


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.


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


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


  • Login