|
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
How To Create A New Template
How To Create New Workbook From Own Template
How To Edit The Template
Protecting
Worksheet Style And Content
How
To Protect The Worksheet
How To Unprotect The Worksheet
How To Enable Cell For Editing In Protected Sheet
How To Hide Cell Formula In Protected Sheet
How To Protect Workbook Content
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 Find Precedents Cells
How To Find Dependent Cells
How To Hide All The Arrows
Tracing
Errors
How
To Circle And Remove Circle For Invalid Data
WORKING
WITH ADVANCE 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
How to Use 3-D Formula Across Worksheet
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
How to link information from different worksheets and workbook
How To Link Information Using Copy And Paste Link
How To Update Link
USING
ADVANCE FUNCTIONS
Using Financial Functions
How
To Use PMT Function
How To Use ABS Function
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/Hide Comment Indicators
How To Edit And Delete Comments
Sharing
Workbooks
How
To Enable Workbook Sharing
How To Disable Workbook Sharing
How
To Copies Of Shared Workbook
How
To Track Changes In Shared Workbook
How
To Accept Or Reject Changes
EXERCISES
Exercise - Validating, Protecting,
Auditing Form And Template
Exercise - Using Advance Formatting, Style And Outlining
Exercise - Working With Multiple Worksheets And Workbooks
Exercise - Using Advance Functions
Exercise - Using Workgroup Features
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
Sorting
Records
How
To Perform Simple Sort
Using
AutoFilter
How
To Enable / Disable AutoFilter
How To Apply / Remove The Criteria In The Database Using AutoFilter
How To Customize Criteria In AutoFilter
How To Use Wildcard In Criteria
Using
Advance Filter
How
To Define The Criteria Range Example Of The Criteria Range
How To Use The Advance 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
PIVOT TABLE AND ANALYZING DATA
Creating Pivot Table
How
To Create A Pivot Table
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
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
EXERCISES
Exercise - Creating Powerful Database
Exercise -
Creating Pivot Table And Analyzing Data
Exercise - Automating Routine Tasks
With Macros
Exercise -
Importing And Exporting Data
©2003
EBook Publishing. All rights reserved.
Web Site: http://www.computertrainingmanual.com
Email: info@computertrainingmanual.com
|