Charts & Dashboard Craft

Learning objectives

By the end you can:

1. State the chart-choice grammar from memory, map a data question (comparison, trend over time, relationship, part-to-whole bridge, two-units-together) to the correct chart type, and justify each choice from how the human eye decodes visual marks.
2. Explain from first principles why length- and position-based charts (bar, line) are read accurately while angle- and area-based charts (pie, 3-D) are not, and use that to reject pie and 3-D in analytical work.
3. Build a combo chart with a secondary axis (revenue as columns on the primary axis, gross-margin % as a line on the secondary axis) using the correct ribbon and Format-pane paths and keyboard shortcuts.
4. Recognise and prevent the misleading dual axis: state why an unanchored secondary axis can manufacture a false correlation, and how an honest analyst scales or annotates it.
5. Bind a chart to a dynamic source (an Excel Table or a spilled # range) so that new rows appear automatically, and explain why a fixed range A1:B13 is a latent bug.
6. Insert and format sparklines (line, column, win/loss) for word-sized in-cell trends, and say when a sparkline beats a full chart.
7. Apply the clean-exhibit rules: one message per chart, a title that states the "so what," direct labelling over legends, ruthless removal of non-data ink, and connect them to the Minto / analyst communication toolkit.
8. Assemble a one-screen KPI dashboard: KPI tiles (big number + context), slicers for interactive filtering, small multiples, and a headline combo chart, laid out so the top questions are answered at a glance.
9. Compute the numbers behind the exhibits correctly (a year revenue total, a blended vs simple-average gross margin, and a year-over-year growth rate) and know why the distinction between a simple average of percentages and a revenue-weighted margin is a real analytical trap, not a rounding quibble.

Prerequisites & connections

Builds on. EX0.01 (the Table discipline (Ctrl+T) which is the backbone of every dynamic chart source and every slicer in this module; a chart bound to a Table grows itself, a chart bound to a raw range does not). EX1.01–EX1.02 (the aggregation and lookup families: SUMIFS, COUNTIFS, and XLOOKUP are what compute the KPI values a dashboard displays, a dashboard is a presentation layer over the calculations you already know how to build). EX1.03 (Dynamic Arrays: FILTER, SORT, and the spilled # operator, which this module turns into self-resizing chart sources). EX2.01 (Data Tools & Integrity, the clean, validated data a good exhibit assumes; you cannot chart your way out of dirty data). You should be comfortable entering a SUMIFS and building a PivotTable before starting here.

Feeds forward. The slicer-and-tile machinery here is the presentation half of EX3.01's PivotTables and PivotCharts; run the two together and you have full interactive reporting. On the analyst track, this is the last mile of almost everything: M2.05's benchmarking exhibits, M3.08's three-statement outputs, and every valuation summary ultimately reach a human through a chart, and a model whose logic is impeccable but whose exhibit is unreadable does not get its recommendation adopted. The communication spine here (one message per exhibit, the title carries the message, declutter everything else) is the same Minto/Pyramid-Principle discipline drilled in the analyst communication toolkit (M1.02); this module is where that principle meets pixels. When you reach board-deck and investment-memo work, the difference between a persuasive analyst and an ignored one is very often exactly the craft in this node.

Choosing the right chart

A chart is a machine that turns numbers into visual marks, the length of a bar, the height of a point on a line, the position of a dot in a plane, and hands the decoding to the reader's visual cortex. The entire craft rests on one empirical fact from the perception literature (Cleveland & McGill, and everyone since): **the human eye judges position along a common scale and length accurately, and judges angle, area, and colour intensity badly.** A reader can tell that one bar is about 1.4× another at a glance; nobody can tell that one pie slice is 1.4× another without the printed numbers, at which point the pie is doing no work the numbers weren't already doing. The rules below are all corollaries of that single fact.