Advanced Excel
For analysts who need dependable Excel workbooks: combine lookups, named ranges and dynamic arrays into formulas others can read, audit and extend.
Free module
Complete module 1 without creating an account
The complete content is available on this page.
Start module 1 freeComplete and no account required · Advanced Excel
Module 1 · complete and free
Advanced Excel
It opens here, with no account or page change.
About this course
Suitable for analysts and advanced Excel users maintaining shared workbooks. You will build lookup logic with error handling, use named ranges and structured references, and replace fragile ranges with dynamic arrays. The result separates inputs, logic and outputs so a colleague can trace a figure quickly. This module teaches you how to design Excel workbooks that another analyst can open, understand, and audit in minutes. You will learn to combine lookup functions, named ranges, and modern dynamic array formulas to build logic that is transparent, resilient, and easy to extend. By the end, you will be able to structure a calculation layer that separates inputs, logic, and outputs cleanly. Converting a data range into a named Excel Table with `Ctrl+T` makes every formula and PivotTable that references it automatically include new rows on refresh, removing the need to manually extend ranges. Structured references such as `tblSales[Amount]` and `[@Amount]` replace fragile cell coordinates, making formulas readable and self-extending as the Table grows. Applying Data Validation dropdowns to categorical columns prevents inconsistent entries like variant spellings from corrupting pivot summaries, while a consistent naming convention using prefixes such as `tbl`, `rng`, and `pv_` lets anyone navigate the workbook immediately. This module teaches you how to use Power Query to import, clean, and combine data from recurring files with minimal manual effort. You will learn to build repeatable transformation steps that refresh automatically when your source data changes, plus simple automation techniques that save hours each week. By the end, you'll turn tedious data preparation into a one-click process. Keeping inputs, calculations, and outputs on separate sheets makes every number on a dashboard traceable to a clearly labelled source, and protects formulas from accidental overwriting by the audience. A dedicated QA sheet where each check is a formula returning `OK` or `CHECK` — covering totals reconciliation, error counts, date-range completeness, and category validation — catches mistakes before the file reaches a stakeholder. A README sheet containing the workbook's purpose, exact refresh steps, editable cells, known limitations, and an owner contact transforms a spreadsheet into a handoff-ready deliverable that the next person can operate without a walkthrough. A decision model workbook is structured into five layers — data, staging, model, controls, and presentation — where dependencies flow in one direction only, so a single Refresh All updates every chart, formula, and recommendation without manual intervention. User-adjustable assumptions such as target margin or selected product line are exposed through form controls linked to named cells on a dedicated Control sheet, keeping model formulas readable and preventing stakeholders from editing calculation logic directly.
What you'll learn
- Build lookup logic using XLOOKUP and INDEX/MATCH with graceful error handling
- Use named ranges and structured references to make formulas self-documenting
- Apply dynamic array functions such as FILTER, SORT, UNIQUE and SEQUENCE to replace fragile manual ranges
- Design a workbook with a clear separation between inputs, logic, and outputs that another analyst can audit
- By the end of this module, you'll be able to convert a raw data range into a named Excel Table so that PivotTables built from it update completely with a single right-click Refresh when new rows arrive.
- By the end of this module, you'll be able to write `SUMIFS` formulas using structured references such as `tblSales[Amount]` and `tblSales[Region]` instead of fixed cell coordinates so the formula never breaks when rows are added.
- By the end of this module, you'll be able to apply Data Validation dropdown lists sourced from a Table column to restrict categorical entries and prevent inconsistent values from corrupting pivot summaries.
How access works
Start 1 course for free. Upgrade when you're ready to unlock the rest.
1 course free
Start one Academy course immediately. No credit card required. Test Academy before committing.
Start freeFull course + every Academy tool
All modules, certificate on completion, career coach, interview prep and unlimited course generation — across every course.
See Premium+ plansOther courses you might like
Account Management Fundamentals
For account managers taking over a client relationship: turn handover notes into a clear account brief, credible next steps and a communication rhythm.
Accounting Fundamentals
Build a sound accounting habit from the start: learn how transactions change assets, liabilities and equity, then record them with clear double-entry logic.
Administrative Management
For administrators and team coordinators: build a filing system people can follow, with clear folders, consistent names and one reliable version of every document.