Workplace Productivity and Office Skills · Spreadsheets
Advanced Excel: formulas, macros and Power Query
There is a point where a spreadsheet user becomes the colleague everyone brings broken workbooks to. Advanced Excel is that ground: nested logic, XLOOKUP and INDEX with MATCH, PivotTables with slicers, Goal Seek and Scenario Manager, recorded macros and, beyond the exam, Power Query and LAMBDA. MO-211 examines most of that list in four weighted domains. This page shows what each domain covers and where Excel should hand over to proper data tools.
Why this topic exists: Dynamic arrays, LET, what-if analysis, advanced PivotTables and recorded macros: the MO-211 Expert blueprint's four domains; Power Query, LAMBDA and hand-written VBA sit beyond the outline but inside the job, where a spreadsheet user becomes the office's analyst without leaving Excel.
Advanced Excel is less about obscure functions than about workbooks that answer new questions without being rebuilt. The shift comes when a user stops asking how to total a column and starts asking how to make the total right for whichever region, month or scenario the reader picks. Microsoft's Expert exam, MO-211, draws the line in four domains, weighted as follows in its published skills outline.
| MO-211 domain | Weight | Examples from the skills outline |
|---|---|---|
| Manage workbook options and settings | 10–15 % | Copying and enabling macros, referencing other workbooks, protecting sheets and workbook structure, calculation options |
| Manage and format data | 30–35 % | Flash Fill, RANDARRAY, custom number formats, data validation, subtotals, conditional formatting driven by formulas |
| Create advanced formulas and macros | 25–30 % | IFS, SWITCH, the conditional aggregates and LET; XLOOKUP and INDEX with MATCH; Goal Seek and Scenario Manager; FILTER and SORTBY; formula auditing; recording and editing simple macros |
| Manage advanced charts and tables | 25–30 % | Dual-axis, waterfall, funnel and box-and-whisker charts; PivotTables with slicers and calculated fields; PivotCharts |
Microsoft sets the same guideline as for the Associate level, roughly 150 hours with the product, with amortisation tables, inventory schedules and custom business templates as its sample workbooks. The weights show where the marks sit: data handling, formulas and charts carry nearly all of them, while settings and protection are the smallest slice. ICDL's advanced Spreadsheets module in the PROFESSIONAL programme is the other exam at this level.
Beyond the outline
- Power Query connects to files, folders and databases, reshapes the data and saves every step as a query that can be refreshed next month. It runs in Excel and in Power BI alike, and the MO-211 outline does not include it.
- LAMBDA turns a formula into a named, reusable function that works anywhere in the workbook, without VBA. With the spilling functions the exam does cover, such as FILTER and SORTBY, it replaces many helper columns.
- VBA written by hand goes beyond the simple recorded macros the exam asks for. It is useful for automating Excel itself, such as looping through files or formatting reports, and it is a maintenance commitment: whoever writes the code owns it when it breaks.
Those three are where the working analyst's time savings usually come from, which is why this topic keeps them even though no Excel exam checks them. A sensible order is Power Query first, because messy source data is the commonest problem, then LAMBDA once the same long formula appears in several places, and hand-written VBA only when nothing else will do. Scheduled flows that open or update workbooks belong with workplace automation.
When Excel should hand over
Advanced users often keep a workbook alive past the point where it serves anyone. The warning signs are familiar: copies emailed around in several versions, lookups across hundreds of thousands of rows, dashboards rebuilt by hand every month. At that stage the data model belongs in Power BI and the wider methods of data and AI, while Excel stays the place where an analyst tests an idea before it becomes a report. The Excel tool page covers the product itself.
Concepts to know
Glossary entries with the reason each one matters here.
- Macro
Recorded automation inside a workbook.
Certifications that test it
Vendor exams and free certificates; facts, cost and the preparation path are on each page, and the certifications hub has them all.
Tools of the trade
- Microsoft Excel
Power Query, dynamic arrays and VBA live inside it.
Frequently asked
- Is MO-211 much harder than MO-210?
- It covers more ground rather than stranger ground. The Associate exam checks that you can build and format a sound workbook; the Expert exam expects conditional logic, lookups, what-if analysis, PivotTables and recorded macros under time pressure. If those features are part of your weekly work, the step is mostly revision; if not, it is new learning.
- Do I need VBA to count as an advanced Excel user?
- No. The Expert outline asks only for simple recorded macros, and plenty of advanced users rarely write code. Power Query and modern formulas handle much of what VBA was once used for; VBA stays useful for driving Excel itself.
- Should I learn Power Query or Power BI first?
- Power Query. It runs inside both Excel and Power BI, so the skill carries across, and it solves the cleaning problems that come before any dashboard.
Courses in the directory
268 courses are filed here; the top 6 by our ranking, details and the provider link on each course page.
Last reviewed 26 September 2026 · Getting Digital