2 Ways to Create a Stem and Leaf Plot in Excel

The Stem and Leaf plots help to note down multi-digited numbers really fast. You may need to create a Stem and Leaf lot for counting products, taking notes of the test scores, noting down lab results, etc. In this article, I will describe how you can create a Stem and Leaf plot in Excel.

What is a Stem and Leaf Plot?

A Stem and Leaf plot is a kind of data plotting system that places a large amount of data in a placement value system that will help the readers acquire the numeric report in a very short time.

The plot contains two parts: the Stem and the Leaf. The main column of the plot, called the Stem, lists all the digits in the tenth place and beyond in order. The other columns on the left side of the stem are called the Leaf.

For Example, If a dataset has two numbers: 23 and 25, the Stem will be 2, and the Leaf will be 3 and 5.

If the plot counts two categories of data, it can also have two leaves on both sides.

Introduction to the Dataset

Let’s say I harvest mangoes on a patch of land. I take note of the numbers after counting how many mangoes are on each tree. Some trees are big and have produced a lot of mangoes, and some trees have produced fewer.

Now I will show you how I create a Stem and Leaf plot from this dataset in Excel using different methods.

Easiest Way to Create a Stem and Leaf Plot in Excel

In the first method, I will be using the FLOOR function and the RIGHT function to create the Stem and Leaf plot.

Usage Guide

Step_1: Select the data range, cell A1:A23.

Step_2: Then go to the Data tab.

Step_3: Select the Sort A to Z button from the Sort & Filter group.

The Sort Warning window will pop up on the worksheet.

Step_4: Select Continue with the current selection box.

Step_5: Hit the Sort button.

This will reorganize the dataset from the smallest to the largest numerical order.

Step_6: Create two new columns named Stem and Leaf in columns B and C, respectively.

Step_7: Type this formula in cell B2:

=FLOOR.MATH(A2/10)

Formula Explanation

• A2 is 5.
• A2/10 is 0.5.
• FLOOR.MATH function rounds down 0.5 and returns the value 0 in cell B2.

Step_8: Take the cursor on the right bottom corner of cell B2 and drag the Fill Handle from cell range B2:B23.

Step_9: Type this formula in cell C2:

=RIGHT(A2,1)

Formula Explanation

• A2 is 5.
• 1 is the mention of the number of characters.
• The RIGHT function returned 1 character from the right side of A2. As inside cell A2, there is only one character, 5, so in cell C2 the RIGHT function returned 5.

Step_10: Drag the Fill Handle icon from cell range C2:C23.

Organize the Result

The columns Stem and Leaf now display the findings. The Stem column, however, includes a repetition of numbers, which is the problem now. For example, the values in cells B2 to B4 are 0. Here, the plot would be satisfied with a single 0.

Tip: I colored those cells that have the same Stem. You can use the Fill Color command to do that.

Final Result >

In columns E and F, I created a new dataset for the final result. As cell B2:B4 has 0, I input a single 0 in cell E2 (Stem). As the 0 in cell B2:B4 has corresponding numbers: 5 (C2), 7 (C3), and 9 (C4), I inserted 5 7 9 in cell F2 as the Leaf.

I repeated the same process for the rest of the cells, and the final result is delivered in the cell range E1:F8.

Alternative Way: Use the REPT Function to Create a Stem and Leaf Plot in Excel

The formula for this method will seem a little bit lengthy at first. Don’t worry, I will break down the formula later in the Formula Explanation part. And if you still find the formula complex, just copy it from the article.

Usage Guide

Step_1: Select the cell range A1:A23.

Step_2: Go to the Data tab.

Step_3: Now, click on the Sort A to Z button from the Sort & Filter group.

Step_4: Create a new column named Stem in an empty cell (C1).

Step_5: Type this formula in cell C2:

=FLOOR.MATH(A2/10)

Step_6: Take the cursor on the right bottom corner of cell C2 and double-click on the Fill Handle icon.

The formula will be copied down from C2 to C23 automatically.

Formula Explanation

• A2 is 5.
• A2/10 is 0.5.
• FLOOR.MATH function rounds down 0.5 and returns the value 0 in cell C2.

Similarly,

• A23 is 347.
• A23/10 is 34.7.
• FLOOR.MATH function rounded down the value 34.7 and returned 34 in cell C23.

Step_7: Create a new column named Leaf in cell D1.

Step_8: Write this formula in cell D2:

=REPT("0",COUNTIF($A$2:$A$23,C2*10+0))&REPT("1",COUNTIF($A$2:$A$23,C2*10+1))&REPT("2",COUNTIF($A$2:$A$23,C2*10+2))&REPT("3",COUNTIF($A$2:$A$23,C2*10+3))&REPT("4",COUNTIF($A$2:$A$23,C2*10+4))&REPT("5",COUNTIF($A$2:$A$23,C2*10+5))&REPT("6",COUNTIF($A$2:$A$23,C2*10+6))&REPT("7",COUNTIF($A$2:$A$23,C2*10+7))&REPT("8",COUNTIF($A$2:$A$23,C2*10+8))&REPT("9",COUNTIF($A$2:$A$23,C2*10+9))

Formula Explanation

• 0 is the number I want to be repeated in the REPT function.
• COUNTIF($A$2:$A$23, C2*10+0) is the condition for 0 to be repeated.
• $A$2:$A$23 is the Absolute Cell Reference of A2:A23.
• C2*10+0 is multiplying C2 with 10 because we divided the numbers in the Stem column in FLOOR.MATH function.

In a similar manner, I used the same formula for 0–9 and added the REPT function with the & symbol.

Step_9: Double-click on the Fill Handle icon in cell D2.

Step_10: Press the CTRL key and select the repeated cells while keeping the CTRL key pressed.

Step_11: Right-click on any of the selected cells and click on Delete.

Step_12: Select Shift cells up from the Delete dialog box.

Step_13: Hit OK.

Final Result >

After trimming the unnecessary cells, the Stem and Leaf plot is displayed in cell range C1:D8.

When Do You Need a Stem and Leaf Plot?

• When the data set has multi-digit numbers.
• When you want to instantly note down long numbers, like test scores, temperatures, or counting products.
• It has better readability than other data representing charts like histograms or bar charts.

Tips for Using the Stem and Leaf Plot in Excel

• Always rearrange the dataset in ascending order before plotting it.
• Keep your dataset small, so data plotting will be easier for you.
• For further statistical analysis, you can find the median and mean of your dataset.

Conclusion

You can apply any of the methods in this article. I hope I delivered the complete concept of creating and using the stem and leaf plot in Microsoft Excel. Share your comments about this blog in the comment section. Also, keep visiting our site to learn more about Excel. Happy Excelling!