50101A - Analyzing Database (Microsoft Office Excel 2002-2003)

Introduction

Elements of this syllabus are subject to change.

This in-depth, one-day, instructor-led course provides students with the knowledge of creating and analyzing databases.

Participants will learn how to sort and manage data in lists; filter and query data; use outlines on worksheets, and use lookup and database functions. Participants will also learn thoroughly how to analyze and evaluate the information in databases by creating PivotTables and PivotCharts.

Audience

Information workers who want to take advantage of Microsoft Office Excel shortcuts, capabilities, and time saving tools

At Course Completion

After completing this course, students will be able to:

Sort in ascending/descending order

Find data

Replace data

Find and replace cell formats

Use the AutoFill feature

Use data entry shortcuts

Create a linear series

Create a date series

Use a stop value

Create a growth trend series

Create a database

Sort records by multiple fields

Validate data by using a list

Create a custom error message

Remove data validation

Create subtotals in a list

Remove subtotals from a list

:: Course 50101A ::

View data form records

Edit data form records

Add data form records

Define criteria

Clear data form criteria

Delete data form records

Enable AutoFilter

Use AutoFilter to filter a list

Clear AutoFilter criteria

Create a custom AutoFilter

Disable AutoFilter

Create a criteria range

Use a criteria range

Show all records

Use an advanced AND condition

Use an advanced OR condition

Copy filtered records

Use database functions

Export data to other applications

Import data from text files

Change external data range properties

Import data from other applications

Remove the query definition

Create a PivotTable report

Add PivotTable report fields

Select a page field item

Refresh a PivotTable report

Change the Summary function

Add new fields to a PivotTable report

Move PivotTable report fields

Hide/Unhide PivotTable report items

Delete PivotTable report fields

Create a Page Field report

Format a PivotTable report

Create a PivotChart report

:: Course 50101A ::

Prerequisites

Before attending this course, students must be:

An intermediate user of Microsoft Office Excel 2002 or 2000 for at least one year

Module 1: CREATING AN EXCEL DATABASE

This module explains how to create a simple database or list in Microsoft Office Excel.

Lessons

An Excel Database

Excel List Command – New in Excel 2003

Create a New Database

Freeze Panes

Lab 1

Review 1

Practice 1

After completing this module, students will be able to:

Create new databases quickly in Excel.

Module 2: MODIFYING A DATABASE

This module explains how to use simple time saving tips on how to enter data quickly into an Excel database.

Lessons

Add New Records

Insert a New Record

Add New Fields

Find and Replace Data

Find and Replace Formats

Lab 2

Review 2

Practice 2

After completing this module, students will be able to:

Learn timesaving techniques in entering data in Excel.

Use timesaving techniques in replacing or changing data in Excel.

:: Course 50101A ::

Module 3: USING DATA VALIDATION

This module explains how to use the Data Validation feature to reduce error while keying in data in Excel databases.

Lessons

Reduce Data Entry Errors by Using Data Validation

Validate Text Length

Create a Custom Error Message

Validate Data by Using a List

Set Maximum and Minimum Values

Remove Validation Rules

Lab 3

Review 3

Practice 3

After completing this module, students will be able to:

Create data validation rules to reduce data entry errors.

Customize data validation rules to help in data entry efforts.

Module 4: USING DATA FORMS

This module explains how to use the data form feature as an alternative way to key in new records, and find and edit records in an Excel database.

Lessons

View Data Form Records

Edit Data Form Records

Add Records by Using Data Form

Define Search Criteria

Clear Criteria

Delete Records by Using Data Form

Lab 4

Review 4

Practice 4

After completing this module, students will be able to:

Use the data form dialog box effectively to key in new records.

Define search criteria for finding records.

Delete unneeded records from the database.

:: Course 50101A ::

Module 5: SORTING AND FILTERING DATA

This module explains how to use the sorting and filtering features available in Excel.

Lessons

Use Simple Sorting

Sort Records By Multiple Fields

Use Custom Sort Orders

Create Custom Sort Orders

Use AutoFilter to Filter Data

Remove a Filter Criteria

Display Top Ten Records

Create a Custom AutoFilter

Create a Custom Filter by Using Wildcard

Lab 5

Review 5

Practice 5

After completing this module, students will be able to:

Use simple one-key sort and multiple-key sort.

Create customized sort orders.

Use the AutoFilter feature to do simple filtering.

Use more complex filtering by using the AutoFilter feature.

Module 6: WORKING WITH ADVANCED FILTER & DATABASE FUNCTIONS

This module explains how to use advanced filter to perform complex filtering and derive analysis results by using database functions.

Lessons

Create a Criteria Range

Use a Criteria Range

Use an Advanced AND Condition

Use an Advanced OR Condition

Copy Filtered Records

Use Database Functions

Lab 6

Review 6

Practice 6

After completing this module, students will be able to:

Build a criteria table.

Create AND and OR condition criteria.

Use a criteria table as part of a database function.

:: Course 50101A ::

Module 7: SUMMARIZING DATA

This module explains how to create a subtotal to summarize data.

Lessons

Create a Total Row

Use Subtotals to Summarize Data

Convert Data List to Data Range

Use Subtotals

Apply Multiple Subtotals

Use Outlines in Subtotals

Lab 7

Review 7

Practice 7

After completing this module, students will be able to:

Create a subtotal by using the Excel List feature

Use the Subtotal feature and create multiple subtotals.

Use group and outlining to hide and show details.

Module 8: EXPORTING AND IMPORTING DATA

This module explains how to export and import data to and from Excel.

Lessons

Export Data to Other Applications

Export Excel Data as a Text File

Export Data to XML

Import Data from a Text File

Refresh Data

Change External Data Range Properties

Remove Query Definition

Import Data from Other Applications

Import Dynamic Data from the Web

Copy a Table from a Web Page

Lab 8

Review 8

Practice 8

After completing this module, students will be able to:

Import and export text file to and from Excel.

Import data from an external database and Web tables.

:: Course 50101A ::

Module 9: PIVOTTABLES

This module explains how to create summary analysis by using Excel PivotTables.

Lessons

Get Answers with PivotTables

Use the PivotTable Wizard

Create a PivotTable Layout

View Our Report Differently

Rearrange the Layout

Format a PivotTable Report

Create a PivotChart Report

Lab 9

Review 9

Practice 9

After completing this module, students will be able to:

Create a PivotTable and rearrange the layout to get different summary results.

Programme scope, delivery arrangements and suitability are confirmed during enquiry.