How to Create a Stem-and-Leaf Plot in Excel

Key Insights

Excel lacks a native stem-and-leaf plot feature, but you can build one using a combination of INT(), MOD(), and TEXTJOIN() functions that separate and regroup your data systematically.
Stem-and-leaf plots preserve individual data points while showing distribution shape—making them superior to histograms when you need to see exact values or work with small to medium datasets (under 100 observations).
A well-structured approach using helper columns keeps your formulas maintainable and makes it easy to update the plot when your source data changes.

Introduction to Stem-and-Leaf Plots

Stem-and-leaf plots are one of the most underrated tools in exploratory data analysis. They split each data point into a “stem” (typically the leading digits) and a “leaf” (the trailing digit), then arrange leaves horizontally next to their corresponding stems. The result is a visualization that shows distribution shape like a histogram while preserving every original value.

Consider a dataset of exam scores: 73, 78, 82, 85, 85, 87, 91, 94. A histogram might show you that most scores fall in the 80s, but a stem-and-leaf plot shows you exactly which scores: the stem “8” has leaves “2 5 5 7”. You see both the pattern and the particulars.

Excel doesn’t include a built-in stem-and-leaf chart type. This isn’t surprising—Microsoft focuses on common business visualizations, not statistical teaching tools. But with the right formula approach, you can construct stem-and-leaf plots that update dynamically when your data changes. Let’s build one from scratch.

Preparing Your Data

Start with your raw data in a single column. For this walkthrough, I’ll use test scores ranging from 23 to 98. Place these values in column A, starting at A2 (keep A1 for a header).

The first step is sorting. Stem-and-leaf plots must display data in ascending order—this is non-negotiable. You have two options: sort the source data directly, or use a formula to create a sorted copy.

For a dynamic approach that doesn’t modify your original data, use the SORT() function:

=SORT(A2:A31)

Place this in a new column (say, column C starting at C2). This spills the sorted values downward automatically. If you’re on an older Excel version without dynamic arrays, you’ll need to manually sort column A instead: select your data, go to Data → Sort, and choose smallest to largest.

For datasets with decimal values, decide upfront how to handle precision. The standard approach is to multiply by 10 (or 100) to shift the decimal point, then apply the stem-and-leaf logic to integers. For example, values like 7.3, 8.1, 9.4 become 73, 81, 94 before processing. Document this in your plot’s legend so readers know to mentally divide by 10.

=SORT(A2:A31*10)

This formula sorts and scales in one step. Your legend would then read: “Leaf unit = 0.1” to indicate each leaf represents a tenth.

Extracting Stems and Leaves with Formulas

With sorted data in column C, you’ll create two helper columns: one for stems and one for leaves. The math is straightforward integer division.

For the stem (the tens digit in two-digit numbers), use INT() to perform floor division:

=INT(C2/10)

Place this in column D. For a value like 73, this returns 7. For 85, it returns 8.

For the leaf (the units digit), use MOD() to get the remainder:

=MOD(C2,10)

Place this in column E. For 73, this returns 3. For 85, it returns 5.

Copy both formulas down to cover all your data rows. You now have parallel columns showing each value decomposed into its stem and leaf components.

Here’s what your worksheet should look like at this point:

| A (Raw)  | C (Sorted) | D (Stem) | E (Leaf) |
|----------|------------|----------|----------|
| 67       | 23         | 2        | 3        |
| 85       | 45         | 4        | 5        |
| 73       | 48         | 4        | 8        |
| 91       | 52         | 5        | 2        |
| ...      | ...        | ...      | ...      |

For three-digit numbers, adjust your divisor. To split 234 into stem 23 and leaf 4:

=INT(C2/10)    ' Returns 23
=MOD(C2,10)    ' Returns 4

The same formulas work—you’re always isolating the final digit as the leaf.

Aggregating Leaves by Stem

This is where the real work happens. You need to collect all leaves that share the same stem and display them as a horizontal string.

First, create a unique list of stems. In column G, starting at G2, list each possible stem value in your data range. If your data spans from 23 to 98, your stems are 2, 3, 4, 5, 6, 7, 8, 9. You can type these manually or extract unique values with:

=UNIQUE(D2:D31)

Now, in column H, use TEXTJOIN() with an IF() condition to concatenate all leaves matching each stem. This is an array formula in older Excel versions:

=TEXTJOIN(" ",TRUE,IF($D$2:$D$31=G2,$E$2:$E$31,""))

In Excel 365 or Excel 2021, this enters normally. In Excel 2019 or earlier, press Ctrl+Shift+Enter to enter it as an array formula (you’ll see curly braces around it in the formula bar).

Breaking down this formula:

$D$2:$D$31=G2 creates an array of TRUE/FALSE values indicating which rows have a stem matching G2
IF(...,$E$2:$E$31,"") returns the leaf value where TRUE, empty string where FALSE
TEXTJOIN(" ",TRUE,...) concatenates all non-empty results with spaces between them

For stem 8 with leaves 2, 5, 5, 7, the result is “2 5 5 7”.

Copy this formula down for each stem value. You now have the core of your stem-and-leaf plot.

Formatting the Final Display

A proper stem-and-leaf plot needs visual structure. Create a display column that combines the stem, a separator, and the leaves:

=G2&" | "&H2

This produces output like “8 | 2 5 5 7”. Place this in column I and copy down.

For better alignment, especially with single-digit stems, use TEXT() to pad the stem:

=TEXT(G2,"0")&" | "&H2

Or for right-alignment with variable-width stems:

=REPT(" ",2-LEN(G2))&G2&" | "&H2

Your final display column should look like this:

2 | 3
3 | 
4 | 5 8
5 | 2 6 7
6 | 1 3 7 8 9
7 | 0 2 3 5 8 8 9
8 | 1 2 4 5 5 7 9
9 | 1 3 4 8

Add a legend cell explaining the notation. A common format:

Key: 7 | 3 = 73

For decimal data that you scaled earlier:

Key: 7 | 3 = 7.3

Format the display column with a monospace font like Consolas or Courier New. This ensures leaves align vertically, making the distribution shape easier to read.

Automating with VBA (Optional)

If you create stem-and-leaf plots regularly, a VBA macro saves significant time. This procedure takes a selected range and outputs a formatted plot:

Sub CreateStemLeafPlot()
    Dim dataRange As Range
    Dim cell As Range
    Dim stems As Object
    Dim minStem As Long, maxStem As Long
    Dim i As Long, stemVal As Long, leafVal As Long
    Dim outputRow As Long
    Dim sortedData() As Variant
    Dim n As Long, j As Long, temp As Variant
    
    Set dataRange = Selection
    Set stems = CreateObject("Scripting.Dictionary")
    
    ' Load and sort data
    n = dataRange.Cells.Count
    ReDim sortedData(1 To n)
    i = 1
    For Each cell In dataRange
        If IsNumeric(cell.Value) And cell.Value <> "" Then
            sortedData(i) = cell.Value
            i = i + 1
        End If
    Next cell
    n = i - 1
    ReDim Preserve sortedData(1 To n)
    
    ' Simple bubble sort
    For i = 1 To n - 1
        For j = i + 1 To n
            If sortedData(j) < sortedData(i) Then
                temp = sortedData(i)
                sortedData(i) = sortedData(j)
                sortedData(j) = temp
            End If
        Next j
    Next i
    
    ' Build stem-leaf dictionary
    For i = 1 To n
        stemVal = Int(sortedData(i) / 10)
        leafVal = sortedData(i) Mod 10
        If stems.Exists(stemVal) Then
            stems(stemVal) = stems(stemVal) & " " & leafVal
        Else
            stems.Add stemVal, CStr(leafVal)
        End If
    Next i
    
    ' Output results
    outputRow = 2
    Cells(1, 10).Value = "Stem-and-Leaf Plot"
    
    minStem = Application.WorksheetFunction.Min(stems.Keys)
    maxStem = Application.WorksheetFunction.Max(stems.Keys)
    
    For i = minStem To maxStem
        Cells(outputRow, 10).Value = i & " |"
        If stems.Exists(i) Then
            Cells(outputRow, 11).Value = stems(i)
        End If
        outputRow = outputRow + 1
    Next i
    
    Cells(outputRow + 1, 10).Value = "Key: " & minStem & " | x = " & minStem & "x"
End Sub

To use this macro: select your data range, press Alt+F8, choose CreateStemLeafPlot, and click Run. The plot appears starting at column J.

Interpreting Your Plot

Once built, your stem-and-leaf plot reveals several statistical properties at a glance.

Distribution shape: Rotate the plot 90 degrees mentally (or literally). A symmetric mound suggests normal distribution. A tail stretching right indicates positive skew; left indicates negative skew.

Central tendency: The median sits at the middle value. Count total leaves, divide by two, and count to that position. With 30 data points, the median is between the 15th and 16th values.

Spread and outliers: Look for gaps in stems or isolated leaves far from the cluster. A stem with no leaves followed by one with many suggests a gap in your data. Single leaves several stems away from the main group are potential outliers worth investigating.

Clusters and modes: Multiple peaks in leaf density indicate multimodal data. This might suggest subgroups in your population—perhaps two distinct cohorts combined in one dataset.

Stem-and-leaf plots work best with 15-100 data points. Fewer than 15 doesn’t show meaningful patterns; more than 100 creates unwieldy leaf strings. For larger datasets, consider splitting stems (using “low” and “high” rows for each stem) or switching to histograms.