Before attending this course, students must have:

• An intermediate usage of Microsoft Office Excel 2003/2007 for at least 1 year.

Module 1: Working with Databases

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 : Practice 1

After completing this module, students will be able to:

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

Module 2: Using AutoFilter

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 : Practice 2

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

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 : Practice 3

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

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

Lessons

• Using VLookup
• Using HLookup

Lab : Practice 4

After completing this module, students will be able to:

• Make use of Vlookup and Hlookup

Module 5: Exporting and Importing Data

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 : Practice 5

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

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 : Practice 6

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