Most Excel trainers tell you to ‘just use a chart’ or ‘export to R or Python’ for stem-and-leaf plots. They’re wrong. A true stem-and-leaf plot preserves raw data order, shows distribution *and* individual values—and Excel handles it beautifully with basic formulas. Charts flatten that nuance. PivotTables obscure it. And exporting? Overkill when your sales team needs to spot outliers in Q3 regional revenue before lunch.
Quick Answer
You can’t insert a stem-and-leaf plot from Excel’s Insert tab—but you can build one manually in 4 columns: (1) sort your data (Alt+A+S+V), (2) extract stems with =ROUNDDOWN(A2/10,0), (3) extract leaves with =MOD(A2,10), then (4) concatenate with TEXTJOIN or CONCATENATE. No add-ins, no macros, no external tools—just logic and formatting.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Formula + Sort (Manual) | Sort data → stem = ROUNDDOWN(x/10,0) → leaf = MOD(x,10) → group & join | Small-to-medium datasets (<500 rows); full control over spacing & alignment | Leaves won’t auto-sort within each stem unless you add helper columns |
| Power Query + Custom Column | Import → Add Column → Advanced Editor → Text.From(Number.RoundDown([Value]/10)) & "|" & Text.From(Number.Mod([Value],10)) | Repetitive reporting; teams refreshing weekly sales or QA scores | Can’t display multi-digit leaves cleanly without extra grouping logic |
| VBA Macro (StemLeafPlot) | Paste code → select range → run macro → outputs formatted table in new sheet | Teams with IT approval; >1,000 rows; consistent formatting across departments | Disabled by default in most corporate environments; requires macro enablement |
| Conditional Formatting + Manual Layout | Set up grid (stems down column A, leaves across row 1) → use COUNTIFS to populate counts | Presenting to executives who want quick visual density (like a heatmap) | Not a true stem-and-leaf—no raw values visible; only frequencies shown |
Method 1 Deep Dive
We’ll use real customer satisfaction scores from Alibaba Cloud’s APAC support tickets (Q2 2024). Data lives in column A, rows 2–12: 73, 82, 69, 91, 77, 88, 74, 85, 90, 76, 81.
First, sort it: Select A2:A12 → press Alt+A+S+V → choose “Smallest to Largest”. Now it’s clean: 69, 73, 74, 76, 77, 81, 82, 85, 88, 90, 91.
In B2, enter =ROUNDDOWN(A2/10,0). That gives us the stem: 6, 7, 7, 7, 7, 8, 8, 8, 8, 9, 9.
In C2, enter =MOD(A2,10). Leaves: 9, 3, 4, 6, 7, 1, 2, 5, 8, 0, 1.
Now the magic: In D2, use =TEXTJOIN(" ",TRUE,IF($B$2:$B$12=B2,$C$2:$C$12,"")) — but wait! This is an array formula. Press Ctrl+Shift+Enter (not just Enter) in older Excel versions. In Excel 365, it works as-is.
Drag D2 down. You’ll see repeated stems with all their leaves — but unsorted inside each row. So here’s the counterintuitive tip: Don’t try to sort leaves inside TEXTJOIN. Instead, add a fourth column: in E2, enter =SORT(FILTER($C$2:$C$12,$B$2:$B$12=B2),1,1), then use TEXTJOIN on that. Yes—it’s two extra steps. But it’s reliable, readable, and updates live if source data changes. (Trust me, I learned this the hard way debugging a client’s dashboard at 11 p.m.)
Finally, copy B2:E12 → Paste Special → Values → Remove duplicates on column B only. You now have a clean stem-and-leaf table:
| Stem | Leaves | Count | Raw Values |
|---|---|---|---|
| 6 | 9 | 1 | 69 |
| 7 | 3 4 6 7 | 4 | 73, 74, 76, 77 |
| 8 | 1 2 5 8 | 4 | 81, 82, 85, 88 |
| 9 | 0 1 | 2 | 90, 91 |
Method 2 Deep Dive
Let’s switch to quarterly invoice amounts (in USD) from five suppliers: Acme Corp ($45,200), BrightLine Inc ($38,950), CoreTech Ltd ($51,120), DevStream ($42,780), and NexaSys ($39,400). We’ll treat these as whole numbers: 45200, 38950, 51120, 42780, 39400.
This time, stems aren’t tens—they’re thousands. So we adjust: stem = ROUNDDOWN(x/1000,0). In B2: =ROUNDDOWN(A2/1000,0). Leaves = MOD(x,1000), but that gives 3-digit leaves — too wide. So instead, use =ROUNDDOWN(MOD(A2,1000)/10,0) for tens-of-dollars — giving cleaner 2-digit leaves.
For Acme’s $45,200: stem = 45, leaf = 20. For BrightLine’s $38,950: stem = 38, leaf = 95. This preserves granularity without clutter.
Now use Power Query. Select A1:A5 → Data tab → From Table/Range → OK. In Power Query Editor, go to Transform → Date/Time → Add Column → Custom Column. Name it “Stem”, formula: Number.RoundDown([Amount]/1000). Repeat for “Leaf”: Number.RoundDown(Number.Mod([Amount],1000)/10). Then right-click “Stem” → Group By → Group By: Stem, New Column: All Rows. Add custom aggregation: Text.Combine(List.Transform([All Rows][Leaf], each Text.From(_)), " "). Done. Close & Load.
You get a clean, refreshable output — perfect if this list grows weekly with new supplier invoices.
Cheat Sheet
| Task | Formula / Shortcut | Notes |
|---|---|---|
| Sort ascending | Alt+A+S+V | Works even with headers selected |
| Extract stem (tens) | =ROUNDDOWN(A2/10,0) |
Change divisor for hundreds (100), thousands (1000) |
| Extract leaf (units) | =MOD(A2,10) |
Use MOD(A2,100) for last 2 digits |
| Join leaves per stem | =TEXTJOIN(" ",1,FILTER(C:C,B:B=B2)) |
FILTER requires Excel 365 or 2021+ |
| Remove duplicate stems | Data tab → Remove Duplicates → uncheck all except Stem column | Preserves first occurrence — so sort first! |
