# How to Make a Stem & Leaf Plot in Excel 2007

Written by stephanie ellen
• Share
• Tweet
• Share
• Email

A stem and leaf plot (also called a stemplot) is a type of diagram used to show statistical data. It’s a way of retaining the individual data points in a diagram that often disappear with other graphical methods such as pie charts and histograms. For example, if you plot the numbers 10,11,12,13, and 14 in a pie chart, the numbers are placed into a category and you cannot see the individual numbers on the chart. With a stem and leaf, you get to see all of the original numbers.

Skill level:
Easy

## Instructions

1. 1

Rename a blank Excel worksheet “Data” by clicking the bottom tab for the worksheet and typing the new name.

2. 2

Rename another blank Excel worksheet in the workbook “Stem” by clicking the bottom tab for that worksheet and typing the new name.

3. 3

Enter your list of numbers in column A of the “Data” worksheet.

4. 4

Press “Alt” and “F11” at the same time to open the visual basic editor.

5. 5

Double click “This Workbook” under Microsoft Excel Objects in the left navigation pane to open a blank code window.

6. 6

Paste the following VBA code into the blank window: Sub StemAndLeaf() data Column = 1

'Clean everything out of the Stem worksheet. Worksheets("Stem").Cells.Clear

'Look at the Data worksheet. Worksheets("Data").Activate

'Find the maximum value. row Pointer = 2 Do Until Cells(row Pointer, 1).Value = "" row Pointer = row Pointer + 1 Loop Maximum = Cells(row Pointer - 1, data Column).Value

'Set the divisor to strip off leaves. divisor = 1 Do Until Maximum / divisor <= 10 divisor = divisor * 10 Loop

'If the first digit of the largest value is less than 5, then 'use a smaller divisor. 'Otherwise you could end up with four or fewer rows in the plot. If Fix(Maximum / divisor) < 5 Then divisor = divisor * 10

'Calculate the top stem’s value. top Stem = Fix(Maximum / divisor)

'Set up the Stem worksheet. Worksheets("Stem").Activate Cells(1, 1).Value = "Count" Cells(1, 2).Value = "Stem" Cells(1, 3).Value = "Leaves" For row Pointer = 2 To top Stem + 2 Cells(row Pointer, 2).Value = row Pointer - 2 Cells(row Pointer, 3).Value = "|" Next row Pointer

'Calculate the counts. 'The following code is slower than it needs to be, 'but a faster code would be harder to read and understand. Worksheets("Data").Activate row Pointer = 2 Do Until Cells(row Pointer, data Column).Value = "" measurement = Cells(row Pointer, data Column).Value Stem = Fix(measurement / divisor) Worksheets("Stem").Cells(Stem + 2, 1).Value = Worksheets("Stem").Cells(Stem + 2, 1).Value + 1 row Pointer = row Pointer + 1 Loop

'Calculate the shrink factor. Worksheets("Stem").Activate maximum Count = 0 For row Pointer = 2 To top Stem + 2 If Cells(row Pointer, 1).Value > maximum Count Then maximum Count = Cells(row Pointer, 1).Value End If Next row Pointer

shrink Factor = Fix(maximum Count / 50) If shrink Factor < 1 Then shrink Factor = 1 Cells(1, 4).Value = "Each digit represents" + Str(shrinkFactor) + " cases."

'Return to the data, and fill the leaves in light of the values in the data. Worksheets("Data").Activate row Pointer = 2 Do Until Cells(row Pointer, data Column).Value = "" measurement = Cells(row Pointer, data Column).Value Stem = Fix(measurement / divisor) leaf = measurement - Stem * divisor leaf = Fix(leaf * 10 / divisor)

Worksheets("Stem").Cells(Stem + 2, 3).Value = Worksheets("Stem").Cells(Stem + 2, 3).Value + Trim(Str(leaf)) row Pointer = row Pointer + shrink Factor Loop

'Get to the Stem worksheet. Worksheets("Stem").Activate End Sub

7. 7

Press “F5” to run the code. Your Stem and Leaf plot will appear in the “Stem” worksheet.

#### Tips and warnings

• If you change your data and need to generate a new stem and leaf plot, simply run the StemAndLeaf macro again.

### Don't Miss

#### References

• All types
• Articles
• Slideshows
• Videos
##### Sort:
• Most relevant
• Most popular
• Most recent