If youre working with data sets, you may have come across the stem and leaf plot, a statistical tool used to visualize data distribution. While creating a stem and leaf plot can seem daunting, its quite simple if you use the right software. In this article, well show you how to create a stem and leaf plot in Excel using WPS Office.
Stem and leaf plots are a handy way to visualize the distribution of numerical data. With just a few clicks, you can create one in Excel to get a quick overview of your dataset. In this comprehensive guide, I’ll walk you through the entire process of making a stem and leaf plot in Excel, from organizing your raw data to customizing the final chart.
What is a Stem and Leaf Plot?
A stem and leaf plot splits each data value into a “stem” (the first digit or digits) and a “leaf” (usually the last digit). The stems are listed vertically on the left, and each stem has its corresponding leaves listed next to it.
For example, let’s say your dataset contains the numbers:
12 45 32, 21, 56
The stems would be
1 2, 3 4, 5
And the leaves would be:
2, 5, 2, 1, 6
Arranged into a plot, it would look like:
1  2 2  13  24  55  6
Stem and leaf plots allow you to see the shape and distribution of data at a glance. The stems provide an overview of the range, while the leaves show the granular values.
When to Use a Stem and Leaf Plot
Stem and leaf plots are best for small to medium sized datasets. They become impractical for very large samples, where a histogram would be preferable.
Some good cases for using a stem and leaf plot include:
 Visualizing the scores on a test with a small class
 Seeing the distribution of data from an experiment with a limited sample size
 Getting a quick overview of survey responses from a subset of participants
The key is that stem and leaf plots keep all the original values visible, unlike histograms which group data into bins. So they are ideal when you want to see and communicate exact figures, not just general patterns.
StepbyStep Guide to Building a Stem and Leaf Plot in Excel
Follow these steps to make a stem and leaf plot from your own data in Excel:
1. Enter the Data in One Column
Start by entering your dataset into a single column in Excel. The data should be numeric.
For this example, we’ll use test scores:
Test Scores 

92 
85 
78 
65 
91 
84 
77 
63 
86 
79 
2. Sort the Data Smallest to Largest
Next, select the column and use Excel’s Sort feature to arrange the values from smallest to largest. Click the Data tab > Sort & Filter > Sort Smallest to Largest.
This makes it easier to break the data into stems and leaves later.
3. Determine the Stems
Decide how you want to separate your data into stems and leaves.
For our test score data, we’ll use the tens place as the stems (60, 70, 80, 90). This keeps each stem to a single digit.
In general:
 For 2digit numbers, the stem is the 10s place and the leaves are the 1s place.
 For 3digit numbers, the stem could be the 100s place and leaves the 10s and 1s place.
 For 4digit numbers, the stem is often the 1000s place.
Choose stems that produce leaves with just 1 or 2 digits. More than that becomes confusing.
4. Insert a Scatter Chart
Highlight both the stems and leaves columns, then insert a basic scatter chart:
 Click the Insert tab > Select the Scatter Chart type > Choose a simple scatter with only markers
This will plot our stems on the xaxis and leaves on the yaxis.
5. Format the Chart Axes
To make our chart look like a stem and leaf plot, we need to format the axes:
 Right click the vertical axis > Format Axis
 Set the Minimum Value to 0.5 and Maximum Value to 9.5
 Set Major Unit to 1 and Minor Unit to 0.2
 Click the Number section on the left and set Decimal Places to 0
This makes the yaxis show integer steps from 0 to 9.
Do the same process for the horizontal axis, setting it to show integer values matching the stems.
6. Add Stem and Leaf Axis Labels
To complete the stem and leaf plot:
 Right click the horizontal axis and add the label “Stem”
 Right click the vertical axis and add the label “Leaf”
The plot now clearly shows the split between stems and leaves.
Customizing the Appearance
To polish your stem and leaf plot, you can customize the appearance:
 Add a descriptive Chart Title
 Change the chart style under Chart Design to alter colors and shading
 Increase the marker size under Format > Shape Options to make the leaves more visible
Take some time to experiment with different looks until you find one that fits your aesthetic and communicates the data effectively.
When Not to Use a Stem and Leaf Plot
While stem and leaf plots are handy for small to medium sized datasets, they aren’t always the best choice. Here are some cases when you may want to consider other chart types:
 You have a very large dataset with over 200 values. A histogram would handle this better.
 Your data includes decimal places. Box plots and dot plots can handle decimals more gracefully.
 You want to visualize and compare multiple datasets. Box plots and clustered column charts work well for this.
 Your data isn’t numerical. Bar charts, column charts, and pie charts can handle categorical data.
The key is picking the type of data visualization that allows you to see the specifics you need for your analysis goals and audience.
Tips for an Effective Stem and Leaf Plot
Here are some best practices to create stem and leaf plots that effectively communicate your data:

Sort the data from smallest to largest values to make the shape obvious at a glance.

Choose clear stems that organize the data into logical groups. Avoid creating stems with multiple digits if possible.

Limit leaf digits to 1 or 2 for simplicity. More causes clutter.

Use markers on the chart instead of just connecting lines to emphasize each data point.

Customize formatting like colors and titles so the plot is intuitive for your particular audience.

Only include 1 dataset per plot to avoid overcomplicating the visualization.
Alternatives to Building a Stem and Leaf Plot in Excel
While Excel is a great option for making stem and leaf plots, there are some other tools you can use:

Google Sheets – The process is nearly identical to Excel. Just sort, determine stems and leaves, insert a scatter chart, and format.

RStudio – Using ggplot2 and QGIS, you can make polished stem and leaf plots. More coding is required.

Tableau – Tableau Public lets you build free interactive stem and leaf plots with drag and drop simplicity.

Python – With matplotlib, NumPy, and Pandas, Python provides extensive control for customized stem and leaf plots.
So if you use tools beyond Excel, don’t hesitate to build stem and leaf plots in your platform of choice. The fundamental concepts remain the same.
Common Questions about Stem and Leaf Plots in Excel
Here are answers to some frequently asked questions about creating stem and leaf plots in Excel:
How do I handle negative numbers in a stem and leaf plot?
List negative stems and leaves separately below the positives. Or, take the absolute value of the negatives and list them together with the positives marked with an asterisk.
Can I create horizontal stem and leaf plots in Excel?
Yes, just switch the stems to the vertical axis and leaves to the horizontal when setting up the chart.
What if my stems have more than 1 digit?
Try adjusting your stem unit or rounding stems to 1 digit if possible. Multiple digit stems can create a cluttered plot.
Can I make a double stem and leaf plot?
Yes, just create two sidebyside scatter charts with different marker colors and connect them with a title. This is an advanced technique.
How do I create backtoback stem and leaf plots?
Use two columns for stems and leaves, then create two separate scatter charts. Place them next to each other and sync the axes.
Key Takeaways
 Stem and leaf plots split data into stems (first digits) and leaves (last digits) to visualize the distribution.
 They are great for small to medium datasets when you want to see granular values.
 In Excel, make a sorted single column of data, break into stems and leaves, then build a scatter chart and format the axes.
 Customize titles, labels, and formatting to make the plot intuitive.
 Sorting, good stems, limited leaves, and thoughtful formatting are key to effective stem and leaf plots
Step 2: Create a Table
Next, create a table in Excel with two columns. The first column will be for the stem values and the second for the leaf values. Enter the stem values in the first column and leave the second column blank for now.
Step 6: Create the Stem and Leaf Plot
To create a stem and leaf plot in Excel, follow these steps:
 Select the range of cells that contain Stem and Leaf Position.
 Click the “Insert” tab in the ribbon menu.
 In the “Charts” section, click the XY Scatter Diagram.
 Rightclick on the chart and go to “Select Data.”
 Click Edit and include Leaf Position as Series X and Stem as Series Y values.
 Click on the Xaxis and Select Format Axis.
 Change the Maximum value to 3, as 36 is the maximum number, and change the Minor value.
 Tick the “Values in reverse order” check box.
 Here is your Stem and Leaf Plot.