A stem and leaf chart is a useful way to organize and visualize numerical data in Excel. This method allows you to see the distribution of numbers at a glance.
Table of contents
YouTube: Stem Leaf Chart in Excel using dynamic arrays
Sorting the data
Before starting, it’s best to sort your numbers in ascending order to make it easier to extract the stems and leaves. To do this, go to Data, then Sort, and choose Continue with the current selection (if asked). Make sure to specify that the list has headers, then sort the values from smallest to largest as per in the image below.
Extracting the stem values
To create the stem portion of the chart, you need to extract the first digit of each number. Use the LEFT function by selecting the cell containing the number and specifying that you only want the first character. Drag the formula down to fill the column. The image shows the extracted stems, which represent the leading digits of the numbers..
Extracting the leaf values
Next, extract the second digit (the leaf) using the MID function. This function allows you to pull characters from a specific position in a number. Select the number column, set the starting point to the second character, and input a large number (e.g., 100) to ensure all digits are captured (in the case where there is more than 2 digits). Copy the formula down, and as shown in the image, the second digit is correctly extracted for each number.
Generating unique stem values
Now, create a list of unique stems so that each number appears only once in the chart. Instead of manually entering values, use the new UNIQUE function (dynamic array). Select the column containing the stem values, and Excel will automatically generate a distinct list of numbers. In the image, you can see that duplicate values are removed, leaving only the unique stems. Because the numbers were previously sorted, this will also be in ascending order.
Grouping leaves using FILTER
To pair leaves with their respective stems, use the FILTER function. Select the leaf column and set the condition to filter only values corresponding to each stem. The formula dynamically updates whenever new numbers are added or changed. However, the leaves initially appear in a vertical format, as shown in the image.
Transposing the leaves for better visualization
Since a traditional stem and leaf chart arranges leaves horizontally, adjust the layout using the TRANSPOSE function. Add TRANSPOSE at the beginning of the existing dynamic array formula, and the leaves will now align from left to right. The image illustrates how this adjustment improves readability.
Handling single-digit numbers
If your dataset contains single-digit numbers, Excel may not format them correctly. By default, Excel removes leading zeros, which affects the stem and leaf separation. To correct this, manually enter single-digit numbers as ’01 (with an apostrophe) to force Excel to recognize them as text. The image shows how this adjustment properly assigns a stem of 0 and a leaf of 1.
Tips and troubleshooting
- Incorrect stem or leaf values: Check that your formulas are correctly referencing the right columns and that your data is sorted before extracting values.
- Numbers not aligning correctly: Ensure you use absolute references ($) when applying formulas like FILTER to prevent errors.
- Single-digit numbers not displaying correctly: Use an apostrophe before the number to force Excel to retain the leading zero/s.
- Data updates not reflecting in the chart: If new numbers are added, refresh formulas by reapplying SORT and ensuring the UNIQUE function is still valid.
- Filter formula not working: Double-check that your condition references the correct cell and that the range includes all necessary data.