|
|
|
|
Course Description
|
The
Microsoft Excel 2000 Level 3 course is designed for the Excel user
who may be asked to create advanced spreadsheet applications for
others to use. Because of this assumption, there is a direct
focus on security and user friendliness. This course examines
using controls such as drop down boxes and check boxes to increase
the level of user friendliness and reduce the quantity of end-user
errors. Advanced functions including VLOOKUP and INDEX are
explored to increase the usability of the application. The
students will learn basic macros which may be used to automate
repetitive tasks. The course also examines some of the
underused, yet useful, features of Microsoft Excel 2000. These
include Named Ranges, Consolidation, and Pivot Tables.
|
Course
Objectives
|
- To learn how to protect your
workbook applications
- To use Templates
- To utilize controls such as
drop-down and check boxes
- To create Macros to execute
repeated tasks
- To use Named Ranges
effectively
- To consolidate data
- To organize my data into
Pivot Tables for easy manipulation
|
Duration
The duration of the course is
one day. This day begins at 8:30 am and ends at 4:30 pm.
Table
of Contents
|
I |
Customizing
Excel
|
|
|
A. |
The
Option Settings |
|
|
B. |
The
Customize Settings |
|
II |
Workbook
Templates |
|
|
A. |
What
Are Templates? |
|
|
B. |
Creating
a Template |
|
|
C. |
Using a Workbook Template |
|
|
D. |
More on Storing Workbook Templates |
|
|
E. |
Using Form Field Controls |
|
III |
Lookup
& Reference Functions |
|
|
A. |
VLOOKUP |
|
|
B. |
The
Index Function |
|
IV |
Security
within Excel |
|
|
A. |
The
Security Features within Excel |
|
|
B. |
Shared
Workbooks |
|
|
C. |
Data
Validation |
|
|
D. |
Workbook Protection |
|
|
E. |
Cell Protection |
|
|
F. |
Worksheet Protection |
|
|
G. |
Hiding Rows/Columns &
Worksheets |
|
V |
Using
Named Ranges |
|
|
A. |
The
Concept |
|
|
B. |
Creating
Named Ranges |
|
|
C. |
Applying Names to Formulas |
|
VI |
Working
with Macros |
|
|
A. |
Macros
Defined |
|
|
B. |
Creating
Command Macros |
|
|
C, |
Running a Macro |
|
|
D. |
Adding Macros an an Application
Button |
|
|
E. |
Adding Macros to a Toolbar |
|
VII |
Consolidation
of Data |
|
|
A. |
What
Is Consolidation of Data? |
|
|
B. |
Consolidating
by Using 3-D References |
|
|
|
C. |
Consolidate
Data by Position |
|
|
D. |
Working
with Outline View Controls |
|
|
E. |
Consolidation
by Category |
|
|
F. |
Difference
between Consolidation by Position & Category |
|
VIII |
Pivot
Tables |
|
|
A. |
What
Is a Pivot Table? |
|
|
B. |
What
Is the Source of Data? |
|
|
C. |
Creating a Pivot Table |
|
|
D. |
Parts of a Pivot Table |
|
|
E. |
Basic Changes to Pivot Tables |
|
|
|
Relevant Links
|
