Exploring Excel

1
Exploring Excel
Chapter 5 List and Data Management Converting
Data to Information By Robert T.
Grauer Maryann Barber
2
Objectives (1 of 2)

• Create a list
• Add, edit and delete records in an existing list
• Use Text Import Query and Query Wizard
• Describe the TODAY function and use date
  arithmetic
• Use the Sort command

3
Objectives (2 of 2)

• Use the database functions DSUM, DAVERAGE, DMAX,
  DMIN, and DCOUNT
• Use AutoFilter and Advanced Filter
• Use the Subtotals command
• Use a pivot table

4
Overview

• Fundamentals of list management
• Display selected records
• Sort the list
• Use database functions, criteria range, and
  arithmetic
• Database management can use either Excel or
  Access but which is better?

5
List and Data Management

• Data management is based on lists in Excel
• Database concepts--record, fields, and primary
  keys are part of list management
• Need valid input to produce valid output--Garbage
  In Garbage Out (GIGO)
• Editing the list through Insert Row and Columns
  command and Edit Delete

6
Database Concepts

• Lists can be used as simple databases
• Record is the individual information contained in
  a row
• Field is unique information contained in a column
  for a record
• Primary key is a unique field or combination like
  social security number

7
Lists and Data commands

• Data Form Command provides easy way to add, edit
  and delete records
• Sort command arranges lists according to value in
  fields
• Date Arithmetic is a powerful tool for formulas

8
Data Form Command
9
Sort Command
Sort Descending
Sort Ascending
10
Text Import Wizard

• Converts an ASCII (text) file into an Excel
  workbook
• Two file formats fixed width and delimited
• Use the Open command to get to Text Import Wizard

11
Text Import Wizard
12
Text Import Wizard
13
Hands-On Exercise 1

• Text Import Wizard
• Add a Record - Data Form Command and Insert Rows
  Command
• The Spell Check
• Sort the List
• Delete a Record
• Enter the Hire Dates Insert a Field
• Format the Date

14
Information versus Data

• Data is simply facts
• Information is data arranged for a specific use
• Decisions in an organization are based on
  information
• Data commands, functions and reports provide
  information

15
Filter commands

• AutoFilter is a subset of records which meet a
  set of criteria
• Advanced Filter allows for complex criterion and
  storing records in a separate worksheet area
• Criteria range specifies the values to search for
  in records

16
Auto Filter
17
Advanced Filter
18
Understanding Criteria Ranges

• Must contain at least two rows--field names and a
  second row of values
• Same row entries imply an AND condition
• Values entered in different rows meet the OR
  condition
• Empty rows return all records

19
Understanding Criteria Ranges

• Relational operators can be used to find a
  designated range
• Upper and Lower Boundaries can be established
• Equal and unequal signs select empty and nonempty
  records

20
Looking at Criteria Ranges
Criteria ranges using same row imply AND
Criteria Ranges using different rows imply OR
21
Database Functions

• Parallel arithmetic operations of the same
  statistical functions
• DSUM
• DAVERAGE
• DMAX
• DMIN
• DCOUNT

22
Using Database Functions
23
Hands-On Exercise 2

• Calculate the Years of Service
• The AutoFilter Command
• The Custom AutoFilter Command
• The Advanced Filter Command
• The Insert Name Command
• Database Functions
• The DAVERAGE Function
• The DMAX, DMIN, DSUM DCOUNT Functions
• Change the Criteria

24
Subtotals Commands

• Subtotals command in the Data menu computes
  subtotals based on data groups
• Uses a summary function like SUM or AVERAGE
• Outline format allows for several views of data

25
Pivot Tables

• Pivot tables extends the capability of database
  functions by presenting the data in summary form
• Use PivotTable Wizard in the Data menu
• Displays a Pivot Table toolbar
• Can also display Pivot charts

26
Query Wizard

• Importing Data from Access
• Use the Data menu and click on Get External Data
  command
• Set up Database Query to select desired fields
• Will import to a Excel list

27
Subtotals Dialog Box
28
Pivot Tables Dialog Box
29
Pivot Chart
30
Hands-On Exercise 3

• Insert a Column
• Create the Subtotals
• Examine the Subtotals
• The PivotTable Wizard
• Modify the Pivot Table
• Modify the Employee List
• Pivot the Table
• The Completed Pivot Table

31
Summary (1 of 2)

• List contains records of data
• Information is data arranged in a useful format
• Dates can be used for calculations
• Importing data from other applications
• Filtered list displays only a subset of records

32
Summary (2 of 2)

• Database functions DSUM, DAVERAGE, DMAX, DMIN
  and DCOUNT
• Subtotals can be inserted into a list
• Pivot table presents data in summary form

33
Practice with Excel

• The Student List
• Pivot Tables and Pivot Charts
• Expanded Employee List
• Object Linking and Embedding
• Object Linking and Embedding
• Date Arithmetic
• The Text Import Wizard

34
Case Studies

• The United States of America
• The Super Bowl
• Personnel Management
• Equal Employment Opportunity
• The Year 2000 Problem
• Data Validation