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

Other People Are Reading

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

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

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.