Excel Training Full Course - Macros, VBA with AI, Power Query & Lookups (August 2026)
Full 2-day Excel training, both days in one video. Day 1 covers COUNTIF and SUMIFS, data validation, Advanced Filter, IF statements with AND and OR, recording macros, and using AI to write VBA that builds a working dashboard. Day 2 covers Power Query end to end - cleaning, unpivoting, conditional columns and combining files from a folder - then the data model, formula-driven conditional formatting, and the full lookup family including XLOOKUP and INDEX MATCH.
Recorded 26-27 August 2026.
Chapters:
00:00 Introduction
00:10 [Day 1 Morning] Course overview and what will be covered
21:27 COUNTIF and COUNTIFS
26:54 Data validation - drop-down lists
29:08 Advanced Filter - unique records and helper cells
42:17 Criteria operators - not equal to, greater than, joining with &
48:41 COUNTBLANK and COUNTA
51:12 SUMIFS - totals by criteria
1:02:24 Wildcards in SUMIFS criteria
1:09:48 IF statements and logical tests
1:14:33 IF with blank and non-blank cells
1:21:55 Nested IF
1:37:49 AND and OR functions
1:47:13 IF with AND / OR - bonus rules
1:58:15 Macro-enabled workbook and the Developer tab
2:08:45 Recording, running and editing a macro
2:20:09 Excel to Word - AI-written VBA for lease agreement PDFs
2:54:36 [Day 1 Afternoon] Session start
3:02:47 Fixing VBA errors with AI
3:05:01 Macro-enabled dashboard project setup
3:07:20 Prompt strategy - isolating the data you send
3:12:19 AI analysis of the sales dataset
3:16:06 Six pivot tables via VBA, and debugging
3:27:20 Sharing headers to fix field-name errors
3:32:03 Dashboard KPI header module
3:39:19 Charts via VBA and layout spacing
3:53:27 Slicers and final dashboard layout
4:08:46 Rebuilding the dashboard on new data
4:16:14 VBA case study - columns into rows
4:21:53 Moving the workbook into Google Sheets
4:24:53 Drop-down lists and the IFS function
4:36:30 Conditional formatting rules and locking ranges
4:50:14 Icon sets, colour scales and data bars
5:07:40 [Day 2 Morning] Session start
5:08:39 Power Query and the ETL concept
5:14:25 Launching Power Query from a table
5:20:25 Remove duplicates, split and merge columns
5:26:16 Close and Load, and the Queries pane
5:31:35 Choosing columns, renaming headers, removing errors
5:37:23 Deleting tables vs queries, reloading output
5:41:13 Refreshing a query when the data changes
5:46:10 Promoting headers and setting data types
5:53:15 Adding an index column
6:04:40 Replacing nulls and filling blank cells
6:20:08 Unpivot columns
6:24:26 Text to Columns, delimiters and trim
6:30:26 Conditional columns and custom formulas
6:46:22 Combining files from a folder
7:01:22 Excel.Workbook binaries, expand and data types
7:17:35 Adding or removing files and refreshing
7:24:52 Connection-only queries for large data
7:30:38 Add to Data Model and pivot from the model
7:41:15 Guided folder-combine practice
7:46:40 Power Query inside Power BI
7:51:26 Cleaning messy dates and regions
7:58:27 Open Q&A on participants' own data
8:14:05 [Day 2 Afternoon] Session start
8:15:40 Power Query and the Power Pivot data model
8:22:46 Conditional formatting with formulas
8:27:16 Highlighting multiple values with OR
8:31:33 Multi-criteria highlighting with AND
8:39:10 Absolute vs relative references in rules
8:46:40 Dynamic highlighting from a typed cell
8:59:33 ISNUMBER and SEARCH for partial text
9:05:26 Rule order and precedence
9:19:42 VLOOKUP - exact vs approximate match
9:29:27 XLOOKUP basics
9:38:27 Multi-column and two-way XLOOKUP
9:44:55 INDEX MATCH as an XLOOKUP alternative
9:57:25 Approximate match and grade bands
10:05:00 XLOOKUP match modes and if-not-found
10:09:28 Google Sheets and Apps Script