Advanced Excel: Analysis, Models and Automation

By Learning Hub Academy8 lessons103 min

For people who already build reports in Excel. Look anything up with XLOOKUP and INDEX/MATCH, let one formula build a whole report with FILTER, SORT and UNIQUE, and use IFS, LET, text and date formulas with confidence. Then clean data with Power Query, analyse it with advanced pivot tables and what-if tools, build models others can trust, and record your first macro. Every lesson has a practice workbook with an answer key.

  • Data and reporting
  • Digital skills

What you'll learn

  • Use every part of XLOOKUP: messages for no match, nearest and last matches, several columns, wildcards and two-way lookups
  • Use INDEX and MATCH for older Excel, and find the cause of a failing lookup or error before hiding it
  • Build live lists and reports with UNIQUE, SORT, SORTBY, FILTER and SEQUENCE, and clear #SPILL!
  • Write clear logic with IFS, SWITCH, AND, OR and LET
  • Split and clean text, and work with dates, working days and month ends
  • Clean, combine and reshape data with Power Query so next month is one click
  • Answer harder questions with pivot tables: Show Values As, the Data Model, distinct counts and measures
  • Test decisions with Goal Seek, data tables, Scenario Manager and Solver
  • Build models others can check and trust: clear inputs, names, validation, protection and checks
  • Record, run and fix a simple macro, and know when to use Power Query or a script instead

Formulas that do more

  1. 1

    Lookups in Depth

    13 min11 text, 2 templates and a 5-question quiz

  2. 2

    Dynamic Arrays

    12 min8 text, 2 templates and a 5-question quiz

  3. 3

    Logic, Text and Dates

    13 min9 text, 2 templates and a 5-question quiz

Analysis

  1. 4

    Power Query: Clean Data Once

    14 min9 text, 2 templates and a 5-question quiz

  2. 5

    Advanced Pivot Tables

    13 min8 text, 2 templates and a 5-question quiz

  3. 6

    What-If Analysis

    13 min7 text, 2 templates and a 5-question quiz

Models and automation

  1. 7

    A Model Others Can Trust

    13 min8 text, 2 templates and a 5-question quiz

  2. 8

    Your First Macros

    12 min7 text, 3 templates and a 5-question quiz

The final assessment opens once every lesson is done.