Advanced Excel &
Data Analytics

🎯 Module goal: Move through, reshape and structure any worksheet at analyst speed — turning a raw export into a clean, expandable, print-ready Excel Table.

1.1 Interface Tour: Ribbon, Dialog Launchers and the Quick Access Toolbar

Excel's interface is a cockpit, and fast analysts know their switches. The Ribbon organizes commands into tabs (Home, Insert, Formulas, Data…) and labeled groups, and nearly every group hides a tiny diagonal-arrow icon called a dialog launcher. Those launchers open the complete settings boxes — Format Cells, Paste Special, Page Setup — which is how professionals reach deep controls without digging through menus.

Your first customization should be the Quick Access Toolbar (QAT): visit File → Options → Quick Access Toolbar and pin the commands you touch hourly — Paste Values, Format Painter, Filter, Freeze Panes. Every pinned command earns an automatic Alt+number shortcut. Better still, press Alt on its own anywhere and Excel paints letter badges across the whole ribbon (KeyTips): the sequence Alt,H,V,V pastes pure values without your hand ever reaching the mouse.

1.2 Navigation Mastery: Ctrl+Arrows, Ctrl+Shift+Arrows and Name Box Jumps

Ctrl+Arrow teleports you to the wall of your data island: from the top of a filled column, Ctrl+↓ lands on the last occupied cell, stopping wherever a blank gap interrupts. Add Shift and the jump becomes a selection — Ctrl+Shift+↓ grabs everything from your active cell to the bottom of the region, which is the backbone of rapid formatting, filling and deleting.

Ctrl + Arrow           Jump to the edge of the data region
Ctrl + Shift + Arrow   Select out to that edge
Ctrl + Home            Snap back to A1
Ctrl + End             Jump to the last used cell of the sheet
Name Box (top-left)    Type A5000, press Enter = instant jump
Ctrl + Backspace       Re-center the view on the active cell

The Name Box doubles as a GPS coordinate field: type any address such as F20000 or a defined name, hit Enter, and you're there. If you scroll away afterward, Ctrl+Backspace re-centers the viewport on wherever your cursor actually lives — a lifesaver in 100,000-row sheets.

1.3 Selection, Insertion and Formatting at Speed

Select whole structures without dragging: Shift+Space takes the entire row, Ctrl+Space the column, Ctrl+A the surrounding data region (tap again for the whole sheet). Reshape immediately after: Ctrl+Shift++ inserts cells, rows or columns; Ctrl+- deletes them; Shift+F11 spawns a fresh worksheet; Ctrl+PgUp/Ctrl+PgDn flip between sheets.

The underrated giant here is F4 = Repeat. Format one cell, select ten more, press F4 — the identical formatting replays instantly. Double-clicking Format Painter makes it sticky across multiple targets (Esc releases it). Round it off with fill shortcuts: Ctrl+D copies the cell above, Ctrl+R copies the cell to the left, and Ctrl+E (Flash Fill) learns your split-or-combine pattern from a single worked example.

1.4 Custom Number Formats: Display Without Touching Data

A number format is a costume the value wears — the underlying figure never changes. Open Format Cells anywhere with Ctrl+1, choose Custom, and learn the skeleton: four zones separated by semicolons, positive ; negative ; zero ; text. Within a zone, # shows a digit only if it exists, 0 forces a digit even when absent, and commas manage thousands.

#,##0.00              12,345.50         money with thousand separators
#,##0,,"M"            13 M              scaled into millions for exec views
0.0%                  23.4%             one-decimal percentages
dd-mmm-yyyy           14-Aug-2026       unambiguous international dates
mmm-yy                Aug-26            clean month axis labels
;;;"Confidential"     text entries display a masked label instead

Date formats stack tokens: d/dd for day, ddd/dddd for weekday name, m/mmm for month number/name, yy/yyyy for year. The fourth zone masks text entries — a credentials column formatted ;;;"Hidden" shows nothing sensitive in screenshots while values stay intact underneath.

1.5 Tables (Ctrl+T): Why Structured References Change Everything

Click any cell inside your data and press Ctrl+T, confirming My table has headers. You've upgraded a dumb range into a self-aware object: it auto-expands the moment you type beneath it, carries filters and banded styling, auto-fills new formulas down the whole column, and offers a one-keystroke totals row (Ctrl+Shift+T while inside the table).

The real superpower is structured references — formulas that speak in column names instead of fragile coordinates:

=SUM(tblSales[Amount])              Always covers every row, including future ones
=tblSales[@Qty]*tblSales[@Price]    [@Col] means "this row's value"
=COUNTA(tblOrders[OrderID])         A live row counter that never goes stale

Name the table something meaningful in Table Design → Table Name (say, tblSales). From this moment, PivotTables, charts, XLOOKUPs and Power Query all point at a growing organism instead of a frozen address — you stop stretching ranges every Monday morning.

1.6 Freeze Panes, Print Setup and Workbook Hygiene

Nothing screams amateur like scrolling into header-less oblivion. On the View tab pick Freeze Top Row, or park your cursor at cell B2 and choose Freeze Panes to lock everything above and left of that point simultaneously — headers and ID column both stay visible.

Print-ready is a habit, not a scramble: Page Layout → Print Titles repeats your header row atop every printed page, Set Print Area fences the report zone, and Page Setup → Fit to 1 page wide kills ugly column spillover onto orphan pages.

• One fact per cell — "Mumbai-West" belongs in two columns (City, Zone), never one
• No merged cells — they wreck sorting, filtering and pivots; use Center Across Selection instead
• Separate layers — RAW (untouched originals), CALC (formula engine), REPORT (presentation)
• Consistent headers — one spelling per column name, forever, so downstream tools never break
• No decorative blanks — spacer rows/columns sabotage Ctrl+Arrow jumps and Table expansion

🎯 Module goal: Write lookup, logic, text, date and aggregation formulas that answer real business questions inside a single cell.

2.1 Absolute vs Relative References: The $A$1 Psychology

References are relative by default — copy =B2*C2 one row down and it becomes =B3*C3, which is usually what you want. The trouble starts when part of a formula must stay pinned: a VAT rate sitting in H1. Copy without protection and the reference drifts to empty H2, returning zeros or errors across your whole column.

Dollar signs are the fix, and each axis is locked separately: $H$1 freezes both column and row, H$1 only the row, $H1 only the column. The mental rule: lock whatever must not move when you copy. While editing a reference, tap F4 to cycle B2 → $B$2 → B$2 → $B2 — no typing dollar signs manually.

=B2*$H$1        Price × fixed tax rate (rate never drifts as you fill down)
=$A2*B$1        Grid pattern: row labels + column headers (multiplication tables)
F4              Cycle reference styles while the cursor is inside one

2.2 XLOOKUP — the Modern King of Lookups

XLOOKUP replaces VLOOKUP/HLOOKUP entirely: it matches exactly by default (no more silent near-misses), searches any direction including leftward, and carries its own "not found" answer instead of needing an IFERROR wrapper.

Wildcard mode (match_mode 2) matches patterns like "P-10*". Search mode -1 scans backwards, which is how you fetch the latest price revision rather than the first ever recorded. And XLOOKUP nests beautifully for two-way lookups — pick the row, then the column:

=XLOOKUP($H$2, tblPay[Employee], XLOOKUP(I$3, tblPay[#Headers], tblPay))
=XLOOKUP(G2, tblInv[SKU], tblInv[Price], "Check code", 2)

2.3 INDEX/MATCH — the Classic Workhorse

Every legacy workbook you'll inherit runs on INDEX/MATCH, so read it fluently. MATCH returns a position number: where in a list the value sits. INDEX returns the value at a position in another list. Bolt them together and search range and return range can live anywhere:

=INDEX(Prices[Rate], MATCH(F2, Codes[SKU], 0))
=INDEX(Grid, MATCH(H2, Grid[Name], 0), MATCH(I2, Grid[#Headers], 0))   two-way grid

The zero third argument forces exact matching. It's more moving parts than XLOOKUP, but it works in every Excel version ever shipped and teaches you to think in positions — a skill that pays off far beyond lookups.

2.4 The Logic Family: IF, IFS, AND/OR/NOT, IFERROR/IFNA

IF(test, then, else) branches a formula: pass/fail, bonus/no-bonus. Deeply nested IFs become unreadable, so Excel grew IFS, a clean ladder of test→result pairs evaluated top-down:

=IF(Amount>=10000, Amount*0.05, 0)
=IFS(Score>=90,"A", Score>=75,"B", Score>=60,"C", TRUE,"D")
=IF(AND(Region="West",Amount>5000),"Priority","Standard")
=IFERROR(XLOOKUP(...), "Not found")     catches any error
=IFNA(XLOOKUP(...), "Not found")        catches only #N/A

AND/OR/NOT combine conditions inside any logical slot. Prefer IFNA over IFERROR on lookups: IFNA flags genuine misses while letting real formula bugs (#REF!, #VALUE!) stay loud and visible instead of being masked as "Not found".

2.5 The Text Arsenal

Real data arrives filthy, so analysts slice and normalize text daily. Fixed-width extraction uses LEFT/RIGHT/MID plus LEN; cleanup uses TRIM (kills doubled spaces — the #1 import plague) and CLEAN (strips invisible characters); casing normalizes with PROPER/UPPER/LOWER. The modern splitters spill whole arrays from one cell:

=TRIM(PROPER(A2))                    "  john o'connor"  →  John O'Connor
=MID(A2, 4, 3)                       Characters 4–6 of a fixed-width code
=TEXTBEFORE(B2, "@")                 Username half of an email
=TEXTAFTER(B2, "@")                  Domain half
=TEXTSPLIT("red|blue|green", "|")    Spills three cells
=TEXTJOIN(", ", TRUE, C2:F2)         Tidy comma list, skipping blanks

Don't overlook Flash Fill (Ctrl+E): type one example of the output you want beside messy input and Excel infers the pattern for the rest — perfect when writing a formula would take longer than showing the answer.

2.6 Date Math That Thinks Like an Analyst

Dates are serial numbers underneath (Jan 1, 1900 = 1), so subtracting two dates yields days and every arithmetic trick works. TODAY() recalculates each day — ideal for aging reports. The workhorses below cover most reporting needs:

=EOMONTH(TODAY(), 0)                     Last day of the current month
=EOMONTH(TODAY(), -1) + 1                First day of this month
=EDATE(A2, 6)                            Six-month contract renewal date
=NETWORKDAYS.INTL(A2, B2, 1, Holidays)   Working days, Sat+Sun off
=DATEDIF(HireDate, TODAY(), "y")         Completed years of service
=YEARFRAC(Start, End)                    Fractional years for prorating fees

EOMONTH is the analyst's anchor: month-end cutoffs drive invoicing, depreciation and KPI snapshots. NETWORKDAYS.INTL accepts weekend codes and a holiday range, turning payroll-day counts into one cell of effort.

2.7 Conditional Aggregation: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS

This family answers management questions directly: revenue per region, open tickets per owner, average basket above a threshold. Argument order matters — the result range comes first, then condition/range pairs. Criteria are text strings like ">=1000", "<>London", or wildcards such as "A*":

=SUMIFS(tblS[Amount], tblS[Region], "West", tblS[Date], ">="&$H$1)
=COUNTIFS(tblT[Status], "Open", tblT[Owner], "A*")
=AVERAGEIFS(tblS[Basket], tblS[Basket], ">0")
=MAXIFS(tblS[Amount], tblS[Region], "East")

Multiple pairs act as AND logic across columns; OR logic (West or North) is just two SUMIFS added together. Notice the concatenated date criterion ">="&$H$1 — building thresholds in helper cells lets users change filters without ever editing a formula.

🎯 Module goal: Make one formula produce a living report that filters, sorts, de-duplicates and resizes itself as the data changes.

3.1 Spill Ranges and the # Operator

In Excel 365, a formula that returns multiple values spills them into neighboring cells automatically, outlined by a soft blue border. The whole block is governed by its top-left anchor cell — only that cell is editable; anything you type inside the spill zone triggers a #SPILL! error until you clear the obstruction.

Other formulas reference an entire spill with the single-character # operator: if =UNIQUE(A2:A500) lives in E2, then E2# means "whatever E2 currently spills". That reference grows and shrinks with the data — no more COUNTA-then-adjust rituals:

=UNIQUE(A2:A500)          Spills distinct values starting here
=SUM(F2#)                 Sums the whole spill anchored at F2
=COUNTA(E2#)              Counts how many results exist right now

3.2 FILTER — Instant Report Slices

FILTER returns every row meeting your conditions — it's AutoFilter as a formula, refresh-proof and copy-safe. Syntax: =FILTER(array, include, [if_empty]), where include is a TRUE/FALSE test applied to one column of the same height.

Multiple conditions combine with arithmetic: multiplying tests means AND (both must pass), adding them means OR (either passes). Wrap each test in parentheses so Excel evaluates comparisons before the multiply:

=FILTER(tblO, tblO[Region]="West", "Nothing found")
=FILTER(tblO, (tblO[Region]="West")*(tblO[Amount]>10000), "No big orders")
=FILTER(tblO, (tblO[Region]="West")+(tblO[Region]="North"), "—")

The third argument keeps dashboards graceful: instead of #CALC! errors on empty results, users see a tidy message.

3.3 SORT, SORTBY, UNIQUE and SEQUENCE

SORT(array, [sort_index], [order]) re-orders a spill without touching source order; order 1 ascending, -1 descending. SORTBY is stronger still — it sorts one array by the ranking of a completely different array, perfect for sorting names by their scores:

=SORT(F2#, 2, -1)                       Sort the spill by column 2, descending
=SORTBY(tblR[Name], tblR[Score], -1)    Names ranked by score
=UNIQUE(tblS[Customer])                 Distinct customers
=UNIQUE(tblS[Code], , TRUE)             Values appearing exactly once
=SEQUENCE(12)                           Numbers 1–12 spilled downward
=SEQUENCE(10, 1, TODAY(), 7)            Next ten weekly dates

SEQUENCE generates number grids from nothing: row counters, calendar scaffolds, simulated test data. Combined with date math it can lay out an entire month's grid in one cell.

3.4 LET and LAMBDA — Readable Formulas and Homemade Functions

LET lets you name intermediate results inside a formula, computing each once instead of repeating expensive sub-formulas. It reads top-down like pseudocode, which makes six-line monsters debuggable:

=LET(
    revenue, SUM(tblS[Amount]),
    cost,    SUM(tblS[Cost]),
    margin,  revenue - cost,
    margin / revenue
)

LAMBDA(parameter1, parameter2, …, calculation) goes further: define it once in Name Manager under a name like NetMargin (=LAMBDA(rev,cost,(rev-cost)/rev)) and thereafter call =NetMargin(B2,C2) anywhere in the workbook — your own function, zero VBA. LAMBDA can even call itself, enabling recursion for things like splitting nested text.

3.5 Combinations: UNIQUE(FILTER(...)) Live Reports

Nested array functions produce self-updating reports in a single cell — the "search engine" pattern analysts ship daily. A customer name sits in B3; everything below answers to it:

=SORT(FILTER(tblOrders, tblOrders[Customer]=$B$3), 2, -1)
=UNIQUE(FILTER(tblOrders[Product], tblOrders[Customer]=$B$3))

Point a Data Validation dropdown's Source at =$H$2# and your input list becomes dynamic too — pick any customer from the spilled list and both reports re-slice instantly. This compose-everything style is the mental model behind modern dashboard interactivity.

3.6 Legacy Fallbacks for Older Excel Versions

No 365? You still have real options. AutoFilter plus SUBTOTAL(109, range) sums only visible rows, recreating FILTER's spirit manually. Helper columns replicate UNIQUE via Data → Remove Duplicates or COUNTIF flags. Array math exists too — entered with Ctrl+Shift+Enter instead of Enter (the old CSE dance). And PivotTables remain the heavyweight fallback for grouping, uniques and slices.

🎯 Module goal: Replace repetitive cleaning chores with refreshable Power Query pipelines — messy files in, analysis-ready tables out, zero formulas.

4.1 The ETL Mindset: Extract, Transform, Load

Analysts burn hours re-doing the same cleanup every month. Power Query (Data tab → Get Data) ends that: it's a free ETL engine inside Excel. Extract pulls from files, folders or the web; Transform reshapes and cleans; Load deposits the result into a sheet or the Data Model.

The magic is that every click is recorded as a step. Your transformations form a recipe stored beside the data — next month you drop in new files and press Refresh. You clean once; Excel repeats forever. Open the Queries & Connections pane to see every query and its pipeline of Applied Steps.

4.2 Importing from CSVs, Folders and the Web

Data → Get Data → From File → From Text/CSV opens a preview that auto-detects delimiters and encodings before you commit. But the star is From Folder: point it at a directory and it ingests every file inside, using one sample file to define the transformation applied to all of them. Thirty regional exports become one query.

From Web scrapes tables straight from URLs into preview grids. Whatever the source, you land in the Power Query Editor — a separate window where the actual shaping happens, safely away from your workbook.

4.3 Cleaning Moves: Split, Unpivot, Replace, Trim and Data Types

The editor's ribbon covers daily cleaning: Split Column breaks "City-Zone" apart by delimiter; Replace Values fixes recurring typos; Transform → Format → Trim/Clean strips stray spaces and invisible characters in bulk; right-click headers to remove duplicates or error rows.

The single most valuable trick for spreadsheet-shaped data is Unpivot: cross-tabs with months running across columns are unreadable by pivots and models. Select your ID columns, right-click → Unpivot Other Columns, and wide grids collapse into tidy rows (Region, Month, Sales). Also make types explicit — the small icon on each column header:

= Table.TransformColumnTypes(Source,
    {{"Order Date", type date}, {"Amount", type number}, {"Region", type text}})
= Table.UnpivotOtherColumns(Source, {"Region"}, "Month", "Sales")

4.4 Merge (Joins) and Append Queries

Merge is VLOOKUP at industrial scale: Home → Merge Queries joins two queries on one or more key columns, bringing columns across side-by-side. The default join kind, Left Outer, keeps every row of the first query; Left Anti returns only unmatched rows — an instant reconciliation report for finding invoices missing prices.

Append stacks queries vertically when they share the same structure — twelve monthly tables become one continuous fact table in two clicks. Standard practice: append raw files first, then merge lookups onto the combined result.

4.5 Parameters and Refreshable Reports

Hard-coding a folder path means editing queries when files move. Instead create a parameter (Manage Parameters): name it pFolderPath, then reference it in your folder source. Change the value once and every dependent query follows. Parameters also drive filters — a pTopN parameter can control how many top products survive a filter step.

Finishing touches: Close & Load To… chooses destination (Table, PivotTable, connection-only), and refresh lives everywhere — Ctrl+Alt+F5 refreshes all, or set a query's property Refresh data when opening the file so reports wake up current.

4.6 Case Study: A Monthly Folder of 30 Exports → One Clean Table

• 1. Create parameter pFolderPath pointing at this month's export folder
• 2. Get Data → From Folder → Combine Files; let the sample define the transform
• 3. In the sample transform: promote headers, trim text columns, set date/number types
• 4. Remove duplicate keys and error rows; unpivot any cross-tab blocks
• 5. Merge in the product master via Left Outer join; load the result as tblMaster
• 6. Next month: replace 30 files, hit Refresh All, walk away in under a minute

🎯 Module goal: Turn flat tables into interactive summaries that answer management questions in clicks — grouping, % of total, rankings and multi-pivot slicer panels.

5.1 Anatomy: The Four-Drop-Zone Mental Model

A PivotTable is a drag-and-drop summary engine. Internalize four zones: Values does math on numbers (Sum, Count, Average — switch via Value Field Settings); Rows buckets categories; Columns adds a second bucket, creating a matrix; Filters slices everything at once. Nearly every pivot you'll ever build is a combination of these four moves.

Always source pivots from a proper Table (Module 1), so new data flows in after a refresh. Under Design → Report Layout, switch to Tabular form when you plan to copy results or export them — compact form looks tidy but merges labels into one column.

5.2 Grouping: Dates Into Months/Quarters, Numbers Into Bands

Right-click any date label in the Rows area → Group → tick Years, Quarters, Months. Excel instantly builds hierarchical periods from raw transaction dates — daily noise becomes boardroom rhythm. Numeric fields group too: right-click a value label like salary or age, choose Group, set starting at 0, by 500, and continuous numbers become clean bands for histogram-style analysis.

Two caveats worth knowing early: grouping a field applies workbook-wide wherever that field appears, and PivotTables built on the Data Model (section 5.6) can't regroup dates — precompute period columns in Power Query instead.

5.3 Show Values As, Calculated Fields, Running Totals and Rank

Right-click any value → Show Values As unlocks the analyst toolkit without writing a single formula: % of Grand Total for contribution analysis, % of Parent Row for mix within region, Difference From (previous month) for movement, Running Total In for cumulative curves, and Rank Largest to Smallest for leaderboards.

When two base fields must interact — Amount ÷ Qty, for instance — use PivotTable Analyze → Fields, Items & Sets → Calculated Field. Know its quirk: it computes on the sums of each field, not row-by-row averages, which is exactly right for ratios like margin but wrong for things like average price per line.

5.4 Slicers and Timelines: One Panel, Many Pivots

Slicers (Insert → Slicer) are clickable filter buttons floating above your pivots — far more discoverable than hidden dropdown filters, which is why dashboards use them. Multi-select by dragging across buttons. For date fields, insert a Timeline: a visual period slider that snaps between years, quarters, months or days.

The power move is Report Connections: right-click a slicer → Report Connections → tick every pivot on the sheet. One click now re-slices four pivots simultaneously — instant "connected dashboard" feel with zero formulas.

5.5 PivotCharts and GETPIVOTDATA Extraction

A PivotChart is welded to its pivot: filter the pivot, the chart follows. That coupling makes exploration fast, though styling options are narrower than regular charts. When you need precise numbers elsewhere on a sheet, referencing a pivot cell auto-writes GETPIVOTDATA — a formula that keeps pointing at the same measure even if rows move:

=GETPIVOTDATA("Amount", $A$3, "Region", $H$2, "Years", 2026)

The trick to making it flexible: swap fixed criteria for cell references, as with $H$2 above. Now typing a different region into H2 retargets the extraction — pivot precision with cell-level control.

5.6 Data Model Pivots for Million-Row Sources

At import time (Power Query's Close & Load To dialog) tick Add this data to the Data Model. Pivots built on the model unlock DISTINCT COUNT — counting unique customers, something normal pivots famously cannot do — and compress data so sources well beyond the 1,048,576-row sheet limit summarize comfortably.

🎯 Module goal: Ship a dashboard a director understands in five seconds and trusts enough to act on — right charts, live titles, real interactivity.

6.1 Design Principles: The 5-Second Rule and KPI Hierarchy

A dashboard exists to trigger decisions, not to display everything you know. Apply the 5-second rule: within five seconds of landing, the viewer must answer "how are we doing?" — which means headline KPIs go top-left where Western eyes land first. Below them, trends; below those, breakdowns; raw detail lives on hidden sheets.

Design around the decision your audience makes weekly: a regional manager needs "which stores need intervention?", not forty metrics at equal weight. Hierarchy is the whole game — one glance layer, one exploration layer, one detail layer.

6.2 Chart Selection: When Bar Beats Pie (and Waterfalls Win)

Match chart to question. Comparison across categories → sorted horizontal bars (they forgive long labels). Trend over time → line charts. Composition with more than three parts → stacked bars; pies only survive at ≤3 slices because humans compare angles badly. Explaining how A became B (last year's profit to this year's) → waterfall. Two different units together → combo chart with a secondary axis:

6.3 Micro-Visuals: Sparklines, Heat Maps and Icon Sets

KPI cards come alive with sparklines (Insert → Sparklines): a 12-month trend line squeezed into a single cell beside the big number. Tables get instant readability from conditional formatting — Color Scales paint heat maps across an entire matrix, while Data Bars draw in-cell magnitude bars that make a column self-charting.

Icon Sets add direction arrows: apply to a YoY% column, then Manage Rules → edit so the green arrow appears only above 0% and the red below — thresholds you control, semantics the reader grasps without reading a single number.

6.4 Dynamic Titles Linked to Formulas

Static titles lie the moment data changes. Put a formula in any cell and bind it to a title: click the chart title once, type =, then click the cell containing:

="Revenue — "&TEXT($B$2,"mmm yyyy")
="Top region: "&INDEX(Regions, MATCH(MAX(Vals), Vals, 0))
=IF($B$3="All","All regions","Region: "&$B$3)

The title now narrates the current state automatically — when a slicer or dropdown changes, the heading rewrites itself. This one habit separates dashboards that get trusted from ones that quietly mislead.

6.5 The Interactivity Layer: Dropdowns and Checkboxes Driving FILTER

Interactivity means the user drives the formulas, never edits them. Create a control hub: a Data Validation list cell (B3) fed by your region column, plus native Excel 365 checkboxes (D2). Every downstream formula reads these cells:

=FILTER(tblO, (tblO[Region]=$B$3)*(tblO[Include]=$D$2), "No matches")
=SUMIFS(tblS[Amount], tblS[Region], IF($B$3="All","*", $B$3))

Flip the checkbox, pick a region, and KPI cards, charts and tables all re-slice through their shared inputs. Arrange slicers and controls in one left-rail panel so interaction has a predictable home.

6.6 Layout, Alignment and the Three-Color Discipline

Screenshot-worthy dashboards are built on restraint. Hide gridlines, align every element to the cell grid, keep generous white space between zones, and standardize font sizes (one size for titles, one for body). Color follows the three-color rule: one primary brand hue for data, neutral grays for structure, and red/green reserved exclusively for good/bad signals — never decoration.

🎯 Module goal: Build a driver-based financial model whose outputs, scenarios and audit trails stand up in front of finance leadership.

7.1 Model Architecture: Inputs / Calculations / Outputs

Professional models separate into three layers. The Inputs sheet holds every assumption — growth rates, prices, loan terms — conventionally in blue font so reviewers spot editable cells instantly. The Calculation layer references inputs and never hardcodes numbers; the Output layer (statements, KPIs, charts) references calculations.

The discipline that makes models auditable: a constant appears exactly once, upstream. When the CFO asks "what if inflation hits 8%?", you change one blue cell and watch the whole model breathe — no find-and-replace archaeology at 11 pm.

7.2 NPV, IRR, PMT and Loan Amortization Schedules

Adopt the sign convention early: cash flowing away from you is negative. NPV discounts future flows assuming they arrive at period-end, so a day-zero outflow gets added outside the function:

=NPV($B$3, D2:H2) + C2        PV today of flows starting next period
=IRR(C2:H2)                    Rate making NPV exactly zero (C2 is negative)
=PMT(rate/12, years*12, -loan) Monthly installment for an amortizing loan
=IPMT(rate/12, per, n, -loan)  Interest portion of payment number "per"
=PPMT(rate/12, per, n, -loan)  Principal portion of the same payment

An amortization schedule is just five copied formulas down sixty rows — Period, Opening balance, Payment, Interest, Closing:

Interest:  =Opening * rate/12
Principal: =Payment - Interest
Closing:   =Opening - Principal      (next row's Opening = prior Closing)

Watch the interest share shrink as principal grows — the schedule itself teaches amortization better than any textbook page.

7.3 Scenario Analysis: Data Tables and Scenario Manager

To sweep one driver, list candidate values down a column with your output formula referenced at the top corner, then Data → What-If Analysis → Data Table, naming the input cell Excel should substitute. Every growth rate from 3% to 15% gets evaluated instantly without copying formulas.

Scenario Manager stores named value sets: Best, Base, Worst each define several changing cells at once. Add all three, hit Summary, and Excel writes a comparison sheet showing how profit moves across stories — the artifact managers actually screenshot.

7.4 Goal Seek and Solver: Target-Driven Questions

Models usually answer forward ("what profit does this price give?"), but stakeholders ask backward: "what price breaks even?" Goal Seek solves it: Data → What-If → Goal Seek, Set cell = Profit, To value = 0, By changing cell = Price. Excel iterates until the target holds.

Solver (enable via Add-ins) generalizes this to multiple changing cells under constraints — maximize margin while keeping capacity ≤ X and price ≥ cost. Start with Simplex LP for linear problems; it's fast and returns explainable answers.

7.5 Sensitivity Analysis Grids

A two-variable Data Table shows profit across price × volume simultaneously: put the output formula in the grid's top-left corner, price values down its left edge, volume values across its top. Select the whole rectangle → What-If → Data Table → Row input = price cell, Column input = volume cell. Excel floods the grid with outcomes.

Lay conditional-formatting heat scales over the result and leadership sees the risk surface instantly — which combinations print money, which bleed. This single artifact routinely replaces forty-slide decks.

7.6 Model Audit: Tracing Precedents and Building Error Checks

Trust is engineered. On the Formulas tab, Trace Precedents/​Dependents draw arrows showing exactly what feeds a cell; Evaluate Formula steps through calculation piece by piece; the Watch Window monitors far-away cells while you edit locally. Keyboard shortcuts accelerate everything:

Finally, build a visible check strip: =IF(ABS(Assets-Liabilities)>0.01,"⚠ FAIL","OK"). Balance checks and negative-inventory checks convert silent corruption into loud alarms before anyone presents broken numbers.

🎯 Module goal: Erase your most boring weekly chore with a one-click, auditable automation — recorded safely, edited confidently, run anywhere.

8.1 Recording Macros — and Relative vs Absolute Recording

First unlock the controls: File → Options → Customize Ribbon → tick Developer. Now Record Macro captures every click into replayable VBA while you perform the chore once, cleanly, the way you'd want it repeated. Name it meaningfully (MonthlyClose, not Macro7) and give it a shortcut if it deserves one.

The critical toggle is Use Relative References on the Developer tab. With it off (absolute), the recording replays at the exact addresses you touched — select A1 no matter where you start. With it on, actions replay as offsets from wherever the cursor currently sits. Formatting a fixed report header wants absolute; "clean whichever row I'm on" demands relative. Choosing deliberately is the difference between an asset and a foot-gun.

8.2 Buttons, .xlsm Files and Trust Center Safety

Make automation discoverable: draw any shape or Insert → Button (Form Control), right-click → Assign Macro, label it "Run Month-End". Colleagues who can't find the button won't use the tool.

Two safety facts protect your work and your reputation. Save as .xlsm — plain .xlsx silently strips macros on save. And understand the Trust Center (File → Options → Trust Center → Macro Settings): Excel disables macros in downloaded files until someone clicks Enable Content. Treat that prompt with respect — enabling macros in a stranger's file is how malware spreads. Never email macro files without warning recipients.

8.3 VBA Literacy: Reading and Editing Recorded Code

Recorded code looks intimidating but reads like stage directions. Learn five things — Sub ... End Sub wraps a routine, Range("A1").Value = 100 writes cells, .Select/.Activate are usually unnecessary noise, With blocks group operations on one object, and For i = 2 To lastRow ... Next loops rows. Then start editing: recorded macros hard-code constants you can safely change.

Sub MonthlyClose()
    ThisWorkbook.RefreshAll                       ' refresh queries + pivots
    Application.Wait Now + TimeValue("0:00:05")   ' let caches rebuild
    With Worksheets("Log")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = Now
    End With
End Sub

Ten readable lines beat 400 recorded ones. The log line above finds the last used row in column A and stamps the current moment beneath it — a pattern worth memorizing because audit trails make automations trustworthy.

8.4 Office Scripts: Automation for Excel on the Web

Colleagues living in browser Excel can't run VBA — Microsoft's modern answer is Office Scripts: open the Automate tab, record actions like you would macros, get clean TypeScript code stored in your cloud account. Scripts run on desktop too, but their superpower is Power Automate: schedule a script to run nightly or when a file lands in SharePoint, no computer awake required. If your organization is Microsoft 365-first, learn Office Scripts alongside VBA rather than instead of it — each covers environments the other can't reach.

8.5 Knowing When to Graduate to Python

VBA shines inside one workbook, but four signals mean it's time for Python: jobs must run on a schedule outside office hours; files exceed comfortable memory; data lives behind APIs or databases; multiple people need version-controlled collaboration. Python with pandas/openpyxl replicates everything here at scale — and your Excel logic transfers directly: a SUMIFS becomes a groupby, Power Query becomes a pipeline, this module's thinking becomes code. Our Python track picks up exactly where this section ends.

🎯 Module goal: Model related tables once and compute KPI measures that stay correct in every slice — replacing VLOOKUP scaffolding with real relationships and DAX.

9.1 Why Power Pivot Beats Lookup Chains

Lookup-driven workbooks copy data sideways until nobody trusts any number. Power Pivot replaces those chains with relationships: tables connect to each other by key columns once, and every pivot, chart and measure reads through those connections automatically. Rename a product once; every report follows.

Bonus muscle you get for free: the VertiPaq engine compresses data so millions of rows summarize comfortably in memory — far past the sheet limit — and one model serves dozens of reports consistently. This is where Excel stops being a grid and becomes a small database with formulas on top.

9.2 Loading Multiple Tables and Building a Star Schema

Load each source separately via Power Query, ticking Add this data to the Data Model. Then classify: a fact table records events (one row per sale — Sales); dimension tables describe entities (Products, Customers, Stores). The ideal shape is a star: facts at the center, dimensions radiating around it, each connected by a single key.

Open Power Pivot → Manage → Diagram View and drag each dimension's ID onto the matching fact column. Relationships are one-to-many, single-direction (dimension filters fact) — resist the urge to connect everything to everything; two facts talk only through shared dimensions.

9.3 Calendar Tables and Date Relationships

Time intelligence demands a dedicated, contiguous date dimension — not scattered raw dates. Build one inside Power Pivot with DAX:

Calendar = CALENDAR(DATE(2023,1,1), DATE(2026,12,31))
Year     = YEAR('Calendar'[Date])
Month    = FORMAT('Calendar'[Date], "MMM")
MonthNum = MONTH('Calendar'[Date])      ' sort Month by this

Every date from Jan 1 2023 to Dec 31 2026 exists exactly once, gaps included — that completeness is what makes month-over-month math honest. Finish by right-clicking the table → Mark as Date Table, then relate Calendar[Date] to Sales[OrderDate]. All pivots now group time from one authoritative source.

9.4 Measures and Filter Context Intuition

A measure is a named formula evaluated fresh for every cell of output. Drag Region to rows with Total Sales := SUM(Sales[Amount]) and Excel computes West's sum for the West row, East's for East — the cell's surroundings (filter context) feed the calculation automatically. That single idea is 80% of DAX.

CALCULATE rewires filter context before evaluating:

Total Sales := SUM(Sales[Amount])
B2B Sales   := CALCULATE([Total Sales], Customers[Segment] = "B2B")

Read the second line as: "compute Total Sales, but first replace any segment filter with B2B." Put both measures side-by-side in a pivot and each row shows its region's total against its region's B2B portion — context-aware comparison without copying a thing.

9.5 Time Intelligence: YoY%, MTD and YTD

With a marked calendar related to facts, the classic period measures are four lines:

Sales LY := CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY %    := DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Sales YTD := TOTALYTD([Total Sales], 'Calendar'[Date])
Sales MTD := TOTALMTD([Total Sales], 'Calendar'[Date])

DIVIDE is deliberate: unlike /, it returns blank instead of #DIV/0! when last year was zero — dashboards stay clean. Drop YoY % into any matrix and every quarter computes its own growth against its own prior-year twin.

9.6 KPI Cards and a Dedicated Measures Table

Scattered measures die in big models. Create an empty table named _Measures (Power Query: blank query, load connection-only, or just a one-column sheet) and set it as home for every measure — reviewers find all business logic in one place. Format each measure once in its dialog (currency, %, thousands separators) so formatting never depends on where it lands.

🎯 Module goal: Deliver an end-to-end analytics product — messy multi-source files in, an interactive dashboard and executive memo out — worthy of your portfolio.

10.1 Read the Brief Like a Consultant

The brief: a retail chain wants a sales performance command center. Before touching Excel, scope it in ten minutes of questions. Which KPIs actually drive decisions — revenue, growth vs last year, target achievement, top/bottom SKUs? Who views this, how often, on what screen? What counts as a "sale" — do returns subtract? Which regions roll up where?

Write the answers into a one-paragraph charter at the top of your workbook. Every later design fight ("should we add a tab for…?") resolves by rereading the charter. Consultants charge heavily for this habit; you can build it free.

10.2 Ingest Messy Multi-Source Data via Power Query

Reality arrives as three awkward sources: a folder of daily POS exports, a weekly ERP extract, and a manually maintained Targets sheet someone owns in their inbox. Apply Module 4 end to end: a folder-path parameter, connection-only staging queries, explicit data types, trim/replace cleanup, append the POS files, then merge lookups.

• Audit query: route rows that fail type conversion into a visible rejects table instead of silently losing them
• Reshape targets: unpivot the month-columns sheet into tidy Store/Month/Target rows
• One exit point: everything lands as clean Tables named for loading into the model

10.3 Model with Power Pivot Relationships and DAX Measures

Load the four dimensions (Products, Stores, Customers, Calendar) plus two facts (Sales, Targets) into the Data Model and wire the star schema from Module 9. Targets usually arrive at monthly grain per store — relate it through a Store-Month key or reshape to match Sales' grain exactly; mismatched grains are the classic modeling bug.

Total Sales  := SUM(Sales[Amount])
Sales LY     := CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY %        := DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Achievement %:= DIVIDE([Total Sales], [Target])
Margin %     := DIVIDE([Total Sales] - SUM(Sales[Cost]), [Total Sales])

Six measures, all formatted once in their dialogs, all living in the _Measures home table. This layer is the brain; everything visual merely reads from it.

10.4 Build the Interactive Dashboard Layer

Sketch the layout on paper before inserting a single chart — seriously, paper first. Then apply Module 6 discipline: left rail of slicers (Year, Quarter, Region) wired via Report Connections; KPI card row across the top reading Total Sales, YoY %, Achievement %, Margin %; beneath them one combo chart (sales bars vs target line) carrying the trend story; a sorted top/bottom SKU table and regional bar chart filling the middle band; drill-through detail one click away.

Every title formula-linked, every color earning its place, every element snapped to the grid. Test like a user: click every slicer combination hunting for #N/A, blanks and broken titles — those are what directors remember.

10.5 Write the Executive Summary Sheet

Data doesn't decide; narratives do. Add one summary sheet with three blocks: a headline verdict in one sentence ("Growth is real but concentrated in two stores"); three insights, each paired with its so-what and a recommended action; a short watchlist of risks or open questions. Reference numbers live from the model (GETPIVOTDATA/cell links) so the memo survives refreshes — never retype figures into prose.

Write for a director reading between meetings: short sentences, verbs first, no jargon walls. The person who writes the takeaway becomes the person invited to discuss it.

10.6 Package It for Your Portfolio

Your showpiece must survive being opened by strangers. Capture clean screenshots (hide personal paths), and include a methodology write-up: a simple diagram of Data → Model → Dashboard, plus the decisions you made and why. Inside the workbook keep an Assumptions & Decisions sheet documenting grain choices, definitions and known limitations — reviewers read that page more carefully than any chart.