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 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.
Highlights
Advanced Functions
Use range names and the new Name Manager dialog box for quick cell/range identification and formula building
Use IF testing, new nested function rules, decision making functions and lookup
Use financial function capabilities
Explore the use of date and text functions
Explore the use of the dfunctions based on criteria: DSUM, DCOUNT
Explore how the dfunctions work in Pivot Table calculations
Control Results
Apply Conditional Formatting to identify data patterns in color
Suppress the display of Excel error messages in cells
Use the formula auditing toolbar 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
External Formula Linking
Create and repair external formula links
Control calculating the external links with the new user prompt controls
Link from Excel to Word
Multiple Worksheet Features
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
Save a workspace file for quick opening of multiple files
Pivot Table Reporting
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
Dynamically use Excel commands to create multiple Pivot Tables automatically
Use Pivot Table options for automatic recalculation
Create a Pivot Table of multiple consolidated ranges
Automate repetitive steps in Excel
Record macro examples for printing and formatting
Record macro examples of data cleanup
Understand the variety of ways to run macros
Take away templates and references for after class use
