Excel Advanced Features, Functions, Pivot Tables and the Macro Recorder
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.
- * 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
- * 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
Professional Area of Focus
CPE Field of Study
Who Should Attend
8890 McGaw Rd Columbia, MD 21045-4743View in Google Maps
8:00 am - 3:30 pm