Course: MS Excel Automation
Author: Lauri Matson
ISBN: 1-935320-28-9
Release Date: 2009-01-30
Duration: 5 hrs / 71 tutorials
VTC's MS 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
1) Introduction
Course Objectives
Command Reference (2003 - 2007)
2) Time Saving Techniques
Shortcuts & Quick Tips
Auto Correct Options
3) Validation & Protection
Introducing Data Validation
Creating a List with Validation
Setting up Protection
4) Named Ranges
Defining a Name
Editing & Using Names
Name Manager
5) Security
Adjusting Security Levels
Using Macro Extension
6) Introduction to Macros
Tools Menu vs. Developer Toolbar
Storing Macros
Working with a Startup Folder
7) Macros
Getting Started
Absolute vs. Relative Reference
8) Practice with Macros
Creating a Routine
Automating Formats
Creating a Template for Headers/Footers
9) Understanding Macro Code
Editing Macros with VBA
Introduction to Reading Code
10) Buttons vs. Command Buttons
Creating a Button
Creating a Command Button
11) The VBA Screen
The Explorer & Properties
Objects & User Forms
12) Creating a Routine
Creating an Object pt.
Creating an Object pt. 2
Adding Code to an Object
13) Adding Controls
Creating Dropdown Lists
Viewing the Code
14) Designing a Pop-Up Calendar
Creating the Calendar Form
Writing the Code
15)Writing Code
Coding with VBA
Examples
16) Formulas & Functions
Formulas vs. Functions
Tips for Formulas
17)Three Dimensional Formulas
Formulas from Other Worksheets
Linking External Workbooks
18) Popular Functions
Conditional Statements
Nested Statements
Introduction to V-Lookup
Tips for Working with V-Lookup
19) VBA & Functions
Using Functions
Creating a Custom Function
20) Using an Add-In
Explaining Add-In
Creating an Add-In for a Custom
Using a Function with Goal Seek
21) Pivot Tables
Using Pivot Tables
Working with Outside Data
Formats & Features of Pivots
22) Automating Pivot Tables
Adding a Pivot Chart
Macros & Code for Automation
23) Advanced Charts & Graphs
Single Point Charts
Specialty Charts
Combination Charts
24) Automating Charts & Graphs
Creating a Dynamic Chart
The Offset Command
25) Pivots & Charts in Legacy Versions
Creating Pivots in Earlier Versions
Charts & Graphs in Prior Versions
26)Working with Other Programs
Linking to PowerPoint
Dressing up Your Spreadsheets
27) Importing/Exporting Data
Working with External Data
Exporting from Excel
28) Analysis Tools
Understanding the Add-In Tools
Descriptive Statistics
Histogram
29) Looking for Help
The Help Menu for Excel & VBA
The Microsoft Interactive Tool
30) Wrap Up
Conclusion
About the Author
31) Exercise Files
http://rapidshare.com/files/244697111/1.rar
http://rapidshare.com/files/244704748/2.rar
http://rapidshare.com/files/244713528/3.rar
http://rapidshare.com/files/244942168/4.rar
http://rapidshare.com/files/245051010/5.rar
http://rapidshare.com/files/246240970/6.rar
http://rapidshare.com/files/246243966/7.rar
http://rapidshare.com/files/246245767/8.rar
http://rapidshare.com/files/246254237/9.rar
http://rapidshare.com/files/246256921/10.rar
http://rapidshare.com/files/246266327/11.rar
http://rapidshare.com/files/246270852/12.rar
http://rapidshare.com/files/246274491/13.rar
http://rapidshare.com/files/246297110/14.rar
http://rapidshare.com/files/246322839/15.rar
for your conveyniance all rar files are separately playable. each rar files covers 2 topic except the last one which contains exercise file too.
Hopefully you will find it useful
Author: Lauri Matson
ISBN: 1-935320-28-9
Release Date: 2009-01-30
Duration: 5 hrs / 71 tutorials
VTC's MS 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
1) Introduction
Course Objectives
Command Reference (2003 - 2007)
2) Time Saving Techniques
Shortcuts & Quick Tips
Auto Correct Options
3) Validation & Protection
Introducing Data Validation
Creating a List with Validation
Setting up Protection
4) Named Ranges
Defining a Name
Editing & Using Names
Name Manager
5) Security
Adjusting Security Levels
Using Macro Extension
6) Introduction to Macros
Tools Menu vs. Developer Toolbar
Storing Macros
Working with a Startup Folder
7) Macros
Getting Started
Absolute vs. Relative Reference
8) Practice with Macros
Creating a Routine
Automating Formats
Creating a Template for Headers/Footers
9) Understanding Macro Code
Editing Macros with VBA
Introduction to Reading Code
10) Buttons vs. Command Buttons
Creating a Button
Creating a Command Button
11) The VBA Screen
The Explorer & Properties
Objects & User Forms
12) Creating a Routine
Creating an Object pt.
Creating an Object pt. 2
Adding Code to an Object
13) Adding Controls
Creating Dropdown Lists
Viewing the Code
14) Designing a Pop-Up Calendar
Creating the Calendar Form
Writing the Code
15)Writing Code
Coding with VBA
Examples
16) Formulas & Functions
Formulas vs. Functions
Tips for Formulas
17)Three Dimensional Formulas
Formulas from Other Worksheets
Linking External Workbooks
18) Popular Functions
Conditional Statements
Nested Statements
Introduction to V-Lookup
Tips for Working with V-Lookup
19) VBA & Functions
Using Functions
Creating a Custom Function
20) Using an Add-In
Explaining Add-In
Creating an Add-In for a Custom
Using a Function with Goal Seek
21) Pivot Tables
Using Pivot Tables
Working with Outside Data
Formats & Features of Pivots
22) Automating Pivot Tables
Adding a Pivot Chart
Macros & Code for Automation
23) Advanced Charts & Graphs
Single Point Charts
Specialty Charts
Combination Charts
24) Automating Charts & Graphs
Creating a Dynamic Chart
The Offset Command
25) Pivots & Charts in Legacy Versions
Creating Pivots in Earlier Versions
Charts & Graphs in Prior Versions
26)Working with Other Programs
Linking to PowerPoint
Dressing up Your Spreadsheets
27) Importing/Exporting Data
Working with External Data
Exporting from Excel
28) Analysis Tools
Understanding the Add-In Tools
Descriptive Statistics
Histogram
29) Looking for Help
The Help Menu for Excel & VBA
The Microsoft Interactive Tool
30) Wrap Up
Conclusion
About the Author
31) Exercise Files
http://rapidshare.com/files/244697111/1.rar
http://rapidshare.com/files/244704748/2.rar
http://rapidshare.com/files/244713528/3.rar
http://rapidshare.com/files/244942168/4.rar
http://rapidshare.com/files/245051010/5.rar
http://rapidshare.com/files/246240970/6.rar
http://rapidshare.com/files/246243966/7.rar
http://rapidshare.com/files/246245767/8.rar
http://rapidshare.com/files/246254237/9.rar
http://rapidshare.com/files/246256921/10.rar
http://rapidshare.com/files/246266327/11.rar
http://rapidshare.com/files/246270852/12.rar
http://rapidshare.com/files/246274491/13.rar
http://rapidshare.com/files/246297110/14.rar
http://rapidshare.com/files/246322839/15.rar
for your conveyniance all rar files are separately playable. each rar files covers 2 topic except the last one which contains exercise file too.
Hopefully you will find it useful
