Charts & Dashboard Craft

Excel Mastery Track · Blue belt · ~6–8 hours · Microsoft 365 on Windows assumed

A chart is not decoration. It is an argument made in ink, a claim about your numbers, encoded so that a busy reader's eye reaches the conclusion before their conscious mind does. When a CFO glances at your slide for four seconds and gets it, that is not luck. It is the payoff of a hundred small craft decisions you made deliberately: this comparison as bars and not a pie, this trend as a line and not a table, this second series lifted onto its own axis, this one clutter-free message and not five competing ones. When the same CFO squints, tilts their head, and asks "wait, what am I looking at?", that too is a decision, you let the software's defaults make the argument for you, and the software does not know what you are trying to say.

This module makes you fluent in turning numbers into exhibits that carry a single, unmissable message. You will learn the grammar of chart choice, the small, almost mechanical mapping from the question your data answers to the mark that answers it (bar for comparison, line for time, scatter for relationship, waterfall for a bridge, combo when two different units must share a frame). You will build combo charts with a secondary axis correctly, and, just as importantly, learn the one way they lie so you never ship the lie. You will bind charts to dynamic ranges so they grow themselves when next month's data lands, cram trends into sparklines the width of a word, strip exhibits down to the clean-exhibit essentials that professional communication demands, and finally assemble the lot into a one-screen KPI dashboard a decision-maker can actually use.

The through-line (the discipline that separates an analyst from someone who merely knows where the chart button is) is this: every chart states one message, and every mark on it either serves that message or is deleted. Excel's defaults optimise for "technically a chart." You will optimise for "the reader agrees with you." Those are very different targets.

One version note, stated once: everything here assumes Microsoft 365. Native combo chart types, spilled-range (#) chart sources, modern sparklines, and slicers connected across PivotTables all behave as described on current Excel for Windows. On Excel 2016/2019 some paths differ (combo charts exist but the ribbon is older, and dynamic arrays with # do not exist at all), and those gaps are flagged where they matter.

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, for the Table habit (Ctrl+T). Every dynamic chart source and every slicer you'll use here rests on it, because a chart bound to a Table grows itself and a chart bound to a raw range doesn't. EX1.01 and EX1.02 cover the aggregation and lookup families. SUMIFS, COUNTIFS, and XLOOKUP are what compute the KPI values a dashboard displays, so a dashboard is a presentation layer over calculations you already know how to build. EX1.03, for Dynamic Arrays: FILTER, SORT, and the spilled # operator, which you'll now turn into self-resizing chart sources. EX2.01, Data Tools & Integrity, for the clean, validated data a good exhibit assumes. You can't chart your way out of dirty data. Before you start, you should be comfortable entering a SUMIFS and building a PivotTable.

Feeds forward. The slicers and tiles here are 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 reach a human through a chart in the end. A model with impeccable logic and an unreadable exhibit doesn't get its recommendation adopted. The communication spine (one message per exhibit, a title that carries the message, everything else decluttered) is the same Minto Pyramid Principle drilled in M1.02, the analyst communication toolkit. Here it meets pixels. When you reach board-deck and investment-memo work, this craft is very often the difference between a persuasive analyst and an ignored one.

Core teaching content

Choosing the right chart

A chart is a machine that turns numbers into visual marks and hands the decoding to the reader's visual cortex. A mark might be the length of a bar, the height of a point on a line, the position of a dot in a plane. The whole craft rests on one empirical finding from the perception research of 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. At a glance, you can tell that one bar is about 1.4× another. Nobody can tell that one pie slice is 1.4× another without the printed numbers, and once the numbers are printed, the pie isn't doing any work they weren't already doing. Every rule below follows from that one fact.

So the "grammar" is a mapping from the question the data answers to the mark that answers it best:

• Comparison across categories calls for a bar (or column) chart. "Which region sold most?" "How do these eight products rank?" Bars encode value as length along a shared baseline, the eye's strongest channel. Use horizontal bars when category labels are long or there are many categories. They read top to bottom like a list, and the labels fit. Use vertical columns for a few categories, or when the categories have a natural left-to-right order. For bars, always start the value axis at zero. The eye compares lengths, so a truncated baseline exaggerates differences and is a classic deception.
• Change over time wants a line chart. "How did revenue trend across the year?" Time is continuous and ordered. A line connects successive points, so the eye reads slope as rate of change, and rate of change is what you asked about. Columns can show a short time series, but past a handful of periods the line wins, because the message is the trend and no single value carries it.
• A relationship between two variables needs a scatter (XY) chart. "Does higher marketing spend go with higher revenue?" A scatter places each observation at its (x, y) position in a plane, and the shape of the cloud reveals correlation, clusters, and outliers. Both of its axes are value axes, while a line chart's horizontal axis holds categories. So "spend vs revenue" has to be a scatter. A line can't show it.
• A bridge from a start to an end value is a job for a waterfall chart. "How did we get from opening cash of ₹40 lakh to closing cash of ₹52 lakh?" A waterfall shows a starting bar, a run of floating up-and-down steps for each contribution or subtraction, and an ending bar. It's the analyst's tool for decomposition: revenue bridges, margin bridges, headcount bridges, variance walks. M365 has a native Waterfall chart type (Insert ▸ Charts ▸ Waterfall) that handles the floating maths for you. Mark subtotal points as "Set as Total" in the data-label menu.
• Two series in different units on one frame need a combo chart, which gets its own subsection below.
• For part-to-whole, prefer a bar to a pie. "What share is each segment?" The textbook answer is a pie. The professional answer is usually a bar chart of the shares, or a stacked bar if you must show the whole, because the eye compares bar lengths far better than pie angles. Save the pie for the rare case of two or three parts where "roughly half / a third / a bit" is the entire message.

Select your data range, headers included, then go to Insert ▸ Charts, or use Insert ▸ Recommended Charts to let Excel propose types. Treat its suggestions as a menu to reject from, and make the decision yourself. Two shortcuts are worth owning. With the data selected, Alt+F1 drops a default chart onto the current sheet, and F11 creates a chart on its own new chart sheet. Once a chart is selected, the + button floating beside it (Chart Elements) toggles titles, labels, gridlines, and the legend. Ctrl+1 opens the Format pane for the selected element. You'll press it more than any other shortcut in chart work, because it formats whatever you've clicked, from an axis to one data point.

Before you insert anything, finish the sentence "the one thing I want the reader to see is ___." The blank tells you the chart type. If you can't fill it in, you're not ready to chart yet.

Common mistakes & how experts think differently

1. The misleading dual axis, the signature warning of chart craft. A combo chart's secondary axis is a scale you choose. Slide its minimum and maximum and you can make two unrelated series appear to rise and fall in lockstep, a manufactured correlation with no basis in the data. The beginner accepts Excel's auto-scaled secondary axis without a thought. Worse, they sometimes tune it until the two lines "look related" for a slide. The expert's reflex is the opposite. Set each axis on defensible, stated grounds (e.g., margin axis fixed 0 to 50% because that's the business's plausible range), and keep the bounds constant across every period's exhibit so comparisons stay fair. If you're ever tempted to nudge an axis toward the picture you want, treat it as a red flag that you're drawing the conclusion and not the data. When two series really do move together, a fairly scaled chart shows it. When they don't, no axis trick should be allowed to pretend they do.
2. 3-D and pie overuse. A 3-D column chart adds depth, an area/volume cue the eye reads badly. It also tilts the plot so nearer bars look bigger than farther ones of equal value. Every judgment the flat version made easy gets harder, purely for decoration. Pies force angle comparison, which the eye also fails at. The beginner reaches for 3-D and pie because they look "designed." The expert sees every third dimension and every wedge as accuracy spent on ornament, and defaults to flat bars and lines. A pie survives only for the rare two-or-three-slice "roughly half" message. In analytical work, 3-D essentially never survives.
3. A chart source frozen to a fixed range, so new data never appears. =SERIES(...,$A$2:$A$13,...) shows twelve months forever. Add month thirteen and the chart ignores it without any warning. The beginner charts a raw range and later presents a stale picture without knowing it. The expert charts only off a Table or a spilled # range, so the exhibit grows itself. They treat a hardcoded end row in a chart source the way they treat a hardcoded number in a formula, as a bug waiting to fire.
4. Too many series, the spaghetti chart. Eight lines in eight colours crossing each other don't make eight messages. They make zero. The beginner plots every series because the data is there. The expert asks "which comparison is the message?" Then they either highlight one series in an accent colour against the rest in grey, or break the tangle into small multiples, one small, identically scaled chart per series. Either way, the comparison the reader needs becomes the one the layout makes easy.
5. Not direct-labelling, outsourcing the reader's memory to a legend. A legend makes the reader hold a map from colour to name in their head and dart back and forth to decode the chart. The beginner keeps the default legend. The expert labels each line or bar at the data and deletes the legend, so the name is read where the value is and there's nothing to memorise.
6. Default colours that fight the message. Excel's stock palette paints every series a different, equally loud hue. Giving them all the same emphasis means none of them is emphasised, which is the opposite of "one message." The beginner ships the rainbow. The expert uses grey for context and a single accent for the series that carries the point, so colour directs attention instead of scattering it. They also check that the exhibit still reads in greyscale and for colour-blind viewers, and they never let red against green carry the meaning alone.
7. A truncated value axis on a bar chart. Bars are read by length, so starting the axis at 40 instead of 0 makes a 5% difference look like a 3× difference. That's a deception even when nobody intended it. The expert starts every bar axis at zero. Only line charts, which are read by position and slope, may legitimately use a non-zero baseline, and even then with care and a clear label.

One habit ties them all together. An expert designs the exhibit so the reader reaches the intended, true conclusion with the least effort, and treats every default (3-D, pie, rainbow, legend, auto-axis, fixed range) as a decision to override, never a gift to accept. Beginners present what the software drew. Professionals present what they mean.

Worked examples

Worked example 1: India annual revenue-and-margin combo chart (India, ₹)

The task. A Pune-based direct-to-consumer brand has twelve months of revenue (₹) and gross margin (%) for the fiscal year, April to March. You'll build the analyst's signature exhibit, a combo chart with revenue as columns on the primary axis and gross-margin % as a line on the secondary axis. Then you'll compute the three numbers the exhibit's title and callouts will carry: the year revenue total, the average gross margin (done two ways, because the naïve way is a trap), and a YoY-style % change between two months to annotate the festive spike. The figures are recomputed in Python, and the results are at the end.

Step 1, the data (the source Table, one row per month):

     A       B (Revenue ₹)   C (Gross margin %)
1    Month   Revenue         Margin%
2    Apr     4,200,000       38.0%
3    May     4,550,000       38.5%
4    Jun     5,100,000       39.2%
5    Jul     4,800,000       37.8%
6    Aug     5,300,000       40.1%
7    Sep     6,100,000       41.0%
8    Oct     8,200,000       42.5%
9    Nov     7,400,000       41.8%
10   Dec     6,600,000       40.5%
11   Jan     5,200,000       39.0%
12   Feb     5,000,000       38.7%
13   Mar     5,800,000       39.9%

Make this range a Table first (Ctrl+T) so the chart you build in Step 3 will grow when next April's row is added.

Step 2, the year revenue total. Sum column B. Adding the twelve months:

4,200,000 + 4,550,000 + 5,100,000 + 4,800,000 + 5,300,000 + 6,100,000 + 8,200,000 + 7,400,000 + 6,600,000 + 5,200,000 + 5,000,000 + 5,800,000 = ₹6,82,50,000 (₹6.825 crore). In the workbook this is =SUM(Sales[Revenue]).

Step 3, the average gross margin, two ways (the trap). There are two different "average margins," and confusing them is a real analytical error, not a rounding quibble:

• Simple average of the monthly percentages (=AVERAGE(Sales[Margin%])) treats each month's margin as one equal observation: (38.0 + 38.5 + 39.2 + 37.8 + 40.1 + 41.0 + 42.5 + 41.8 + 40.5 + 39.0 + 38.7 + 39.9) / 12 = 477.0 / 12 = 39.75%.
• Blended (revenue-weighted) margin is the true margin of the whole year, total gross profit divided by total revenue. Compute each month's gross profit as Revenue × Margin% and sum them, which gives total gross profit of ₹2,73,16,050. Then divide by total revenue: 27,316,050 / 68,250,000 = 40.02%.

Why do the two differ, 39.75% against 40.02%? The high-revenue festive months, October and November, also carry the highest margins, so weighting by revenue pulls the true figure up. The blended 40.02% is the correct "our margin this year," and a simple average of percentages is almost always the wrong number to report. A margin line on a chart shows the monthly path, but the headline margin KPI must be revenue-weighted. It's the kind of distinction that separates an analyst from a chart-button-presser. You'll drill it again in Worked example 2 and the practice set.

Step 4, a YoY-style % change to annotate. The exhibit's story is the festive spike from April (₹4,200,000) to October (₹8,200,000). The percentage change uses the same arithmetic as a year-over-year figure, (new − old) / old: (8,200,000 − 4,200,000) / 4,200,000 = 4,000,000 / 4,200,000 = 95.24%. October revenue is up ~95% on April. That's a callout worth putting right on the chart, at the October column.

Step 5, build the combo chart. Select Month, Revenue, Margin%. Go to Insert ▸ Charts ▸ Combo ▸ Create Custom Combo Chart, set Revenue to Clustered Column and Margin% to Line, then tick Secondary Axis for Margin%. The columns now read against the left axis (₹, 0 to ~9,000,000), and the margin line reads against the right axis (%). Format the right axis (Ctrl+1) to a fixed, defensible range of 0% to 50%, so this exhibit and every future month's are comparable. Colour the line and the right-axis labels in one accent, and use another for the bars and left axis. Then apply the clean-exhibit rules and retitle the chart with its message. Instead of "Revenue and Margin", write "Revenue nearly doubled into the festive quarter while margin held above 40%." Direct-label October's column with "+95% vs Apr." Delete the legend and label the line "Gross margin %" at its right end instead. Last, switch the worksheet gridlines off.

Step 6, read it. The exhibit now makes one argument at a glance. The business scaled sharply into the festive season without buying that growth through discounting. You can see it in the bars, which rise steeply while the margin line stays high and even climbs. A table of the same 24 numbers hides that story, and the combo chart puts it in front of you. Notice what kept it truthful. The secondary axis was fixed on stated grounds. The reported margin was the blended 40.02%, and the simple average stayed out of the headline whether it flattered or not. And the source is a Table, so next April's row drops in without a rebuild.

Python verification (every number recomputed):

Year revenue total     = 68,250,000        (₹6,82,50,000)
Total gross profit     = 27,316,050
Simple-average margin  = 39.75%
Blended (weighted)     = 40.02%   (27,316,050 / 68,250,000 = 0.400235)
YoY-style Apr→Oct      = 95.24%   (4,000,000 / 4,200,000)
October gross profit   = 3,485,000         (8,200,000 × 0.425)

Worked example 2: US SaaS KPI tile block (US, $)

The task. A US software company's dashboard opens with three KPI tiles (Revenue, Gross Margin %, and YoY growth), computed from a small quarterly dataset. Build each tile's number from first principles and verify all three in Python. A dashboard that displays a wrong KPI in big, confident type is worse than no dashboard, because it launders a bad number through good design.

Step 1, the data (current fiscal year, four quarters, $):

     A        B (Revenue $)    C (COGS $)
1    Quarter  Revenue          COGS
2    Q1       1,250,000        450,000
3    Q2       1,380,000        480,000
4    Q3       1,510,000        520,000
5    Q4       1,680,000        560,000

The Data sheet also holds prior-year total revenue = $4,900,000, the denominator for YoY.

Step 2: Tile 1, Revenue. Sum the four quarters: 1,250,000 + 1,380,000 + 1,510,000 + 1,680,000 = $5,820,000. Formula: =SUM(Sales[Revenue]). Display it large ("$5.82M") with the tiny context line "+18.8% YoY" beneath it. Never show the bare number.

Step 3: Tile 2, Gross Margin %. Gross profit is revenue minus COGS. The margin is the blended figure, total gross profit over total revenue. Averaging the four quarterly margins would fall into the same trap as the Pune brand's simple average. Total COGS = 450,000 + 480,000 + 520,000 + 560,000 = $2,010,000. Total gross profit = 5,820,000 − 2,010,000 = $3,810,000. Gross margin % = 3,810,000 / 5,820,000 = 65.46%. Formula: =(SUM(Sales[Revenue])-SUM(Sales[COGS]))/SUM(Sales[Revenue]).

Step 4: Tile 3, YoY growth. Year-over-year growth is (current − prior) / prior: (5,820,000 − 4,900,000) / 4,900,000 = 920,000 / 4,900,000 = 18.78%. Formula: =(SUM(Sales[Revenue]) - PriorYearRevenue) / PriorYearRevenue. Colour the tile green for positive growth. Show the sign or an arrow as well, so the meaning doesn't rely on colour alone.

Step 5, wire the tiles to the dashboard. Each tile is a large-font cell (or linked text box) that references the Calc sheet's formula. When a slicer refilters the underlying Table, the tiles recompute live. Add a Revenue sparkline across the four quarters inside the Revenue tile, so the rising trajectory shows in place. Then put a Region slicer on the dashboard and connect it (Slicer ▸ Report Connections) to the PivotTable the tiles are built from. Now a click on "West" reprices all three KPIs at once.

Step 6, read it. The three tiles answer "how big, how profitable, how fast" at a glance: $5.82M revenue, 65.5% gross margin, +18.8% YoY. Each one is a blend or ratio computed from totals, never an average of averages, so the CFO can quote them without a footnote. None of the pretty surface matters if those numbers are wrong. The dashboard's design earns trust only when the numbers behind the tiles are the right numbers.

Python verification (every KPI recomputed):

Current-year revenue   = 5,820,000
Current-year COGS      = 2,010,000
Current-year gross profit = 3,810,000
Gross margin %         = 65.46%   (3,810,000 / 5,820,000 = 0.654639)
Prior-year revenue     = 4,900,000
YoY growth             = 18.78%   (920,000 / 4,900,000 = 0.187755)

Practice set

Work each problem before you read its solution. Where a question asks for a chart choice, name the type and give the one-line reason. Where it asks for a number, compute it. A dashboard is only as trustworthy as the figure behind the tile.

P1 (guided). Chart choice from the question. For each, name the one best chart type and give the one-line reason. (a) Rank eight sales regions by revenue this quarter. (b) Show monthly revenue across a year. (c) Show whether marketing spend and revenue move together across 40 campaigns. (d) Explain how opening cash became closing cash via four inflows/outflows. (e) Show revenue (₹) and gross-margin (%) for twelve months together.

Solution. (a) Bar chart. A comparison across categories reads best as length along a common baseline. Use horizontal bars, since there are several regions and the labels are region names. (b) Line chart. Change over ordered time, where slope = rate of change, is what a line is for. (c) Scatter (XY). A relationship between two value variables needs both axes to be values, so that the shape of the point cloud can reveal correlation. A line chart is wrong here because its x-axis holds categories and not values. (d) Waterfall. A bridge from a start value to an end value through signed steps is what the floating-bar waterfall decomposes. (e) Combo chart with a secondary axis. Two series in different units (₹ and %) can't share one scale, so the % goes on its own right-hand axis.

P2 (guided). Set up the combo chart. You have Month, Revenue (₹), and Margin (%). Describe the exact clicks to make revenue columns on the primary axis and the margin a line on the secondary axis, and state the one axis setting that keeps the exhibit honest.

Solution. Select the three columns including headers, then Insert ▸ Charts ▸ Combo ▸ Create Custom Combo Chart. Set Revenue to Clustered Column and Margin% to Line, then tick Secondary Axis for Margin%. The other route works too. Make a column chart, select the Margin series, Ctrl+1 ▸ Series Options ▸ Secondary Axis, then right-click ▸ Change Series Chart Type ▸ Line. The honesty setting is to fix the secondary-axis Minimum and Maximum on defensible grounds (e.g., 0% to 50%) and keep them constant across periods. Don't accept the auto-scale, because an unanchored secondary axis can be slid to fake a correlation.

P3 (guided). Make the chart grow. A chart is bound to A1:B13 (twelve months). Next month you paste a thirteenth month into row 14 and the chart ignores it. Explain why, and give the two modern fixes.

Solution. The chart stores a fixed source reference ending at row 13 (=SERIES(...,$A$2:$A$13,...)), so row 14 is outside what it plots. Fix 1: convert the source to an Excel Table (Ctrl+T) before charting. A Table auto-extends its series, so a new row flows into the chart immediately. Fix 2 (M365): if the data is produced by a spilling formula, point the chart at the spilled-range reference H2#. It always means "the whole current spill," and it grows as the spill grows. Underneath both is one principle. A chart source should be a rule ("all the data"), never a fixed address.

P4. Year revenue total. A store's monthly revenue for a half-year is ₹520,000, ₹610,000, ₹580,000, ₹700,000, ₹660,000, ₹750,000. Give the six-month revenue total that a KPI tile would display, and the formula.

Solution. =SUM(range) gives 520,000 + 610,000 + 580,000 + 700,000 + 660,000 + 750,000 = ₹38,20,000 (₹38.2 lakh). The tile shows this beside a context line such as vs prior half-year. It never shows the bare total.

P5. Blended vs simple-average margin, the trap. Two months: Month 1 has revenue ₹1,000,000 at 30% gross margin; Month 2 has revenue ₹3,000,000 at 50% gross margin. Compute (a) the simple average of the two margin percentages and (b) the true blended gross margin for the two months together, and say which belongs on the "gross margin" KPI tile.

Solution. (a) Simple average = (30% + 50%) / 2 = 40%. (b) Blended = total gross profit / total revenue = (1,000,000×0.30 + 3,000,000×0.50) / (1,000,000 + 3,000,000) = (300,000 + 1,500,000) / 4,000,000 = 1,800,000 / 4,000,000 = 45%. The blended 45% belongs on the tile, because it's the actual margin of the combined revenue. The simple average of 40% under-weights the larger, higher-margin month, so it's the wrong number to report. Averaging percentages that rest on different-sized bases is one of the most common quiet errors in dashboards.

P6. YoY growth. This year's revenue is $7,200,000; last year's was $6,000,000. Compute the year-over-year growth for the tile, and the formula.

Solution. =(current − prior)/prior gives (7,200,000 − 6,000,000) / 6,000,000 = 1,200,000 / 6,000,000 = 20.0%. Show it with a sign or arrow so the direction doesn't depend on tile colour alone.

P7. Sparklines vs a full chart. You have 30 products, each with 12 months of sales, and you want the reader to scan all 30 trends beside their table rows and spot any product falling off a cliff. Which tool, and the one setting that stops it from misleading?

Solution. Line sparklines, one per product row (Insert ▸ Sparklines ▸ Line; Data Range = each row's 12 months, Location = the cell beside it). The setting that matters is Axis ▸ Vertical Axis Min/Max ▸ Same for All Sparklines, so a big-selling product's spike isn't drawn the same height as a small product's bump. Without a shared axis, sparklines mislead just like a truncated bar axis. Sparklines beat full charts here because the message is the comparison across many rows. The precise values aren't.

P8. Which series belongs on the secondary axis, and the danger. A combo chart plots units sold (tens of thousands) and average selling price ($8–$12). Which goes on the secondary axis, why, and what is the one way this chart can lie?

Solution. Put average selling price on the secondary axis. Its range ($8 to $12) is tiny next to units in the tens of thousands, so on a shared axis the price line would flatline invisibly along the floor. Its own right-hand axis rescales it so you can see it. Now for the lie. The secondary price axis is a scale you set, so sliding its min and max can make price appear to track units or diverge from them at will. That's a manufactured correlation. Fix the price axis on defensible bounds and keep them stable across exhibits.

P9. Gross-margin KPI. A quarter's revenue is $2,500,000 and its COGS is $1,600,000. Compute the gross-margin % the tile displays, and the formula.

Solution. Gross profit = 2,500,000 − 1,600,000 = $900,000. Gross margin % = 900,000 / 2,500,000 = 36.0%. Formula: =(Revenue − COGS)/Revenue. Notice that this is margin, which is based on revenue. Markup is based on cost, and 900,000/1,600,000 = 56.25% would be the markup, a different number and the wrong one for a "margin" tile.

P10. Diagnose the misleading exhibit. A manager shows a 3-D pie of six product shares and a combo chart whose secondary axis was hand-tuned until two lines "look correlated." Name each flaw and the fix.

Solution. (1) 3-D pie. The pie forces poor angle comparison, and the 3-D tilt distorts slice sizes because nearer slices look bigger. Replace it with a flat bar chart of the six shares, sorted and read by length. (2) Hand-tuned secondary axis. Sliding the axis to fabricate a visual correlation is a deception. Fix the secondary-axis bounds on stated, data-driven grounds and keep them constant. If the series really do move together, a fairly drawn scale will show it. If they don't, no scale should pretend they do.

P11 (timed: 6 min). Build a KPI block end-to-end. A firm's four quarters of revenue ($) are 900,000, 950,000, 1,010,000, 1,140,000; its four quarters of COGS ($) are 360,000, 370,000, 400,000, 450,000; prior-year revenue was $3,600,000. Compute the three KPI-tile numbers: Revenue, blended Gross Margin %, and YoY growth.

Solution. Revenue = 900,000 + 950,000 + 1,010,000 + 1,140,000 = $4,000,000. Total COGS = 360,000 + 370,000 + 400,000 + 450,000 = $1,580,000. Gross profit = 4,000,000 − 1,580,000 = $2,420,000, so blended gross margin = 2,420,000 / 4,000,000 = 60.5%. YoY growth = (4,000,000 − 3,600,000) / 3,600,000 = 400,000 / 3,600,000 = 11.11%. All three are ratios or sums over totals, so when a slicer refilters the source they recompute live and correctly.

P12 (timed: 6 min). Choose the exhibit. For each dashboard need, name the one best exhibit and justify it in one line. (a) The headline "how big, how profitable, how fast" at the top of the dashboard. (b) Revenue and margin for twelve months in one frame. (c) Each of 12 sales reps' 12-month trend beside their name. (d) Comparing the same monthly-revenue shape across six regions without a tangle. (e) Letting the manager refilter the whole dashboard to one region.

Solution. (a) KPI tiles. A big number with context is the fastest possible "at a glance." (b) A combo chart with margin on a secondary axis, so two units share one frame. (c) Line sparklines, one per row. They're word-sized small multiples, made for scanning many trends. (d) Small multiples, six small, identically scaled line charts in a grid. The outlier region pops out, where a six-line spaghetti chart would bury it. (e) A slicer connected to every PivotTable via Report Connections, so one click refilters the entire dashboard coherently.

Applied mini-project

Build a one-screen sales KPI dashboard.

Assemble a complete, interactive dashboard that turns a raw sales Table into a single screen a manager can operate: KPI tiles up top, a headline combo chart, small multiples, and slicers that drive everything at once. Throughout, no number gets typed twice, no chart is bound to a fixed range, and no exhibit carries more than one message.

Set up the data and calc layers (keep presentation separate):

1. Data sheet: one raw Table (Ctrl+T) named Sales with at least Date, Region (4 regions), Product, Revenue, and COGS, and at least 12 months × several regions of rows. Also store a single PriorYearRevenue cell for the YoY denominator.
2. Calc sheet: a PivotTable (or a SUMIFS/XLOOKUP block) that produces monthly revenue, monthly COGS, and monthly gross margin, plus the three headline KPIs. Those are total Revenue, blended Gross Margin % (total gross profit ÷ total revenue, never an average of monthly margins), and YoY growth.

Build the Dashboard sheet (presentation only; View ▸ Gridlines off, Headings off):

1. Three KPI tiles across the top (Revenue, Gross Margin %, YoY growth). Each is a big number with a small context line (vs prior period / target), and the Revenue tile also gets an in-cell sparkline of the monthly trend. The Gross Margin tile must show the blended figure. If you display a simple average of monthly margins by mistake, mark it wrong in your self-score.
2. A headline combo chart: monthly revenue as columns on the primary axis and gross-margin % as a line on the secondary axis, bound to a Table or spilled # range so it grows. Fix the secondary axis on defensible bounds. Give it a title that states the message, which "Revenue and Margin" doesn't. Delete the legend in favour of direct labels, and switch the worksheet gridlines off.
3. Small multiples: one small, identically scaled line chart (or a sparkline column) per region, so the six regional trends compare frame to frame without tangling into a spaghetti chart.
4. A slicer (Region) and a Timeline (Date), each connected via Report Connections to every PivotTable feeding the tiles and charts, so one click refilters the whole screen coherently.
5. Clean-exhibit pass: on every chart, delete the non-data ink (border, heavy gridlines, background fill). Use grey for context and a single accent for the series that carries the message. Confirm that each exhibit states one message only, and that the whole thing reads in greyscale.
6. Robustness pass: add a new month's rows to the Sales Table and confirm that every tile, the combo chart, and the small multiples update without a rebuild. Then click through the slicers and confirm all the elements refilter together.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. Its tile-and-slicer skeleton becomes the dashboard template you'll reuse whenever a model needs a presentation layer.

Lab: open the Charts & dashboard craft workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

Craft and perception (read these first):

• Edward Tufte, The Visual Display of Quantitative Information, the source of the data-ink ratio and the sparkline. It's the one book that changes how you see every chart. [Paid] [Intermediate]
• Cole Nussbaumer Knaflic, Storytelling with Data (and the storytellingwithdata.com blog), the most practical modern treatment of decluttering, direct-labelling, and "one message per chart," with before/after makeovers. [Paid book / Free blog] [Beginner]

Excel-specific how-to:

• Microsoft Learn, the official pages for combo charts, sparklines, slicers, and Timelines (search "Excel combo chart Microsoft support", etc.). They give authoritative click-paths and version notes. [Free] [Beginner]
• ExcelJet (exceljet.net), concise entries on dynamic chart ranges via Tables and the spilled # operator, plus chart-formatting recipes. [Free] [Beginner]
• Chandoo (chandoo.org), dashboard build-alongs and the classic "Excel dashboard" series with downloadable files. It's strong on slicer-driven interactivity. [Free] [Intermediate]

Judgment and anti-patterns:

• Any reputable "why pie charts (and 3-D) mislead" explainer. Search "pie chart perception bar chart" and internalise the angle-vs-length argument before you inherit a deck full of 3-D pies. [Free] [Beginner]

Bridge forward: the PivotTable/PivotChart engine used lightly here for the tiles and slicers is the whole subject of EX3.01. Reread its intro once your dashboard's slicers are driving multiple PivotTables cleanly.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "The arithmetic honesty of a chart — what an axis floor and a secondary-axis span actually do to the visual impression of unchanged underlying numbers",
  "use": "An AI image or design tool can generate a chart in seconds and, unprompted, will often pick an axis that flatters whichever trend looks best — exactly the failure this module warns against. Use an assistant instead to COMPUTE the honesty check: given a data series and a proposed axis floor or secondary-axis span, ask it to report the visual distortion factor before you look at the picture, so the number, not your eye, is what you judge the chart choice against.",
  "tools": ["Coding assistants — Claude, ChatGPT, or a notebook's inline assistant for the deception-factor and zoom-amplification code below", "Chart/design AI — useful for a first-draft layout, never for choosing axis bounds unsupervised", "Chat assistants — for explaining what a colleague's already-built chart implies before you present it"],
  "prompt": "Here is a data series and a chart axis I am considering: <PASTE values, proposed axis min/max>. Compute the ratio between the visual size difference the axis would produce and the true ratio in the data (a 'deception factor'). Do not tell me whether the axis is 'fine' or 'misleading' — give me the number, and I will decide against my own honesty standard.",
  "verify": "Recompute the true percentage difference between the two data points by hand from the raw numbers, then re-derive the visual ratio the proposed axis would produce, and confirm the two match what the assistant reported — an assistant asked to justify a client's preferred axis will sometimes round the honesty check in the client's favour.",
  "diy": "The gate is unaided: given two values and a proposed axis floor, you compute by hand how many times more dramatic the axis makes the visual difference look versus the true percentage change, with no tool in the room."
}

Modern Data Analysis

By hand first. You can already choose the chart type from the question, build the combo chart with a deliberately fixed secondary axis, and spot the blended-vs-simple-average KPI trap. In Excel, a chart's axis bounds are set in a formatting dialog that a reader of the finished exhibit never sees. Whether they were left on "Auto" or typed in by hand, the final picture looks the same.

Today's workflow. A chart built in code states its axis bounds as a literal line. ax.set_ylim(4_000_000, None) is a visible, reviewable decision, where a dialog box is something someone clicked through once and forgot. That doesn't make code-built charts automatically honest. It makes the dishonesty auditable, because the axis choice is now text in a script that a colleague (or a future you) can read, diff, and question. You'll hear the same reproducibility argument about Power Query, where a query is a recipe and not a snapshot.

Tools & sources (IN + US). matplotlib for the axis-bound and correlation code below. For India, the same kind of monthly revenue-and-margin series a D2C brand's finance team already tracks, shaped like the Pune brand's data in the first worked example. For the US, a comparable SaaS quarterly KPI series, shaped like the second worked example's tiles.

# recomputed in-session — R3
revenue = [4200000,4550000,5100000,4800000,5300000,6100000,8200000,7400000,6600000,5200000,5000000,5800000]
apr, oct_ = revenue[0], revenue[6]
true_ratio = oct_ / apr
for floor in [0, 4_000_000]:
    visual_ratio = (oct_-floor) / (apr-floor)
    print(floor, round(visual_ratio, 2))   # 0 -> 1.95 (true); 4,000,000 -> 21.0 (distorted)

Verify. The year revenue total recomputes to ₹6,82,50,000, which matches the first worked example. The true April-to-October ratio recomputes to 1.9524×, which matches its "up ~95%" callout (8,200,000/4,200,000 − 1 = 95.24%).

Quantitative lens

Axis truncation has a computable "deception factor," and it grows fast. Take the Pune brand again. April's revenue is ₹42,00,000 and October's is ₹82,00,000, a true ratio of 1.95×, which is the "+95%" callout from the first worked example. Draw the columns on a zero-based axis and the bar heights show that same 1.95× faithfully. Now move the axis floor to ₹40,00,000. It looks like a modest crop, but the visible bar-height ratio becomes (82,00,000−40,00,000)/(42,00,000−40,00,000) = 21.0×. That's a deception factor of 21×, measured as the visual ratio's excess over 1 divided by the true ratio's excess over 1. Crop the floor further, to ₹41,50,000, and the deception factor reaches 84×.

The true difference hasn't moved at all. Only the floor has.

The span of a secondary axis, as well as its presence, sets how dramatic a line looks. The Pune brand's margin line runs from 37.8% to 42.5% across the year, a true range of 4.7 percentage points. Plot it on the defensible 0 to 50% axis chosen in the worked example, and that range fills just 9.4% of the vertical space. It reads as flat and stable, which is the right impression, because a margin that swings by under five points really is stable. Re-plot the same twelve numbers on a "zoomed-in" 37 to 43% axis and the range now fills 78.3% of the space. The picture reads as sharp and volatile, 8.33 times more dramatic-looking than the fixed-axis version, from data that hasn't changed by a single decimal. Neither axis lies about any individual number. When there is dishonesty, it comes from letting the axis span do the arguing that the data itself doesn't support.

Correlation is invariant to axis choice, so the choice of axis is a presentation issue and not a statistics one. The Pearson correlation between the Pune brand's revenue and margin series is r = 0.9513, and that number stays identical whatever secondary-axis bounds the chart uses. Zooming the margin axis changes how tightly the two lines seem to track each other on screen. It can't move the one number that actually measures how tightly they move together. If you tighten the axis to make the relationship look stronger, you're manufacturing an impression the statistic itself doesn't endorse.

Limits: "deception factor" and "zoom amplification" are this addendum's own descriptive terms for the visual-versus-true ratio. They aren't standardised statistical measures. They put a number on a real effect, but if you meet them elsewhere, don't assume a universally defined metric. Both examples use the actual monthly series from the first worked example, and the specific multiples (21×, 84×, 8.33×) belong to this series and these particular axis choices. They aren't universal constants. Run the same arithmetic on a flatter or steeper series and you'll get different factors, though the mechanism, a fixed true difference over a shrinking denominator, is general.

Where this goes next: galaxy cross-links

• G2 · DA1.04 EDA & Visualization: the Python-native continuation of the chart-choice grammar you've just learned, where axis bounds are a reviewable line of code and not a dialog-box setting.
• G3 · CN1.03 The "So What": Synthesis & the Answer-First Storyline, a chart's title and axis together make a claim, and the honesty rule here is the visual half of that same synthesis work.
• G1 · QM1.02 Hypothesis Testing, Correlation & Regression, correlation's invariance to axis choice is a direct instance of that module's distinction between a statistic and its visual presentation.

Teach it back & journal

Feynman prompt. Explain to a shopkeeper who has never made a chart why they shouldn't put their six product shares in a 3-D pie. Use only the metaphor of comparing slices of a cake seen from the side with sticks of different length laid on the table. When the cake is tilted and seen edge-on, the nearer slices look bigger even when they aren't. Nobody can tell whether one wedge is a fifth or a quarter without the numbers printed on it, so the picture does no work. Sticks laid flat on the table can be compared by length at a glance. Only in your last two sentences may you use the words bar chart, pie chart, and the eye judges length well but angle and area badly, and attach each one to the moment in the cake-and-sticks story that it explains.

Journal prompt. Find one chart you (or your team) actually presented recently, in a deck, a report, a status email. Test it against three of the rules you've learned here. (1) Its title should state the message and not just name the metric. (2) Any non-data ink competes with the one thing you wanted the reader to see. That includes a legend you could replace with direct labels, gridlines, a 3-D effect, a rainbow palette. (3) If it shows an average margin or rate, decide whether it's a blended figure (weighted by the right base) or a naïve average of percentages that misstates the truth without anyone noticing. Rebuild the chart to fix whatever you found. Then write the single sentence you want to remember forever. Make it about the difference between a chart that is technically a chart and an exhibit that makes your one point land, and why the reader's four seconds of attention is the scarcest resource you're designing for.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.