Marriott International – Excel Macro Programming for Advanced Excel Users
Description
Professionals who work with Excel every day will benefit from these advanced topics. This fast-paced program covers: range naming, decision-making IF and VLOOKUP functions, financial and other functions, Pivot Table reporting, controlling calculation inputs, securing files, formula auditing, external formula linking, many multiple worksheet tips and time-saving usage of the macro recorder. New features in Excel 2013 and 2016 will be covered.
Please note: The INDIVIDUAL EVENT PAGE is for additional course details only. Do NOT use it for registration.
Learning Objectives
Utilize advanced functions for IF testing and Lookup
Understand how to use the Excel Pivot Table feature to report data summaries in seconds
Incorporate a variety a multiple worksheet tips and external formulas linking
Apply range naming, formula auditing, conditional formatting and protecting features
Identify time saving and simplification tips using the macro recorder
Major Topics
Range names
Name Manager dialog box for quick cell/range identification and formula building
IF testing, nested function rules, decision making functions and lookup
Financial function capabilities
Date and text functions
Multiple criteria functions of SUMIFS, COUNTIFS, AVERAGEIFS
Pivot Table calculations • Conditional Formatting to identify data patterns and duplicates in color
Suppress the display of Excel error messages in cells
Use formula auditing tools to trace formula dependencies across sheets
Use the watch window to track calculations on a sheet
Use the Data Validation feature to control user input
Group and outline according to formula structure
Create and repair external formula links
Control calculation of external links with user prompt controls
Link from Excel to Word
Group worksheets to take action on more than one worksheet at the same time
Use 3-dimensional functions across sheets
Copy sheets in the same file or to a new file
Understand list design rules and the new Table feature
Use Pivot Tables to analyze data by category with subtotals
Learn Pivot Table tips for recalculation and duplicating Pivot Tables
Create a Pivot Table report of unique values, codes or categories from data
Create a calculated field in a Pivot Table
Use Excel commands to create multiple Pivot Tables automatically
Create a Pivot Table of multiple consolidated ranges
Record macro examples for printing, formatting, and data cleanup
Understand the variety of ways to run macros
See what’s new in Excel
Take away templates and references for after class use
Provider
Business Learning Institute
Course Level
Advanced
CPE Field of Study
Computer Software & Applications
6.0
Who Should Attend
CPAs and Professionals at all levels of an organization needing to increase their knowledge of Excel functions and features at an advanced level
