Excel Power User

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

Topics:

Advanced Lookup and Related Useful Functions

• XLOOKUP and VLOOKUP Functions
• Troubleshooting with IFERROR for Secondary Lookup Table
• Two-way Lookup with INDEX and MATCH
• Using IF Logic to Choose Return Values
• Core Date and String Functions: MONTH, TEXT, MID

Advanced Pivot Table Techniques

• Report Page Filters
• Building Outside Formulas
• Calculated Items and Fields
• Grouping: by Date and Manually
• Alternate Data Views with "Show Values As" Menu
• Timelines and Slicers

Module 2: Arrays, Advanced Charts and Macros

Topics:

Arrays

• Creating Array Formulas for Single Value Output
• Array Formulas for Multiple Value Output
• Useful Array Functions

Advanced Conditional Formatting

• Highlighting an Entire Row
• Highlighting Based on Logical Tests
• Implementing Banded Rows

Advanced Charting

• Recommended Charts
• Choosing Appropriate Charts for Different Data Types
• Combo Charts
• Wrangling Existing Charts to Display What You Want

Macros

• Recording Macros
• Saving Macros to your Personal Macro Workbook
• Accessing and Editing Your Macros
• Using the "Step Into" Feature with the VBA Editor to Debug
• Saving a Workbook Containing VBA Script