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


  • 69 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 1 e

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


  • Login