Advanced Excel users encounter repetitive tasks such as: reports that need to be produced, data that needs to be updated or tables that need to be formatted on a regular basis. Using “Macros” many of these routine tasks can be automated. Using advanced knowledge of Excel learned in this class they can record the steps or operations that they want to perform, and then press a button to repeat them over and over again (and again).
Prerequisite
Proficient knowledge of Microsoft Excel. This class is for non-programmers.
Target Student
This course is for individuals who use Excel 2016, 2013 or 2010 frequently and who need to automate routine tasks. Programming experience is not required.
Course Outline
Configuring Excel for Macros
- The Developer Tab
- Macro Security
- Trust Center
- Saving Files
Basic Macro Recording
- The “Record Macro” Window
- Rules for Macro Name
Running Macros
- Running a Macro from the Developer Tab
- Running a Macro Using a Shortcut Key
- Running a Macro from a Button
- Running a Macro from a Worksheet Object
Creating Dynamic Macros
- Absolute versus Relative References
- Effective Range Selection
- The Importance of the Cursor’s Position
- Handling Empty Cells in a Data Range
Using Functions and Formulas in Macros
- Creating dynamic formulas
- Filling Formulas
- Using Data Tables
- Deleting Empty Rows at the End of a Worksheet
- Functions with Dynamic Ranges
Working with Files
- The Personal Macro Workbook
- Using Hyperlinks
- Writing Macros that Work on Variable File Names
- Importing a Text File
Macros for Data Entry
- Creating a worksheet to use as a Data Entry Form
- Creating a macro to add the data to a table
Remote Classroom Instruction (RCI) available
Connection Email (Friday Before Class)
- The email with your class connection details is sent out by Noon (Eastern Time) the Friday before your class. Contact us at 1.800.288.8221 immediately if you do not receive your connection email by that deadline.
Course materials (2 Days Before Class)
- The class materials are shipped to the address you provided at the time of registration. You should receive the materials by Noon (Eastern Time) two (2) business days before your class. If you do not receive them by that deadline contact us immediately at 1-800-288-8221.
Minimum Requirements
- A computer running Windows
- High speed Internet connection (DSL or faster, about 75KB of bandwidth)
- Speaker phone or hands-free headset for your telephone
