Advanced Excel: Formulas, Pivot Tables and Dashboards
Dynamic arrays, Power Query and interactive dashboards.
Taught by Niraj Thapa · Analytics Lead
What you'll learn
- Write dynamic array formulas with FILTER, SORT, UNIQUE, SEQUENCE and XLOOKUP that spill and update automatically
- Structure complex calculations with LET and package reusable logic as named LAMBDA functions
- Build Power Query pipelines that import, clean, unpivot and merge data and refresh on demand
- Combine multiple files from a folder and parameterise a query so paths and dates are not hard-coded
- Load data to the Data Model, create pivot tables with measures and connect slicers across visuals
- Design an interactive dashboard with a clear layout, consistent formatting and documented refresh steps
About this course
Take Excel from a calculator to an analysis platform. This intermediate course covers the advanced Excel features that professional analysts use every day in Microsoft 365: dynamic array formulas such as FILTER, SORT, UNIQUE and SEQUENCE; LET and LAMBDA for readable, reusable logic; Power Query for repeatable import, cleaning and combining of files and databases; pivot tables built on the Data Model with measures and slicers; and interactive dashboard design with consistent layout, charts and controls. It is for analysts, accountants and operations staff who already use SUMIFS, XLOOKUP and basic pivot tables and want to build reports that refresh with one click and survive handover to a colleague. You need Microsoft 365 Excel for Windows; Power Pivot and the Data Model are not available in Excel for Mac. By the end you will have a refreshable, documented dashboard driven by Power Query and a pivot model, plus a formula toolkit that replaces hours of manual work.
Course content
7 lessons- 1Dynamic Arrays: FILTER, SORT, UNIQUE and SEQUENCEWrite one formula that returns many results and updates as the data changes.Preview
- 2LET and LAMBDA: Readable, Reusable FormulasName intermediate steps, define your own functions and stop copying long formulas around.
- 3Power Query: Import, Clean and TransformRecord cleaning steps once and replay them on every new export with a single refresh.
- 4Power Query: Combining Files, Merging and ParametersAppend a folder of exports, merge lookup tables, and remove hard-coded paths.
- 5Pivot Modelling: Data Model, Measures and SlicersLoad queries to the Data Model, relate tables and write measures that pivots can use.
- 6Dashboard Design: Layout, Charts and InteractionArrange metrics, charts and controls so a reader finds the answer without instructions.
- 7Performance, Auditing and HandoverKeep large workbooks fast, find errors quickly and document the model so someone else can run it.