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 will be emphasized. Participants will receive take-aways. (Format: “hands-on, instructor-led group training”)
Course ID: EXCELA-L
Excel Advanced Features, Functions, Pivot Tables and the Macro Recorder
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
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
Fields of Study
Computer Software & ApplicationsPrerequisites
Intermediate knowledge of Microsoft Excel