Capstone — Self-Updating Sales Dashboard

Nine chapters of Excel Advanced each covered one genuinely powerful tool in isolation — dynamic arrays, deeper lookups, PivotTables and PivotCharts, Power Query, the Data Model and DAX, macros, VBA, and formula auditing. This capstone chains all nine into a single realistic project: a Sales Dashboard that imports its own data every month, relates it properly, summarizes it correctly under any filter, and refreshes itself with one click — the kind of workbook the rest of this course was actually building toward.

The Project Brief

Every month, a new CSV export of that month's sales lands in a shared folder. The dashboard needs to: pull in every file in that folder automatically; relate each sale to its product's category; calculate profit margin correctly no matter how the data is sliced; let a user drill down by category, then a live, dependent subcategory list; visualize it with a chart controllable by clickable filters and a date range; and refresh the entire pipeline with a single click, not nine separate manual steps.

Step 1 — Importing and Cleaning Monthly Sales Files

Data → Get Data → From Folder (Chapter 4), pointed at the shared monthly-exports folder, combines every CSV inside it into one query. Applied Steps clean the result: Change Data Type forces Amount and Cost to genuine numbers (not text — avoiding Chapter 4's own PivotTable Count-instead-of-Sum trap), Trim removes stray whitespace from the Category column (avoiding a silent Merge mismatch later), and Remove Duplicates guards against any accidentally re-exported rows.

Step 2 — Relating Sales to a Products Reference Table

Both the cleaned Sales query and a separate Products reference table (ProductID, Category, Subcategory) are loaded into the Data Model (Chapter 5). In Diagram View, Sales[ProductID] is related to Products[ProductID] as a one-to-many relationship — with Products[ProductID] first confirmed unique (Chapter 5's own warning box), avoiding a silently inflated total from a duplicate key.

Step 3 — DAX Measures for Total Sales and Profit Margin

Two measures, defined once in the Data Model (Chapter 5), correctly recalculate under whatever filter the dashboard later applies:

Step 4 — A Distinct, Live Category List for Reporting

A small reporting area lists every category currently in use, kept automatically up to date with a dynamic array (Chapter 1):

This spilled list needs no maintenance as new categories appear in future monthly imports — it's the same non-destructive, always-current list Chapter 1 built for exactly this reason.

Step 5 — A Dependent Category → Subcategory Dropdown

A Data Validation dropdown (Chapter 2) lets a user pick a Category; a second, dependent dropdown then narrows to just that category's own subcategories, sourced from a live FILTER formula:

Step 6 — Building the PivotTable, PivotChart, Slicer, and Timeline

A PivotTable built from the Data Model (Chapter 3) drags Category into Rows and the Total Sales and Profit Margin measures into Values — no merged table or VLOOKUP needed anywhere, since the relationship (Step 2) already joins Products and Sales live. A PivotChart built directly from it visualizes the same data, and both a Category Slicer and a Date Timeline are connected via Report Connections, so one click on a category button or one drag of the date range filters the PivotTable and PivotChart together, instantly.

Step 7 — Automating the Monthly Refresh with VBA

Rather than manually clicking Refresh All, then re-checking every query, a single macro (Chapters 6-8) does the whole job:

This macro is assigned to a shape placed directly on the dashboard sheet (Chapter 6) rather than requiring anyone to know where to find it in the Macros list. Application.EnableEvents is wrapped around the refresh specifically because this workbook also has a Worksheet_Change handler elsewhere (Chapter 8) that would otherwise fire redundantly as the refreshed values land in the sheet.

Step 8 — Auditing Before Calling It Done

Before treating the dashboard as finished, Chapter 9's habits apply directly: Trace Precedents on the Profit Margin measure's PivotTable cells confirms it's genuinely pulling from both related tables as intended; Error Checking is run across the whole workbook to catch any stray #REF! left over from earlier iteration; and any IFERROR/IFNA used elsewhere in the workbook is checked against Chapter 9's own rule — reserved for genuinely expected cases (a lookup that legitimately might not match), never used as a blanket way to hide whatever error happens to show up.

Putting It All Together

Hands-On Exercises