The first thing most people do when they need a stemplot is search for ‘stemplot chart’ in Excel’s Insert tab — then get frustrated when nothing appears. Worse, some try forcing it with scatter plots or bar charts, stacking numbers haphazardly across columns. That’s not a stemplot. It’s visual noise — and it breaks the core rule: stems must be ordered, leaves must align right-justified under consistent place values. You’ll waste 20 minutes before realizing Excel won’t auto-generate this.
Quick Answer
You can’t insert a stemplot directly in Excel — but you can build one manually in 3 columns: one for stems (tens/hundreds), one for sorted leaves (units), and one for formatted display. For datasets under 100 values, use formulas like INT(), MOD(), and TEXTJOIN(); for larger sets, Power Query + TEXTSPLIT (Excel 365) cuts time by 70%.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Manual Formula Build | Sort data → extract stems (INT(A2/10)) → extract leaves (MOD(A2,10)) → group & TEXTJOIN | Small samples (≤50 values); learning fundamentals | Fails with decimals >1 digit; no auto-resort on edit |
| Power Query + TEXTSPLIT | Import data → add Custom Column for stem/leaf → Group By stem → TEXTJOIN leaves | Datasets 50–500 values; reusable workflow | Requires Excel 365 or 2021; no native support for negative numbers |
| VBA Macro | Paste code → run → outputs formatted table in new sheet | Teams reusing stemplots weekly; IT-approved environments | Macro security blocks; requires admin enablement |
| Copy-Paste from R/Python | Generate in external tool → paste as plain text → clean whitespace | Analysts already using R (stem()) or Python (matplotlib) | Breaks Excel-only workflow; formatting lost on paste |
Method 1 Deep Dive
Let’s say your sales team logged 12 quarterly deal sizes (in $1,000s):A1:A12 = {23, 47, 31, 19, 55, 28, 42, 36, 14, 51, 29, 38}
Start by sorting: select A1:A12 → Alt + A + S + S → OK. Now your data sits in B1:B12.
Next, build stems in column D. In D1, enter =INT(B1/10). Drag down to D12. You’ll get {1,1,2,2,2,3,3,3,4,4,5,5}.
Leaves go in E1: =MOD(B1,10). Drag down. You now have stems and leaves side-by-side — but ungrouped. Here’s the counterintuitive part: don’t use PivotTables. They can’t sort leaves numerically *within* each stem row. Instead, use FILTER and TEXTJOIN.
In F1, type:=TEXTJOIN(" ",TRUE,FILTER($E$1:$E$12,$D$1:$D$12=1))
That gives you leaves for stem “1”: 4 9. Copy that formula down for stems 2 through 5, changing the last digit each time. Final output:
| Stem | Leaves | Formatted Stemplot Row |
|---|---|---|
| 1 | 4 9 | 1 | 4 9 |
| 2 | 3 8 9 | 2 | 3 8 9 |
| 3 | 1 6 8 | 3 | 1 6 8 |
| 4 | 2 7 | 4 | 2 7 |
| 5 | 1 5 | 5 | 1 5 |
Pro tip: Use REPT(" ", 2) inside TEXTJOIN to pad single digits — keeps alignment clean if you later paste into reports.
Method 2 Deep Dive
Now imagine you’re reviewing 87 customer satisfaction scores (1–100 scale) from Sheet2! A1:A87 holds values like 67, 82, 45, 91.... Manual formulas will take 15+ minutes and break if someone inserts a row.
Open Power Query: Data → Get Data → From Table/Range (select A1:A87, check “My table has headers” → OK). In the Query Editor, add a custom column:= Number.IntegerDivide([Score], 10) → name it “Stem”.
Add another: = Number.Mod([Score], 10) → name it “Leaf”.
Select the Stem column → Group By (Transform tab) → Operation: “All Rows”, New column name: “Grouped”. Then add a final custom column:= Text.Combine(List.Transform([Grouped][Leaf], each Text.From(_)), " ")
Remove unnecessary columns → Close & Load to new worksheet. You’ll get two clean columns: Stem (1–9) and Leaves (space-separated digits). Paste into G1:H10. To format as a true stemplot, insert column I and use:=G1&" | "&H1 in I1, then drag down.
This method updates automatically when source data changes — just right-click the output table → Refresh. No formula dragging. No broken references.
Cheat Sheet
| Task | Formula / Action | Shortcut / Note |
|---|---|---|
| Sort raw data | =SORT(A1:A12) |
Alt + A + S + S (for legacy Excel) |
| Extract stem (tens) | =INT(B1/10) |
Use ROUNDOWN(B1,-1)/10 for negatives |
| Join leaves per stem | =TEXTJOIN(" ",1,FILTER(E1:E12,D1:D12=2)) |
FILTER only works in Excel 365/2021+ |
| Load into Power Query | Data → From Table/Range → OK | Ctrl + T first if data isn’t a table |
| Format final row | =D1&" | "&E1 |
Use monospace font (e.g., Consolas) for alignment |
