mentor training
Excel Tips & Tricks

This 6 hour course is designed for the experienced Excel user. Participants will learn time-saving tools and techniques and will use several of Excel's most powerful advanced functions. Goals of the class include enhancing the intermediate-to-advanced Excel users productivity and problem- solving abilities. To get the most out of this course, participants should have some experience and familiarity with Excel functions such as SUM, IF, VLOOKUP, and SUBTOTAL, and should understand features and concepts such as Nested Functions, Tables, Named Ranges, and Data Filters.

Course prerequisites: Excel 2010/2013 Level 2 or equivalent experience.

Working with Tables
Creating Tables Adding columns and rows Using Table Styles Sorting and Filtering Structured References External references to Tables Converting Tables to Ranges

User Interface Tools and Techniques
Ribbon topics Status Bar topics Formula Bar topics Function / Argument dialog boxes Recent Documents List Document Properties Mini toolbar Shortcut keys and clicks

Customizing Excel
Simple customization options Custom Styles Creating a new default Workbook Customizing the Quick Access Toolbar Customizing My Places

Importing Data
Getting data from a Database or Query Fixing problems with imported data

Formatting Tools and Techniques
Using Office Themes Conditional Formatting presets Conditional Formatting formulas Custom number formats Rounding numbers Watermarks Hide rows and columns by Grouping

Functions and Formulas
Understanding Absolute References Using Arrays Using Data Validation Lists in Formulas Working with the Name Manager
Advanced Sum Functions
SUMPRODUCT SUMIFS
Advanced Lookup Functions
VLOOKUP INDEX and MATCH Two-column Lookups Array formulas
Error Functions
ISERROR ISNA IFERROR
Formula Auditing

Other Tools and Techniques
Joining and Separating Data
CONCATENATE Text to Columns Remove Duplicates
Go To Special
Paste Special Goal Seeking Filling in the Gaps Using a Reverse PivotTable