Microsoft Excel Advanced Level

Training Course Outline:

1). IMPORTING DATA
IMPORTING DATA FROM EXTERNAL SOURCES
Using Database Terminology
Importing Data into Excel
IMPORTING TEXT FILES
Importing text files into Excel
Importing text using the Text Import Wizard
Refreshing data from imported text files
USING MICROSOFT QUERY
Adding a Data Source
Creating a Query
QUERYING DATA FROM THE WEB
Using Web Queries
Accessing the New Web Query dialog box
Creating a new Web Query
Saving a Web Query
Setting Web Query options
Running a saved Web Query
Refreshing a Web Query
REFRESHING DATA
Refreshing external data without losing the formatting
Refreshing external data automatically

2). TEMPLATES AND STYLES

WORKING WITH TEMPLATES
Using Templates
Creating a Template
Applying Templates
WORKING WITH STYLES
Using Styles
Creating a Style
Applying a Style
Editing a Style
Deleting a Style
Copying Styles from another Workbook

3). ANALYZING DATA
Pivot tables
WHAT-IF ANALYSIS
Using What-If Analysis
GOAL SEEK
Using Goal Seek
Applying Goal Seek
DATA TABLES
Using Data Tables
Creating a one-variable Data Table
Creating a two-variable Data Table
Speeding up calculations with Data Tables
SCENARIO MANAGER
Using Scenario Manager
Adding a Scenario
Showing a Scenario
Deleting a Scenario
Editing an existing Scenario
Summarizing Scenarios
SOLVER
Using Solver
Installing Solver
Applying Solver
Changing a Constraint
Deleting a Constraint

4). MACROS AND CUSTOM CONTROLS
This is a basic introduction to Macros. Use of the Visual Basic Editor is covered in the Excel VBA course.
WORKING WITH MACROS
Using Macros
Recording a Macro
Assigning a shortcut key to your Macro
RUNNING MACROS
Running a Macro using the main menu
Running a Macro using a shortcut key
Running a Macro using a button
ADDING CUSTOM CONTROLS
Working with Buttons
Creating a new Button
Renaming a Button
Formatting Buttons
Linking an existing Macro to a Button
Deleting a Button

5). SECURITY AND PROOFING
SECURITY FEATURES
Working with Security Features
PROTECTING EXCEL FILES
Specifying a password for opening a workbook
Specifying a password for saving a workbook
Removing a Workbook Password
Using the Read-only option
Creating backup files automatically
WORKSHEET PROTECTION
Protecting Worksheets
Un-Protecting Worksheets
Protecting Cells
WORKING WITH DIGITAL SIGNATURES
Using Digital Signatures
Signing a File
Removing a digital signature from a file
CHECKING SPELLING
Using the Spell Checker
USING DATA VALIDATION
Setting data validation
Creating the Input Message
Displaying an Input Message
Creating the Error Alert
Displaying an Error Alert
USING TEXT TO SPEECH
Reading back a group of cells
Using Speak On Enter
Turning off Speak On Enter
DOCUMENTATION
Working with Comments
Adding a Comment
Displaying a Comment
Editing a Comment
Deleting a Comment

6). CUSTOMIZING EXCEL XP
CONFIGURING EXCEL XP OPTIONS
Customizing Excel Options
Customizing View Options
Customizing Calculation Options
Customizing Edit Options
Customizing General Options
Customizing Transition Options
Customizing Custom Lists Options
Customizing Chart Options
Customizing Color Options
Customizing International Options
Customizing Save Options
Customizing Error Checking Options
Customizing Spelling Options
Customizing Security Options
CUSTOMIZING THE EXCEL ENVIRONMENT
Displaying Toolbars using the Customize dialog box
Creating a Custom Toolbar
Adding a Menu to a Toolbar
Attaching a Toolbar
Deleting a Custom Toolbar
Resetting a Menu
Resetting a Toolbar
WORKING WITH WORKSPACES
Using Workspace files
Saving your Workspace

7). COLLABORATION AND CONSOLIDATION
SHARING WORKBOOKS
Using Shared Workbooks
Allowing Workbook Sharing
Customizing Workbook Sharing Options
Comparing and Merging Shared Workbooks
SENDING WORKBOOKS
Sending Workbooks for Review
Sending a Worksheet as Attachment
Routing a Workbook
Routing a Workbook to the next recipient
CONSOLIDATION
Consolidating data over several Worksheets
Consolidating selected rows and columns from several Worksheets