Who should attend
This class is for users who are already proficient in formulas, functions, cell references, charts, and basic pivot tables.
Even if you're already a "power user" with a few pivot tables under your belt and a good grasp of charts and other standard Excel topics, there are a few next-level features and techniques that truly distinguish the Excel pros. With a little guidance from one of our amazing trainers, these features won't be hard for you to pick up and will significantly enhance your spreadsheets:
Lookups. In a database like MS Access you can look up data by joining tables along shared fields, like a customer ID in both the Customers and Orders table. This feature also exists in Excel: you can look up values in one worksheet and put them in another worksheet based on a related field: for example, look up the sales figure adjacent to a cell containing "January" in worksheet B, and put it into worksheet A adjacent to the cell containing "January." We'll show you how to do this and troubleshoot common problems.
Pivot Table Enhancements. Need to customize a pivot table to add extra info? Excel provides a number of features to add calculated values, and data groupings into otherwise vanilla pivot tables. To help users change the way your pivot table displays, you can add timelines and "slicers" to select only certain types of data or specific date ranges.
Charts and Advanced Formatting. The choice of chart type can make a huge difference in how easy it is to glean insights from your data. We'll show you how to choose the right charts based on your data. Have two types of data in the same chart (e.g., temperature and rainfall) with different units? You'll learn how to add secondary axes using "combo charts" and get them to display exactly as you want. Along the way you'll master some advanced conditional formatting rules.
Macros and VBA. Macros are recorded steps that can be memorized for later playback. For example, if you find yourself formatting all your tables the same way (frozen header rows, banded rows and thin gray border), you might want to record those formatting steps and then be able to reapply them to any new table. These steps are actually saved in Excel's internal programming language, VBA (Visual Basic for Applications). Once you've recorded your steps, you can dive into the behind-the-scenes VBA code that activates those steps and customize them even further.
Module 1: Advanced Lookups and Pivot Tables
Learning Outcomes:
1. Look up values between worksheets based on matching criteria and resolve common errors.
2. Apply logical functions to have Excel make decisions about what values to return.
3. Enhance your pivot tables with a suite of advanced features.
Topics:
Advanced Lookup and Related Useful Functions
Advanced Pivot Table Techniques
Module 2: Arrays, Advanced Charts and Macros
Learning Outcomes:
1. Create array functions for complex searches and calculations.
2. Set up, edit, and manage Excel charts
3. Create and modify a macro library for repetitive tasks in Excel
Topics:
Arrays
Advanced Conditional Formatting
Advanced Charting
Macros
Skills covered
"It was fun and practical."
"This was a great course. I wish I had taken it years ago. Thank you!"
