Tutorials and Online Training Videos

Microsoft Excel Automation Tutorial

VTC's Microsoft Excel Automation course is intended for intermediate and advanced users who wish to increase their efficiency, accuracy, and productivity. Students should have already completed Excel introduction courses or have equivalent knowledge. Created for all versions of Excel, this course will help students master the art of spreadsheet design and will provide comprehensive instruction in the automation and simplification of even the most complex data. Expanding upon our basic Excel courses, Lauri Matson explores the more advanced features of Excel such as VLookup functions, validation, pivot tables, analysis tools, charting, and more. To begin learning Excel Automation today, simply click on the movie links.


Course Objectives
Command Reference (2003 - 2007)

Time Saving Techniques

Shortcuts & Quick Tips
Auto Correct Options

Validation & Protection

Introducing Data Validation
Creating a List with Validation
Setting up Protection

Named Ranges

Defining a Name
Editing & Using Names
Name Manager


Adjusting Security Levels
Using Macro Extension

Introduction to Macros

Tools Menu vs. Developer Toolbar
Storing Macros
Working with a Startup Folder


Getting Started
Absolute vs. Relative Reference

Practice with Macros

Creating a Routine
Automating Formats
Creating a Template for Headers/Footers

Understanding Macro Code

Editing Macros with VBA
Introduction to Reading Code

Buttons vs. Command Buttons

Creating a Button
Creating a Command Button

The VBA Screen

The Explorer & Properties
Objects & User Forms

Creating a Routine

Creating an Object pt. 1
Creating an Object pt. 2
Adding Code to an Object

Adding Controls

Creating Dropdown Lists
Viewing the Code

Designing a Pop-Up Calendar

Creating the Calendar Form
Writing the Code

Writing Code

Coding with VBA

Formulas & Functions

Formulas vs. Functions
Tips for Formulas

Three Dimensional Formulas

Formulas from Other Worksheets
Linking External Workbooks

Popular Functions

Conditional Statements
Nested Statements
Introduction to V-Lookup
Tips for Working with V-Lookup

VBA & Functions

Using Functions
Creating a Custom Function

Using an Add-In

Explaining Add-In
Creating an Add-In for a Custom
Using a Function with Goal Seek

Pivot Tables

Using Pivot Tables
Working with Outside Data
Formats & Features of Pivots

Automating Pivot Tables

Adding a Pivot Chart
Macros & Code for Automation

Advanced Charts & Graphs

Single Point Charts
Specialty Charts
Combination Charts

Automating Charts & Graphs

Creating a Dynamic Chart
The Offset Command

Pivots & Charts in Legacy Versions

Creating Pivots in Earlier Versions
Charts & Graphs in Prior Versions

Working with Other Programs

Linking to PowerPoint
Dressing up Your Spreadsheets

Importing/Exporting Data

Working with External Data
Exporting from Excel

Analysis Tools

Understanding the Add-In Tools
Descriptive Statistics

Looking for Help

The Help Menu for Excel & VBA
The Microsoft Interactive Tool

Wrap Up

About the Author

Related Books on Microsoft Excel Automation

Save $80 on the
by Motorola with Verizon Wireless service for less than from Verizon Wireless directly.
Save $80 on DROID BIONIC
More Cell Phone Discounts