Week 3: Excel Lookups, PivotTables & Your First Dashboard

1. VLOOKUP, XLOOKUP & INDEX/MATCH

A lookup pulls a value from one table into another, matched by a shared key — exactly the job a customer_id or product_id column exists to do. Say a Products sheet holds product_id, product_name and price, and an Orders sheet only has product_id:

' VLOOKUP: exact match, column position counted from the lookup column
=VLOOKUP(A2, Products!$A:$C, 2, FALSE)
' A2 = product_id to find | Products!$A:$C = table to search | 2 = 2nd column in
' that range (product_name) | FALSE = exact match, not "closest"

' XLOOKUP: newer, clearer, and doesn't break if a column is inserted
=XLOOKUP(A2, Products!A:A, Products!B:B)
' find A2 in Products column A, return the matching value from Products column B

' INDEX/MATCH: the classic pre-XLOOKUP alternative -- still everywhere in real files
=INDEX(Products!B:B, MATCH(A2, Products!A:A, 0))
' MATCH finds A2's ROW POSITION in column A; INDEX returns column B at that same row

VLOOKUP's biggest weakness is that 2 — a hardcoded column position. Insert a new column into Products and every VLOOKUP pointing at it can silently start returning the wrong value. XLOOKUP (Excel 2021/365) fixes this by referencing the actual return column directly, and also looks up in either direction, not just left-to-right like VLOOKUP requires. INDEX/MATCH solves the same left-to-right limitation an older way, and is worth recognizing since it still appears constantly in files built before XLOOKUP existed.

2. PivotTables: Turning Rows into Summaries

A PivotTable takes a flat table — one row per transaction — and summarizes it by dragging fields into four zones, with no formulas written at all: Rows (what to group by), Columns (a second grouping, spread across the top), Values (what to summarize — sum, count, average), and Filters (what to exclude before summarizing).

1. Click any cell inside your data range
2. Insert -> PivotTable -> New Worksheet
3. Drag "Region" into ROWS
4. Drag "Amount" into VALUES -- Excel defaults to Sum, but Value Field Settings
   lets you switch to Count, Average, Max, Min, etc.
5. Drag "Order Date" into COLUMNS, then right-click a date -> Group ->
   choose "Months" to turn 500 individual dates into 12 tidy month columns

This is the exact same "one row per group" idea as Week 2's SQL GROUP BY — a PivotTable grouped by region and summed by amount produces literally the same result as SELECT region, SUM(amount) FROM orders GROUP BY region. Dragging a field to a different zone re-summarizes instantly, with no formula to rewrite — that's the entire value proposition of a PivotTable over a manual SUMIFS table.

3. PivotCharts

A PivotChart is a chart built directly from a PivotTable, and it stays connected to it — filter, re-group or drill into the PivotTable, and the chart redraws automatically, with no manual re-selecting of a data range.

1. Click any cell inside your PivotTable
2. Insert -> PivotChart -> choose a chart type (Clustered Column works for most
   region/category comparisons)
3. The chart's field buttons let you filter directly from the chart itself,
   without touching the underlying PivotTable

Building the chart from the PivotTable (rather than the raw data) is what keeps it live — changing the PivotTable's grouping from monthly to quarterly, for instance, instantly reshapes the chart too, since it's reading the PivotTable's current summarized output, not a static snapshot of the original rows.

4. Slicers & Conditional Formatting

A slicer is a clickable filter button connected to one or more PivotTables — a far friendlier interface than the small dropdown arrows on a PivotTable's field headers, and the standard way to make a report interactive for someone who isn't comfortable digging into Excel's menus.

1. Click inside a PivotTable -> PivotTable Analyze -> Insert Slicer -> choose "Region"
2. Right-click the slicer -> Report Connections -> check every PivotTable it
   should also filter -- one click on "North" now filters every connected
   PivotTable and PivotChart on the dashboard at once

Conditional formatting solves a different problem: making values readable at a glance instead of requiring someone to read every number. Color scales, data bars and icon sets (Home → Conditional Formatting) all work directly on a range with no setup beyond a couple of clicks, and a formula-based rule handles anything more specific:

' Conditional Formatting -> New Rule -> "Use a formula to determine which cells to format"
=D2>5000
' Applies whatever formatting you choose (e.g. green fill) to any row where
' column D exceeds 5000 -- the formula re-evaluates per row automatically

5. Building a One-Page KPI Dashboard

A dashboard is just these pieces — PivotTables, PivotCharts, slicers, conditional formatting — arranged deliberately on one sheet, designed to be read in seconds rather than explored:

Row 1:  Three or four large KPI numbers across the top
        (=SUM(...), =AVERAGEIFS(...) -- big font, one number, one label each)
Row 2+: 2-3 PivotCharts side by side (revenue by region, trend by month, top products)
Side:   One or two slicers, positioned so they visually control everything below them

Two habits separate a dashboard people actually use from one they ignore: keep the whole thing visible without scrolling (design for one screen, not a scroll of charts), and lead with the 3–4 numbers that matter most before any chart — most people reading a dashboard want the headline number first, and the supporting detail only if something looks off.

6. Hands-on Exercise

7. Knowledge Check

Four quick questions. Expand each to check your answer.