Excel Advanced Features, Functions, Pivot Tables and the Macro Recorder

Date: December 12, 2017 Time: 8:00 am - 3:30 pm EST Location: Columbia, MD CPE: 8 Format: In-Person
EVENT 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. Participants will receive take-aways.

Please note this course will be hands-on. We recommend bringing your own laptop to this program with excel loaded. You can attend without one but you would not be able to participate during the hands on exercises.

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
EVENT DETAILS
PROFESSIONAL AREAS OF FOCUS
Business & Industry Technology
CPE FIELD OF STUDY
Computer Science* (8 Credits)
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
INSTRUCTOR(S)
Judith Borsher
PREREQUISITES
Intermediate knowledge of Microsoft Excel
COURSE LEVEL
Advanced
LOCATION
Loyola University Maryland Graduate Center Columbia Campus 8890 McGaw Rd, Columbia, MD 21045, USA
Loading