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.
Introduction
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
Security
Adjusting Security Levels
Using Macro Extension
Introduction to Macros
Tools Menu vs. Developer Toolbar
Storing Macros
Working with a Startup Folder
Macros
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
Examples
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
Histogram
Looking for Help
The Help Menu for Excel & VBA
The Microsoft Interactive Tool
Wrap Up
Conclusion
About the Author