Table of Contents: Module 3



VALIDATING, PROTECTING, AUDITING FORM AND TEMPLATE
Using Data Validation

How To Validate Data Input
How To Compose Data Input Message
How To Create Error Alert Message

Creating New Workbook Using Template

How To Create A New Workbook Using Built-In Template On Your Computer

How To Create A New Workbook Using Template On Office Online
How To Create A New Template
How To Create New Workbook From Own Template
How To Edit The Template

Protecting Worksheet Style, Contents And Elements

How To Protect The Worksheet
How To Unprotect The Worksheet
How To Unlock Cell For Editing In Protected Sheet
How To Hide Cell Formula In Protected Sheet
How To Set Password To Edit Specific Range
How To Grant Selected Users Edit Range Without Password

Protecting Your Workbook From Unauthorized Users Access

How To Protect The Workbook Structure
How To Unprotect The Workbook Structure

Protecting Your Workbook From Unauthorized Users Access

How To Set Password To Open
How To Remove Password To Open
How To Set Password To Modify

Auditing Worksheets And Tracing Cell References

How To Select Cells That Contain Formula
How To Watch Formulas

How To Find Cells That Provide Data To An Active Cell (Precedents)
How To Find Cells That Referred By An Active Cell (Dependents)

How To Hide All The Auditing Arrows

Tracing Errors

How To Circle And Remove Circle For Invalid Data

How To Correct Calculation Errors

How To Set Error Checking Options

How To Scan And Correct All Errors



WORKING WITH ADVANCED FORMATTING, STYLE AND OUTLINING
Customizing Data Appearance With Advance Formatting

How To Use Build-In Number Formats
How To Custom Number Format
How To Custom Date Format

Using Conditional Formatting

How To Use Conditional Formatting

Maintaining Format Consistency With Style

How To Add Style Box Tool To The Toolbar
How To Create New Style
How To Apply Styles
How To Redefine Style
How To Delete Style
How To Merge Style From Another Workbook

Using Data Outlining

How To Create An Outline Automatically
How To Remove Outline
How To Create Outline Manually
How To Use Subtotal To Create Outline
How To Show / Hide Details Using Outline Symbols
How To Remove Subtotal



WORKING WITH MULTIPLE WORKSHEETS AND WORKBOOKS
Managing Data in Multiple Worksheets

How To Select Adjacent Worksheets
How To Select Non-Adjacent Worksheets
How To Enter Data Into Multiple Worksheets

Using 3-D Formula Across Worksheets

How To Enter 3-D Formula Across Worksheets
How To Enter 3-D Formula Across Worksheets Using Function

Using Range Name

How To Define A Range Name
How To Define A 3-D Range Name Using The Menu
How To Redefine The Cell References In Range Name
How To Delete A Range Name
How To Use Range Name In Formula

Combining Worksheets And Data From Different Workbooks

How To Consolidate Data
How To Move Or Copy Worksheets to Another Workbook

Linking Information From Different Worksheets And Workbook

How To Link Information Using Copy And Paste Link
How To Update Link

USING ADVANCED FUNCTIONS
Using Financial Functions

How To Use PMT Function
How To Use ABS Function

Using Logical Functions

How To Use Logical Test
How To Use IF Function To Return A Text
How To Use IF Function To Return A Value
How To Use Nested IF Function
How To Use OR Function
How To Use AND Function
How To Use COUNTIF Function

USING WORKGROUP FEATURES
Using Comments

How To Add Comment Into A Cell

How To Show Comments
How To Show/Hide Comment Indicators
How To Edit And Delete Comments

Sharing Workbooks

How To Enable Workbook Sharing
How To Disable Workbook Sharing

How To Merge Copies Of Shared Workbook

How To Accept Or Reject Changes In Merged Workbook

How To Protect A Shared Workbook

How To Track Changes In The Same Workbook

How To Accept Or Reject Changes From Different Users

Creating Workbook With Restricted Permission Using IRM

How To Create Workbook With Restricted Permission
How To View Workbook With Restricted Permisssion

How To Allow Users To View Workbook Without Microsoft Office 2003

How To Allow Users To Print Documents With Restricted Permission

How To Change User's Access Level

How To Set An Expiration Date For A Workbook

How To Attach A Referral Information In Restricted Workbook

How To Remove Workbook With Restricted Permission



EXERCISES
Exercise - Validating, Protecting, Auditing Form And Template
Exercise - Using Advanced Formatting, Style And Outlining
Exercise - Working With Multiple Worksheets And Workbooks
Exercise - Using Advance Functions
Exercise - Using Workgroup Features




Microsoft Excel 2003 Training Manual
Table of Contents: Module 4



CREATING POWERFUL DATABASE
Creating Database

How To Create Database Field
How To Add Records Manually
How To Add New Record Using A Built-In Data Form

Managing Record in Database

How To Search For The Record Using Data Form
How To Edit / Delete The Record Using Data Form

How To Create A List

How To Find Total In A List

How To Switch Functions In A List

How To Convert A List To A Range

Sorting Records

How To Perform Simple Sort

How To Perform Multiple Sort

How To Set Advanced Sort Options

Using AutoFilter

How To Enable / Disable AutoFilter
How To Apply / Remove The Criteria
How To Customize Criteria
How To Use Wildcard In Criteria

Using Advance Filter

How To Define The Criteria Range
How To Use The Advanced Filter Command

Using Simple Database Functions

How To Find The Sum Of Specific Records Using Paste Function
How To Find The Average Of Specific Records
How To Find The Maximum / Minimum Value Of Specific Records
How To Find The Number Of Records

Using VLOOKUP Function

How To Use VLOOKUP To Find Data
How To Limit The Lookup Value For Exact Match
How To Use The Closest Match For The Lookup Value




CREATING PIVOTTABLE AND ANALYZING DATA
Creating Pivot Table

How To Create A PivotTable

How To Create A PivotTable From An Access Database

Updating Pivot Table

How To Update Information In Pivot Table
How To Add Information In Pivot Table
How To Arrange Information In Pivot Table
How To Remove Information In Pivot Table
How To Create Page In Pivot Table
How To Display Pages On Separate Sheets

Modifying Calculated Field in Pivot Table

How To Use Other Functions In Calculated Fields
How To Insert Calculated Fields
How To Hide Calculated Fields
How To Delete Calculated Fields

Creating Chart From Pivot Table

How To Create Chart From Pivot Table

Exploring What-If Table

How To Create One Variable What-If Table
How To Create Two Variable What-If Table

Using Scenarios

How To Create Scenarios
How To Apply Scenario
How To Edit Scenario
How To Delete Scenario

Implementing Problem Solving

How To Use Goal Seek

How To Load Add-Ins

How To Use Solver



AUTOMATING ROUTINE TASKS WITH MACROS
Understanding Macros

How To Record New Macro

How To Run Macro With Menu

How To Create A Run Macro Button On Toolbar

How To Create Macro Command Button On Worksheet

How To Edit Command Button

Editing Macro

How To Edit Macro Coding
How To Rename Macro

How To Delete Macro

Using Relative Reference Macro

How To Record A Relative Reference Macro
How To Run Relative Reference Macro

IMPORTING AND EXPORTING DATA
Importing / Exporting Data

How To Use Text Import Wizard
How To Export Data To A Document
How To Copy Data From Other Applications
How To Insert Objects

How To Export Data To Excel Workbook In Other Application

Retrieving Data From A Database

How To Get Specific Information Using Database Query
How To Edit Database Query

WORKING WITH THE WEB
Creating Web Pages

How To Create Web Page In Excel

How To Create An Interactive Web Page

How To Preview A Web Page

How To Publish A Web Page

How To Add A New FTP Site

How To Access Files In An FTP Site

Editing The Web Pages

How To Open And Edit A Workbook Web Page

How To Create A Hyperlink

How To Delete A Hyperlink



EXERCISES

Exercise - Creating Powerful Database
Exercise - Creating Pivot Table And Analyzing Data

Exercise - Automating Routine Tasks With Macros
Exercise - Importing And Exporting Data

Exercise - Working With The Web