Excel Power Skills: Lookups, Pivots, Arrays & Automation

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.

Mastering Lookups & Pivot Table Insights

·      Unlocking the Power of XLOOKUP

·      Two-Way Lookups with INDEX & MATCH

·      Using IF/IFS Logic to Control Results

·      Essential Date & Text Functions

·      Pivot Tables Outside Formulas Building

·      Calculated Items & Fields

·      Group Pivot Data

Arrays, Dynamic Charts & Macro Automation

·      Array Formulas for Single & Multiple Results

·      Key Array Functions for Analysis

·      Advanced Conditional Formatting

·      Combo Charts for Multi-Dimensional Data

·      Developer Tools

·      Using Radio Buttons for Dynamic Charts

·      Adding Checkboxes

·      Recording Macros for Reusable Steps

·      Accessing & Editing Macros

·      Building Custom Macros with VBA