Microsoft Excel Intermediate + Copilot

Load and shape data into Power Pivot, then structure it into a clean star-schema data model.

• Get data ready in Excel
  • –Import Excel and CSV files and structure them as Excel Tables — open a .csv with File > Open and copy the sheet in, because the ribbon's From Text/CSV route opens the query tool this course leaves to the Advanced level.
  • –Clean essentials: remove duplicates and blanks, fix data types, split text — then confirm each column's type in the Power Pivot window (Home > Data Type), because a date left as text will not join to Calendar and quietly breaks Day 2's time intelligence.
  • –Combine tables with lookup formulas only where a table is missing a key it needs — the tables stay separate and get joined with relationships in Power Pivot, not flattened into one sheet.
• Power Pivot & the Data Model
  • –Enable the Power Pivot COM Add-in; load tables to the model.
  • –Power Pivot vs a standard PivotTable — when each wins.
  • –The Data Model window and Diagram View.
• Relationships & star schema
  • –Fact and dimension tables; one-to-many relationships.
  • –Why star schema wins for performance and clarity.
  • –Cardinality, filter direction, and troubleshooting — in Excel, filters flow one way only, from the one side to the many. Bi-directional cross-filtering is a Power BI setting; Power Pivot has no such switch.
  • –More than one fact table — joining Targets and Inventory to the same Product, Customer and Calendar dimensions, and the grain problem when targets are monthly and sales are daily.
• Calculated columns & a date table
  • –Calculated columns vs measures — when to use each.
  • –RELATED and RELATEDTABLE across relationships.
  • –A dedicated Calendar table for time intelligence — use the Calendar file provided, or generate one in the Power Pivot window with Design > Date Table > New.
  • –Design > Mark as Date Table — skip it and Day 2's time intelligence quietly returns the wrong answer, with no error message.
• Prompting for your data model
  • –Describe your tables to Copilot chat and have it suggest table roles and relationships — then build them yourself in Diagram View. Copilot cannot see or edit the data model.
  • –Draft calculated columns from a plain-language goal — Copilot writes the expression in chat, you add the column in the Power Pivot window.
  • –Sense-check the model structure before you build on it.

Write DAX with confidence — from aggregations to time intelligence and KPI logic — and understand how filter context really works.

• DAX foundations
  • –Measures vs calculated columns — implicit vs explicit.
  • –Aggregations: SUM, AVERAGE, MIN, MAX, DISTINCTCOUNT.
  • –Readable measures with variables (VAR / RETURN).
• Filter & row context
  • –How filter context flows from PivotTables, slicers, and relationships.
  • –CALCULATE — the most important function in DAX.
  • –Shape context with FILTER, ALL, ALLEXCEPT, KEEPFILTERS.
• Iterators & relationships
  • –SUMX, AVERAGEX, RANKX — row-by-row calculation.
  • –RELATED and RELATEDTABLE across the model.
  • –DIVIDE for safe, error-free ratios.
• Time intelligence
  • –TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, DATEADD — on the Calendar table you marked as a date table on Day 1.
  • –YTD, QTD, MTD; YoY%, MoM%, QoQ% growth.
  • –Moving averages and running totals.
• KPI measures & analysis
  • –Variance, % achieved, and target vs actual — against the Targets table you loaded on Day 1.
  • –Ranking, top-N, and % of total.
  • –Organise measures for a clean, maintainable model.
• Prompting for DAX
  • –Draft and explain measures from a plain-language goal in Copilot chat, then paste the DAX into the Power Pivot measure grid yourself.
  • –Debug and optimise DAX with Copilot — paste in the measure and the wrong number, because Copilot cannot read your model.
  • –Sense-check every measure against a manual total.

Turn the model into an interactive dashboard, speed up reporting with modern formulas and Copilot, and ship a capstone.

• Multi-table PivotTables
  • –Build PivotTables directly from the model, across tables.
  • –Slicers and timelines connected to the whole model.
  • –Drill down to detail with Quick Explore, and double-click a value to Show Details — Excel's equivalent of drill through, capped at 1,000 rows until you raise it in PivotTable Analyze > Options > Data.
• Model-driven dashboard
  • –Interactive dashboards built straight from the data model.
  • –Connect slicers and timelines across every PivotTable and PivotChart.
  • –Dynamic chart titles — a PivotChart title will not take a cell reference, so you link a text box to a cell and place it over the chart — and KPI cards for leadership, pulling single model measures into formatted cells with CUBEVALUE and CUBEMEMBER (PivotTable Analyze > OLAP Tools > Convert to Formulas).
• Modern formulas to finish
  • –Dynamic arrays: FILTER, SORT, UNIQUE for summary tables.
  • –LET and LAMBDA for readable, reusable logic.
  • –XLOOKUP and INDEX/MATCH with error handling.
• Advanced Copilot workflows
  • –Multi-step prompts that clean, analyse, and report in sequence — one instruction at a time, on the worksheet where Copilot can see the data.
  • –Automate repetitive monthly reporting — fixed Table names, a fixed prompt set, then Refresh All. Copilot has no saved or scheduled workflow, so the routine is yours to own.
  • –Audit and validate Copilot output on larger datasets.
• Ship & hand over
  • –Refresh All — here it rebuilds the model and every PivotTable from the Tables inside this workbook, so next month starts by pasting the new rows inside the existing Table, not below it.
  • –Field-consistency checks and handover guidelines.
  • –A dashboard your team can maintain and extend.
• Prompting for dashboards & reporting
  • –Copy the PivotTable results you want commentary on into a plain worksheet Table first — Copilot reads worksheet data, not PivotTables and not the model — then ask it to summarise the trends and draft the exec narrative.
  • –Generate KPI commentary and chart recommendations from that summary Table, in the sheet where Copilot can actually see the numbers.
  • –Catch hallucinated numbers before they reach leadership — check every figure Copilot quotes back against the PivotTable it came from.