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

This tutorial will demonstrate how to create a stem-and-leaf plot in all versions of Excel: 2007, 2010, 2013, 2016, and 2019.

A stem-and-leaf display (also known as a stemplot) is a diagram designed to allow you to quickly assess the distribution of a given dataset. Basically, the plot splits two-digit numbers in half:

• Stems – The first digit
• Leaves – The second digit

As an example, look at the chart below. The chart displays the age breakdown of a small population. The stem (black, y-axis) shows the first digit of the age, while the red data points show the second digit.

You can quickly see that there are six people in their twenties, which is the second most populous age group.

However, the chart is not supported in Excel, meaning you will have to manually build it from the ground up. Check out our Chart Creator Add-In, a tool that allows you to put together impressive advanced Excel charts in just a few clicks.

In this step-by-step tutorial, you will learn how to create a dynamic stem-and-leaf plot in Excel from scratch.

Getting Started

For illustration purposes, suppose you have 24 data points containing the ages of your customers. To visualize which age groups stand out from the crowd, you set out to build a stemplot:

The technique you are about to learn is viable even if your dataset has hundreds of values in it. But to pull it off, you need to lay the groundwork first.

So, let’s dive right in.

Step #1: Sort the values in ascending order.

To start with, sort your actual data in ascending order.

1. Select any cell within the dataset range (A2:A25).
2. Go to the Data tab.
3. Click the “Sort” button.
4. In each dropdown menu, sort by the following:
   1. For “Column,” select “Customer Age” (Column A).
   2. For “Sort On,” select “Values” / “Cell Values.”
   3. For “Order,” select “Smallest to Largest.”

Step #2: Set up a helper table.

Once the column of data has been sorted, set up a separate helper table for storing all the chart data as follows:

A few words on each element of the table:

• Stem (Column C) – This will contain the first digit of all of the ages.
• Leaf (Column D) – This will contain the second digit of all the ages.
• Leaf Position (Column E) – This helper column will help position the leaves on the chart.

Step #3: Find the Stem values.

First, compute the Stem values (Column C) using the LEFT and VALUE functions. The LEFT function—which returns the specified number of characters from the start of a cell—will help us extract the first digit from each value while the VALUE function formats the formula output as a number (that’s crucial).

Enter this formula into cell C2:

=VALUE(LEFT(A2,1))

Once you have found your first Stem value, drag the fill handle to the bottom of the column to execute the formula for the remaining cells (C3:C25).

Step #4: Find the Leaf values.

Our next step is finding the values for the Leaf column (Column D) by pulling the last digit of every number from the original data column (column A). Fortunately, the RIGHT function can do the dirty work for you.

Type the following function into cell D2:

=RIGHT(A2,1)

Once you have the formula in the cell, drag it across the rest of the cells (D3:D25).

Step #5: Find the Leaf Position values.

As a scatter plot will be used for building the stem-and-leaf display, to make everything fall in its place, you need to assign to each leaf a number signifying its position on the chart with the help of the COUNTIF function.

Input this formula into cell E2:

=COUNTIF($C$2:C2,C2)

In plain English, the formula compares every single value in column Stem (Column C) with each other to spot and mark duplicate occurrences, effectively attributing unique identifiers to the leaves that share a common stem.

Again, copy the formula into the rest of the cells (E3:E25).

Step #6: Build a scatter XY plot.

You have now gathered all the puzzle pieces needed to create a scatter plot. Let’s put them together.

1. Highlight all the values in columns Stem and Leaf Position by selecting the data cells from Column C then holding down the Control key as you select the data cells from Column E, leaving out the header row cells (C2:C25 and E2:E25). NOTE: You are not selecting Column D at this time.
2. Go to the Insert tab.
3. Click the “Insert Scatter (X, Y) or Bubble Chart” icon.
4. Choose “Scatter.”

Step #7: Change the X and Y values.

Now, position the horizontal axis responsible for displaying the stems vertically. Right-click the chart plot and pick “Select Data” from the menu that appears.

Next, click the “Edit” button.

Once there, you need to manually change the X and Y values:

1. For “Series X values,” select all the values from column Leaf Position (E2:E25).
2. For “Series Y values,” highlight all the values from column Stem (C2:C25).
3. Click “OK” for Edit Series dialog box.
4. Click “OK” for Select Data Source dialog box.

Step #8: Modify the vertical axis.

After you have rearranged the chart, you need to flip it again to sort the stems in ascending order. Right-click on the vertical axis and choose “Format Axis.”

Once there, follow these simple steps:

1. Navigate to the Axis Options tab.
2. Change the Maximum Bounds value to “6” because the biggest number in the dataset is 60.
3. Set the Major Units value to “1.”
4. Check the “Values in reverse order” box.

Step #9: Add and modify the axis tick marks.

The tick marks placed along the vertical axis will be used as a separator between the stems and the leaves. Without closing the “Format Axis” task pane, scroll down to the Tick Marks section and next to “Major type,” choose “Inside.”

Make the tick marks stand out by changing their color and width.

1. Navigate to the Fill & Line tab.
2. Under Line, click the “Fill Color” icon to open the color palette and choose black.
3. Set the Width to “3 pt.”

You can now remove the horizontal axis and gridlines. Just right-click on each element and choose “Delete.” Also, increase the font of the stem numbers and make them bold so they are easier to see (select the vertical axis and navigate to Home > Font).

Step #10: Add data labels.

As you inch toward the finish line, let’s add the leaves to the chart. To do that, right-click on any dot representing Series “Series 1” and choose “Add Data Labels.”

Step #11: Customize data labels.

Once there, get rid of the default labels and add the values from column Leaf (Column D) instead. Right-click on any data label and select “Format Data Labels.”

When the task pane appears, follow a few simple steps:

1. Switch to the Label Options tab.
2. Check the “Value From Cells” box.
3. Highlight all the values in column Leaf (D2:D25).
4. Click “OK.”
5. Uncheck the “Y value” box.
6. Uncheck the “Show Leader Lines” box.

Change the color and font size of the leaves to differentiate them from the stems—and don’t forget to make them bold as well (Home > Font).

Step #12: Hide the data markers.

The data markers (the dots) have served you well, but you no longer need them, except as a means to position the data labels. So let’s make them transparent.

Right-click on any dot illustrating Series “Series 1” and select “Format Data Series.”

Once the task pane pops up, do the following:

1. Click the “Fill & Line” icon.
2. Switch to the Marker tab.
3. Under “Marker Options,” choose “None.”

And don’t forget to change the chart title.

Step #13: Add the axis titles.

Use the axis titles to label both elements of the chart.

1. Select the chart plot.
2. Go to the Design tab.
3. Click “Add Chart Element.”
4. Select “Axis Titles.”
5. Choose “Primary Horizontal” and “Primary Vertical.”

As you may see, the axis titles overlap the chart plot. To fix the issue, select the chart plot and adjust the handles to resize the plot area. You can now change the axis titles.

Step #14: Add a textbox.

Finally, add a textbox with a key to make it easier to read the stemplot.

1. Select the chart plot.
2. Go to the Insert tab.
3. In the Text group, choose “Text Box.”

In the textbox, give an example from your data explaining how the graph works, and you’re all set!