Charts in Excel are no doubt a helpful tool to present data in a way that others can easily understand. To present data visually in a better mode, charts and graphs play an important role. A stem-and-leaf plot is one of the type of charts that shows quantitative data.
Today, you will get to learn how to make a stem and leaf plot in Excel as it can surely improve the productivity of your work. Stem and Leaf plot is basically, a histogram tabulation of your data. This kind of chart simply helps you in presenting data in a highly certified manner. Let’s have a deeper overview:
Stem and Leaf
The stem as you know holds the tree and splits into branches once it grows. Branches have leaves and the concept of stem and leaf plots is mainly similar to it. Suppose your data has numbers, 12, 12, 13, 53. You can visualize these numbers as:
1 ->2 2 3 and 5->
Here 1 5 shows the stem and 2 2 3 3 are leaves.
How to Make a Stem and Leaf Plot in Excel?
To make a stem and leaf plot, the first thing you need to do is find out the stem and leaves from the given data for further processing.
Here are the simple steps to follow:
First Things First
In the first step, you have to organize the values from smallest to largest. Suppose your data has different ages of people and you need to make a stem and leaf plot of these age values.
To organize the values from smallest to largest, select the values and open the Data tab.
Click on the Sort & Filter option and then choose Smallest to the Largest icon.
Extract the Stems and Leaves
Now, you have to find the stems and leaves from the data and for this, you can have two formulas.
The first formula is to find the stems:
=LEFT(A2,1)
The second formula is for the leaves:
=RIGHT(A2,1)
Both of the above formulas can take out the values you have specified either from the right or the left.
Determine the Position of Leaves
Here is the formula to find the position of the leaves:
=COUNTIF($B$2:B2,B2)
This formula can find the duplicate values as well and then it makes the right position of the leaves.
Time to Make a Stem and Leaf Plot
The basic procedure has been done so far, now is the time to make the stem and leaf plot.
For this, select the stem column. Press and hold the CTRL key and select the leaf positions column. Remember that you only have to select the values not the labels of columns.
Open the Insert tab and click on the Recommended Charts option.
Choose an X-Y Scatter.
Right-click on the graph and open the Select Data menu.
A dialog box will appear from which you have to choose Edit.
In the Series X values, you have to add the stem values by selecting the stem column.
In the Series Y values, you have to add the leaf positions by selecting the leaf position column.
Format the Stem and Leaf Plot
You are almost there. You just need to make some changes to it for further perfection.
Double-click on the horizontal axis and set the bounds to 0 and 5.
Double-click on the vertical axis now and set the bounds to 0 and 6.
It will activate the Values in reverse mode.
Input the data labels now. For this, choose the dots or markers given in the graph and right-click to select Add Data Labels.
Edit the data label now. For this, choose the data labels in the graph and right-click to select the Format Data Labels.
Select the Leaf column in the dialog box once your values from the cell are activated. Now, uncheck the Y value.
Click on the markers and open the marker option to choose the No Fill and No Line option.
You will see the marker gets hidden.
Remove the horizontal axis now and all other gridlines so that your plot does not have confusion.
In the end, choose the vertical axis and change the color to black. Now, you must set the width to 3 points.
That’s it!
Your stem and leaf plot is all set to display your data in a highly presentable manner.
Summary:
To present your data in a stem and leaf plot, you can simply follow the above-given steps. The stem and leaf plot approach is dynamic that’s why Excel will update it automatically once you add values.
