Create Stem and Leaf in Excel

Organizing and analyzing data is a fundamental aspect of data analysis, and one effective way to do this is by creating stem-and-leaf plots. While Excel doesn’t have a built-in feature specifically for stem-and-leaf plots, you can create one using a combination of formulas and sorting. Here’s how you can create a stem-and-leaf plot in Excel for a set of data.

Step 1: Prepare Your Data

First, ensure your data is in a single column. For this example, let’s say your data is in column A, starting from A2.

Step 2: Separate the Stem from the Leaf

Assuming your data is numeric and you want to create a stem-and-leaf plot where the stem is the first digit and the leaf is the second digit (for single or double-digit numbers), you can use the following formulas:

• For the Stem: In cell B2, enter the formula =INT(A2/10) and copy it down. This formula works for numbers up to 99. For larger numbers, you’ll need to adjust the divisor accordingly.
• For the Leaf: In cell C2, enter the formula =A2-INT(A2/10)*10 and copy it down. This formula calculates the remainder when the number is divided by 10, giving you the last digit.

Step 3: Round the Data for Leaf (If Necessary)

If your data has decimal points and you only want to consider the integer part for the stem-and-leaf plot, ensure your data in column A is rounded appropriately before creating the stem and leaf columns.

Step 4: Sorting

1. Select the entire data range including headers (A1:C[n], where n is your last data point).
2. Go to Data > Sort and sort by the stem column (Column B) first, and then by the leaf column (Column C).

Step 5: Creating the Stem-and-Leaf Plot

Now, you’ll manually create the stem-and-leaf plot layout:

1. In a new area of your sheet (e.g., columns E and F), list the unique stems from your data in column E. You can do this by copying the stem column (B), pasting it into a new area, and then removing duplicates (select the range, go to Data > Data Tools > Remove Duplicates).
2. Next to each stem, you’ll list the corresponding leaves. You can do this by filtering your data for each stem value and then typing out or copying the leaves next to their respective stems.

Alternatively, for a more automated approach to listing leaves next to their stems without manually filtering and typing:

1. Use the TEXTJOIN function (available in Excel 2019 and later versions) combined with IF to concatenate leaves for each unique stem. For example, if your stems are in column E and your data is in columns B and C, you could use a formula like this in column F (assuming the first stem is in E2):

   =TEXTJOIN("", TRUE, IF(B:B=E2, TEXT(C:C,"#"), ""))

This formula assumes your stem and leaf data are in columns B and C, respectively. Note that this approach might not be feasible for very large datasets due to Excel’s formula character limits.

1. Copy this formula down for each stem.

Step 6: Final Touches

Your stem-and-leaf plot is now set up. You might want to format it to look more like a traditional stem-and-leaf plot by aligning the stems in one column and the leaves in the next, separated by a vertical line or space for readability.

Example

Suppose you have the following numbers: 12, 13, 15, 23, 24, 26, 31, 32.

After applying the steps above, your stem-and-leaf columns might look like this:

And your final stem-and-leaf plot in a readable format might appear as:

1 | 2 3 5
2 | 3 4 6
3 | 1 2

This approach allows you to visually analyze the distribution of your data, which can be particularly useful for understanding the shape of the data distribution, identifying outliers, and comparing different datasets.