Make a Stem-and-Leaf Plot in Excel – Step by Step

In this guide, we are going to explain how to easily build a stem and leaf plot in Excel step by step.

Quick caveat before we get started – there is no official method for building a Stem and Leaf Plot using Excel. Our guide runs through a manual workaround with easy to follow steps to get the required result.

Before we get to the steps to be undertaken, let’s quickly talk about what this type of visualization is.

What is a Stem and Leaf Plot?

A Stem and Leaf Plot is a table or chart that clearly visualises the distribution of numbers within a specific range.

The numbers are sorted in ascending order, and usually broken into smaller chunks per row – for example 10s or 100s.

Stem and Leaf plots are very handy as they allow us to see the distributions across categories without a whole lot of mathematical caluclations.

These plots then allow for quick and easy analysis such as calculating the mode or median of a dataset, or easily scanning for outliers that may be present in cases where numbers are clearly sitting far beyond the typical distribution.

Can we build these charts in Excel?

Excel is a very powerful tool when it comes to visualising sets of raw data. It can make bar charts, line charts, scatterplots, sunbursts, combinations of these together – but there is no built in function to create a Stem and Leaf Plot in Excel.

While this clearly isn’t ideal, luckily there are some relatively simple ways to do so.

In the interest of keeping things as simple as possible, for the purpose of this post the process will be relatively manual, so probably best to stick to datasets totalling under 100 or so figures all up.

There are also some more advanced methods we could look to use involving formulas. A clever combination of IF statements, COUNT statements, and dragging these across the spreadsheet as needed could definitely produce a similar result, however depending on the overall size of the dataset, and level of granularity we are looking to work with in distributing the figures this can get to be quite complex.

The more advanced method will be covered in a separate post for anyone who is interested in giving it a shot, but for now let’s stick with the simple and manual approach.

How to create a Stem and Leaf Plot using Excel

For this tutorial, we are going to look at a simple dataset comparing the average test scores across two different schools.

Our dataset looks like the below:

While this dataset may be quite small at only 20 figures per school, it is still a bit hard to make any quick assumptions on the distribution of scores.

First, we will need to arrange both columns in ascending order.

Simply highlight all figures for school one (cells B3 – B22), and under the Data heading select the Sort A-Z button as highlighted below.

When prompted to expand the current selection, choose Continue with the current selection.

Repeat the same process for the second set of data if you are working with more than one column like we are here. Make sure to do these individually rather than selecting all columns at once as it will not sort them properly.

From here is where things start to get a bit more manual.

We are going to work in ranges of 10 to keep things simple, so based on the newly sorted data we know that we are working with numbers in the 10s (due to the score of 11 with school 1), and 100 (based on the perfect score of 100 also from school 1).

Find some empty space and enter the values from 1 to 10 down a page, labeling this column as the Stem, and add two Leaf headings on either side of this, as below:

Now, simply make your way down the list of original values and mark the values one by one to fill in the leaf. Working from the closest column to the stem and making your way outwards cell by cell until all relevant values from that row are complete, and then make your way down to the next, and repeat.

Make sure to only fill in a single number rather than the entire value – for example if there is a 23 in one dataset, only enter a single 3 in the cell on the row covering off the 20s.

Based on our dataset, the final filled out values will look like the below:

For the most part this does the job, and we can definitely see that school 2 generally has higher scores, but let’s work on the formatting a bit to make it easier to read.

All we really need to do is narrow the columns a fair bit, make the alignment of text centered, and add some borders to the cells between the stem and two leafs.

Our final Stem and Leaf Plot now looks like the below:

There we have it! As we can see, while the process is a bit more manual than other charts, due to the nature of these plots being purely numbers organised it is very easy to make our own custom Stem and Leaf Plot in Excel by following these steps.

As highlighted earlier, this tends to be best when working with smaller datasets. Using just 40 numbers here the process was quite simple, but unless you really love data entry it might not be ideal working with sets in the thousands! Given the manual nature of this too human error does start coming into play as well with the larger volumes as the more numbers we need to type in one by one the higher the chance of entering an incorrect figure.

Now that the data is all organised it is easy to perform our analysis on, or even take this data and convert it into other charts such as histograms (although this is something Excel can do itself through from the 2016 version and onwards).

This sums up our guide on how to create a Stem and Leaf Plot using Excel.

For more tips and tutorials on using Excels built in functions be sure to check out our Excel Tips page