GUIDINEE.COM › Microsoft Excel › Level 6 › 6.1

6.1 · Case studies

Level 6 · In practice

6.1Case studies

Goal: see how the skills of levels 0 to 5 chain together on real cases.
Estimated time: 14 min · If you only have 5 min: read “The essentials in 30s” + the diagram.

The essentials in 30 seconds

  • Assemble: raw export → named structured table (Ctrl+T), cleaned types, pivots by region/month/product, KPIs with SUMIFS, 3 sheets Data/Calculations/Dashboard.
  • Replay: the next month, you replace the data and Refresh — Power Query replays the consolidation of the 12 exports in one click, with no copy-paste.
  • Prove: a slicer connected to all the pivots, decluttered charts, totals that add up — what isn't verified doesn't exist.

Wassim thread — two deliveries in Algiers

Wassim, an accountant in Algiers, reviews his two standout projects of the quarter: the monthly sales dashboard delivered to his management (raw export, « Sales » table, pivots, KPIs, Region slicer), then the consolidation of the 12 monthly exports replayed in one click via Power Query. The two cases below pick up his sequence: clean table, checked types, pivots, robust KPIs, decluttered output.

Each case follows the flow Context → Steps → Result: replay it on your own data.

Why case studies

The previous levels laid the bricks; here we assemble them end to end. It's the chaining of the right reflexes — clean table, robust formulas, pivots, decluttered visuals — that separates a reliable workbook from an error factory.

Case 1 — A sales dashboard, from A to Z

Context: You receive a raw sales export (Date, Region, Product, Amount) and must deliver a reusable monthly dashboard.

Déroulé :

  1. Convert the export into a structured table (Ctrl+T) and name it « Sales ».
  2. Clean the types (dates, amounts as numbers), remove the empty rows.
  3. Create pivots: sales by region, by month (grouped dates), by product.
  4. Add KPIs with SUMIFS (total revenue, month revenue, top region).
  5. Compose 3 sheets: Data, Calculations (pivots), Dashboard (KPIs + 2 charts + slicer).
  6. Connect a Region slicer to all the pivots; declutter the charts.

Result: A readable and interactive dashboard; the next month, you replace the data and Refresh.

Case 2 — Consolidate 12 monthly exports without copy-paste

Context: Twelve identical monthly files to combine into an easily updated annual report.

Déroulé :

  1. Place the 12 files in one « Sales 2026 » folder.
  2. Power Query ▸ From a Folder ▸ Combine.
  3. Transformation steps: remove useless columns, set types, filter errors.
  4. Load the result into a single table (or into the data model).
  5. Build pivots and charts on this consolidated table.
  6. Next month: drop in the 13th file → Refresh → everything updates.

Result: A consolidation replayable in one click, with no copy-paste or range errors.

Common misinterpretation: « copy the cases exactly ». No. The cases show an order of production (structure, clean, analyze, quantify, output, replay), not a recipe to copy: take the order, adapt columns, KPIs and visuals to your data.
Text version of the diagram (SEO / screen readers / translation): Full chain: 1) Structure (export → Ctrl+T table named « Sales », unique headers, N1/N2). 2) Clean (real numbers right-aligned, real dates, empty rows removed). 3) Analyze (pivots by region, by month with grouped dates, by product, N3). 4) Quantify (KPIs with SUMIFS: total revenue, month revenue, top region). 5) Output (3 sheets Data/Calculations/Dashboard, connected Region slicer, decluttered charts, N5). 6) Replay (Power Query folder + Combine for 12 exports, Refresh the next month, N4). Lesson: a reliable workbook comes from the chaining, not from a single formula.

Ready-to-say script — frame a dashboard (30 s)

« Our need: [monthly table / annual consolidation]. Our data: [named structured table, checked types]. Our analysis: [pivot region/month/product + SUMIFS KPIs]. Our proof: [connected slicer, totals that add up, Refresh replayed]. »

Copy-paste template — sales KPIs

=SUMIFS(Sales[Amount],Sales[Region],"Algiers")
=SUMIFS(Sales[Amount],Sales[Date],">="&D2,Sales[Date],"<"&EOMONTH(D2,0)+1)
=IFERROR(XLOOKUP(A2,Sales[Product],Sales[Amount],"Not found"),"Not found")

SME / small budget

A single case replayed small: a named table, a pivot, two KPIs, proof noted.

SME Algiers

Adapt to the firm's real exports: French columns, semicolon separator, checking the amounts in dinars.

Remote / hybrid

Double the traceability at a distance: named source, refresh date and proof written on the dashboard sheet.

Intercultural

Make it explicit before transposing: a header or date format obvious to one person must speak to any newcomer.

Caution: Generic reminder: a shared workbook commits your team — keep a copy before you Refresh, only distribute the output (not the raw exports), and check types and totals before considering the delivery done.

48h challenge — apply tomorrow

Take 1 real export and replay the sequence: named structured table, checked types, 1 pivot, 2 KPIs with SUMIFS, Refresh proven.

Section exit checklist
  • I know how to chain structured table, pivot, KPIs and output on a real case.
  • I have a replayable sequence (Refresh or Power Query folder).
  • I did the 48h challenge or planned its date.
Self-assessment 1-5: does my dashboard update with one Refresh?do my totals add up against the source?
Next : Recurring problems (playbook) (V3) →
Check your understanding

Wassim delivers his sales dashboard. Which sequence respects the guide?