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

In this tutorial, you will learn how to create a Stem-and-Leaf plot in Excel.

In order to help visualize the shape of a distribution, a stem-and-leaf display or stem-and-leaf plot is a tool for presenting quantitative data in a graphical manner, comparable to a histogram.

Once you are ready, we can get started by using real-life scenarios to help you understand how to create a Stem-and-Leaf plot in Excel.

Create a Stem-and-Leaf plot

Before we begin we will need a group of data to create a Stem-and-Leaf plot in Excel.

Step 1

Make sure your group of data is displayed in a clean and tidy manner in the first sheet.

Step 2

To create a Stem-and Leaf plot, we will first select the entire data group, then select ‘Data’, then select ‘Sort’.

Step 3

We will then sort the exam results from smallest to largest by inputting these criterias.

Step 4

Now we set up a helper table. The first digit of each age will be in the stem (Column C).

The second digit of each age will be in the leaf (Column D).

Leaf Position (Column E): The leaves on the chart will be placed in this auxiliary column.

Step 5

First, use the LEFT and VALUE functions to compute the Stem values (Column C). We may extract the first digit from each value using the LEFT function, which returns the specified number of characters from the beginning of a cell, while the VALUE function presents the formula output as a number (that’s important). These are the formula to input =VALUE(LEFT(A2,1)) and =RIGHT(A2,1).

Step 6

While a scatter plot will be used to construct the stem-and-leaf display, you must use the COUNTIF function to assign each leaf a number that represents its location on the chart in order to make everything fall into place. The formula, which assigns unique identities to the leaves that share a similar stem, compares each value in column Stem (Column C) with each other to identify and highlight duplicate occurrences. We will use this formula =COUNTIF($C$2:C2,C2).

Step 7

As of right now, you have amassed all the necessary jigsaw pieces to construct a scatter plot. Put them together now. Choose the data cells from Column C, hold down the Control key as you select the cells from Column E, then select “Insert,” click the “Insert Scatter (X, Y) or Bubble Chart” button, and then select “Scatter” to highlight all the values in Columns Stem and Leaf Position.

Step 8

We will then swap the x and y values. To do that we will select ‘Chart Design’, select ‘Select Data’. Then a pop up box will appear, we will select the right x and y values.

Step 9

We then format the x-axis and y-axis to the following formats.

Step 10

To include the like, we will select ‘Solid line’, change the colour of the line to black then change the width to 3.

Step 11

To add the data label, we will select the data points, right-click and select ‘Add Data Labels’. Then select the Leaf column to be used as a data label.

Step 12

To remove the data points, we will right-click and select ‘Format Data Series’ and select ‘None’.

Step 13

Once we are done, the Stem-and Leaf plot is ready.