Excel Power User

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

What We'll Explore:

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.

4. Create array functions for complex searches and calculations.

5. Set up, edit, and manage Excel charts

6. Create and modify a macro library for repetitive tasks in Excel