Expert Excel · Interactive lesson 12 of 12
Expert capstone
Requirements, ingestion, modeling, dashboard, automation, QA, documentation, handoff — everything in this track, on one refreshable operations model, delivered as if another team will own it after you.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialExcel Capstone Project: Build a Complete SolutionSkill check
Before starting the capstone, confirm you can do each of these independently — they're what the diagnostic below actually tests:
- Translate a vague request into requirements (E1).
- Build reusable formula logic with LET or LAMBDA (E2).
- Design robust query steps that handle bad rows (E3).
- Explain fact/dimension design and filter context (E4).
- Choose an automation path based on maintenance risk (E5, E6).
- Add tests and reconciliation checks (E9).
- Package and hand off the workbook (E11).
Not solid on one of those yet? Loop back to that module before the diagnostic below — it's meant to be a checkpoint, not a first exposure.
Concept teach
First-class diagnostic exercise: Expert Excel readiness
Five sources feed one operations model — this is what "design it before you build it" (E1) looks like applied to a real handoff.
| Source | Key Field | Measure | Refresh Need | Control Check | Owner |
|---|---|---|---|---|---|
| Sales export | Order ID | Revenue | Weekly folder drop | Record count & revenue total | Sales Ops |
| Product table | Product ID | Category | Monthly update | No missing product IDs | Product Ops |
| Region table | Region ID | Manager | Quarterly update | Every sale maps to a region | Finance |
| Targets file | Region + Month | Goal | Monthly update | Targets exist for every active region | FP&A |
| Calendar table | Date | Period | Annual refresh | Dates cover all transactions | Analyst |
Where to find it in Excel
Data › Refresh All›Data Model›QA sheet
| A | B | C | D | E | F | ||
|---|---|---|---|---|---|---|---|
| 1 | Check | Status | |||||
| 2 | Sources refreshed | PASS | |||||
| 3 | Rows reconcile | PASS | |||||
| 4 | Measures tie out | PASS | |||||
| 5 | Handoff notes | Done | |||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Swipe sideways to see the whole window.
- 1One Refresh All updates every source (Ctrl+Alt+F5).
- 2Five queries, one per source.
- 3Manage Data Model opens the star schema and the measures.
- 4The QA sheet must pass before handoff.
- 5Tabs follow the build order: README to ChangeLog.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build — the diagnostic
Work through all eight, start to finish
- Create a workbook map showing source, staging, model, report, QA, and notes sheets (E1).
- Define the fact table and lookup tables needed for a clean model (E4).
- Describe the Power Query steps needed to ingest and validate the data, including how bad rows get handled (E3).
- Create at least three measures or formulas that should be reusable — a LAMBDA, a DAX measure, or both (E2, E4).
- Design the dashboard controls and state what decision each visual supports (E8).
- Add at least three QA checks, including a reconciliation to source totals (E9).
- Choose one automation target and explain whether formulas, Power Query, VBA, Office Scripts, or Power Automate is the best fit (E5, E6).
- Write a handoff note explaining refresh steps, risks, and maintenance ownership (E11).
There's no formula engine grading this one — it's meant to be worked through on paper or in a real workbook, then reviewed against the completion standard below.
Independent extension — the capstone
Build the complete refreshable model
Build a complete refreshable operations model end to end: ingest real (or realistic) data through an engineered query, model it with a star schema and real measures, design a two-audience dashboard, automate one repeatable step, add QA checks and a reconciliation, protect and version it, and write the documentation someone else would need to run it without you. Hand it to someone else and watch where they hesitate — that's your real punch list.
Spot a mistake or have an idea for this lesson? Send feedback