How to Make a Stem and Leaf Plot in Excel: A Step-by-Step Guide for Beginners

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.

Step-by-Step 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:

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 2-digit numbers, the stem is the 10s place and the leaves are the 1s place.
• For 3-digit numbers, the stem could be the 100s place and leaves the 10s and 1s place.
• For 4-digit 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 x-axis and leaves on the y-axis.

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 y-axis 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 side-by-side scatter charts with different marker colors and connect them with a title. This is an advanced technique.

How do I create back-to-back 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