Course 50450-B: Creating and Analyzing Database Using Microsoft Excel 2007 & 2010

Microsoft

Course 50450-B: Creating and Analyzing Database Using Microsoft Excel 2007 & 2010

Share This Learning Path

This 1 day course provides students with the knowledge and skills to use advanced features in creating and analyzing databases. Students will learn how to sort and manage data in lists; filter and query data; apply lookup and database functions. Students will also learn how to analyze and evaluate the information in databases by creating pivot table and pivot charts.

Audience Profile

This course is intended for Information workers who have at least a year experience in using Microsoft Office Excel 2003/2007.

Prerequisites

Course outline

Module 1: Working with Databases

Module Overview

This module explains how to make use of Excel to create a sample database format.

Lessons

Creating a Database
Modifying a Database
Sorting Records by Multiple Fields
Using Data Validation
Validating Data using a List
Creating a Custom Error Message
Removing Data Validation
Creating Subtotals in a List
Removing Subtotals from a List

Lab Sessions

Working with Databases

Lab Lessons

Creating and Modify Database
Using Data Validation
Creating, Removing Subtotals

After completing this module, students will be able to:

Creating and Modify Database.
Using Data Validation.
Creating, Removing Subtotals.

Module 2: Using AutoFilter

Module Overview

This module explains how to use AutoFilter to get their desired details from Excel List.

Lessons

Enabling AutoFilter
Using AutoFilter to Filter a List
Clearing AutoFilter Criteria
Creating a Custom AutoFilter

Lab Sessions

Using AutoFilter

Lab Lessons

Enabling AutoFilter
Using AutoFilter to Filter a List
Clearing AutoFilter Criteria
Creating a Custom AutoFilter

After completing this module, students will be able to:

Use AutoFilter to get their desired details from Excel list.
Create Custom AutoFilter.

Module 3: Working with Advanced Filters

Module Overview

This module explains how to make use of the advanced filter to set criteria range and copy the result to another location in Excel ranges.

Lessons

Creating a Criteria Range
Using a Criteria Range
Showing All Records
Using an Advanced And Condition
Using an Advanced Or Condition
Copying Filtered Records
Using Database Functions

Lab Sessions

Working with Advanced Filters

Lab Lessons

Set Criteria range for the advanced filters
Copying Filtered record to another location in Excel ranges
Use Database function for calculating required results

After completing this module, students will be able to:

Set Criteria range for the advanced filters.
Copying Filtered record to another location in Excel ranges.
Use Database function for calculating required results.

Module 4: Lookup Formulas

Module Overview

This module explains how to make use of Vlookup, Hlookup to retrieve desired items in Excel Tables.

Lessons

Using VLookup
Using HLookup

Lab Sessions

Lookup Formulas

Lab Lessons

Make use of Vlookup and Hlookup

After completing this module, students will be able to:

Make use of Vlookup and Hlookup.

Module 5: Exporting and Importing Data

Module Overview

This module explains how to import and export Excel data to text formats. It also shows how to import data from the web.

Lessons

Exporting Data to Other Applications
Exporting to XML
Importing Data from Text Files
Changing External Data Range Properties
Importing Data from Other Applications
Removing the Query Definition
Importing Dynamic Data from the Web
Copying a Table from a Web Page

Lab Sessions

Exporting and Importing Data

Lab Lessons

Import and export data to Text format
Import data from other applications
Import data from the Web

After completing this module, students will be able to:

Import and export data to Text format.
Import data from other applications.
Import data from the Web.

Module 6: Creating/Revising PivotTables

Module Overview

This module explains how to use determine the source needed to create its PivotTable/PivotChart report.

Lessons

Creating a PivotTable Report
Adding PivotTable Report Fields
Selecting a Page Field Item
Refreshing a PivotTable Report
Changing the Summary Function
Adding New Fields to a PivotTable Report
Moving PivotTable Report Fields
Hiding/Unhiding PivotTable Report Items
Deleting PivotTable Report Fields
Creating a Page Field Report
Formatting a PivotTable Report
Creating a PivotChart Report
Publishing PivotTable Reports to the Web

Lab Sessions

Creating/Revising PivotTables

Lab Lessons

Determine the source needed for its PivotTable
Create the PivotTable
Adding/removing Fields for the PivotTable
Creating Page Field Report
Creating a PivotChart Report
Publishing PivotTable Report to the Web

After completing this module, students will be able to:

Determine the source needed for its PivotTable.
Create the PivotTable.
Adding/removing Fields for the PivotTable.
Creating Page Field Report.
Creating a PivotChart Report.
Publishing PivotTable Report to the Web.

Book Your Seat

Find Learning Paths

Course 20703-1-B: Administering System Center Configuration Manager

Join our community of certified professionals

About Us

ICTC is the leader in technical certification courses and exams. Our labs consist of a latest tech PCs and our instructors are certified from each vendor

Learn

View all the provided certifications and there relevant courses. Book online for a certification exam.

Contact Us

International Computer Training Center