Course Objectives
You will automate your job tasks in Microsoft® Office Excel® 2010.
Prerequisites
Knowledge of the basics of Excel, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data. Students are recommended to first take the following courses or have equivalent knowledge:
- Microsoft® Office Excel® 2010: Level 1
- Microsoft® Office Excel® 2010: Level 2
- Microsoft® Office Excel® 2010: Level 3
- Microsoft® Office Excel® 2010: Level 4
Target Student
This course is intended for advanced Microsoft Excel professionals that need to automate Excel spreadsheet tasks using Visual Basic for Applications (VBA).
Hardware Requirements
For this course, you need one computer for each student and one for the instructor. Each computer will need the following minimum hardware configuration:
- A PC with a Pentium® processor, at least 300 MHz.
- 10 GB of hard disk space or larger. You should have at least 1 GB of free hard disk space available for Office installation.
- A minimum of 64 MB of RAM with 1 GB of free hard disk space.
- A CD-ROM drive.
- Keyboard and mouse or other pointing device.
- 1024 x 768 resolution monitor is recommended.
- Network cards and cabling for local network access.
- Internet access (contact your local network administrator).
- A printer (optional) or an installed printer driver.
- A projection system to display the instructor’s computer screen.
Software Requirements
Each computer requires the following software:
- Microsoft® Office Professional Plus 2010 Edition
- Microsoft® Windows 7 (Professional Edition)
- Windows Rights Management Services (RMS) Client with Service Pack 2.
Performance-Based Objectives
Upon successful completion of this course, students will be able to:
- develop a macro.
- format worksheets using macros.
- create an interactive worksheet.
- work with multiple worksheets.
- perform calculations.
Course duration
3 Days
Course outline
Lesson 1: Developing Macros
Topic1A: Create a Macro Using the Macro Recorder
Topic1B: Edit a Macro
Topic1C: Debug a Macro
Topic1D: Customize the Quick Access Toolbar and Hotkeys
Topic1E: Set Macro Security
Lesson 2: Formatting Worksheets Using Macros
Topic2A: Insert Text
Topic2B: Format Text
Topic2C: Sort Data
Topic2D: Duplicate Data
Topic2E: Generate a Report
Lesson 3: Creating an Interactive Worksheet
Topic3A: Determine the Dialog Box Type
Topic3B: Capture User Input
Lesson 4: Working with Multiple Worksheets
Topic4A: Insert, Copy, and Delete Worksheets
Topic4B: Rename Worksheets
Topic4C: Modify the Order of Worksheets
Topic4D: Print Worksheets
Lesson 5: Performing Calculations
Topic5A: Create User-Defined Functions
Topic5B: Automate SUM Functions
|
