PowerPoint Presentation to Accompany
Download
1 / 27

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


  • 122 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' PowerPoint Presentation to Accompany GO! with Microsoft Excel ® 2007 Comprehensive 1 e' - burke


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
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


Objectives1
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
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 column1
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 column2
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 column3
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 column4
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
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 list1
Create and Sort a Custom List

List Entries

Selected List


Filter by using advanced criteria
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
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 functions1
Evaluate Data with Database Functions

Insert Function dialog box

DAVERAGE

function selected

Description of the

function


Evaluate data with database functions2
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 functions3
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 functions4
Evaluate Data with Database Functions

Column

specifier

Name of header—special item specifier

Table name

Table

specifier


Import data to excel
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 excel1
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 excel2
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 excel3
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
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
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 worksheet1
Enter Subtotals and Outline the Worksheet

Subtotal dialog box

Fields available for subtotal


Enter subtotals and outline the worksheet2
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
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
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 objectives1
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


ad