Stem and Leaf Plot How to Read

This tutorial volition demonstrate how to create a stem-and-foliage plot in all versions of Excel: 2007, 2010, 2013, 2016, and 2019.

Stem-and-Leaf Plot – Free Template Download

Download our free Stem-and-Leafage Plot Template for Excel.

Download At present

A stalk-and-foliage brandish (too known as a stemplot) is a diagram designed to permit you to quickly assess the distribution of a given dataset. Basically, the plot splits 2-digit numbers in half:

• Stems – The get-go digit
• Leaves – The second digit

As an example, look at the chart beneath. The chart displays the age breakdown of a small population. The stem (black, y-axis) shows the start digit of the age, while the cerise data points show the 2d digit.

You lot can quickly see that there are six people in their twenties, which is the second about populous age group.

However, the chart is non supported in Excel, pregnant yous will accept to manually build information technology from the ground up. Bank check out our Nautical chart Creator Add-In, a tool that allows you to put together impressive advanced Excel charts in only a few clicks.

In this stride-by-step tutorial, y'all volition learn how to create a dynamic stem-and-leaf plot in Excel from scratch.

Getting Started

For illustration purposes, suppose you have 24 data points containing the ages of your customers. To visualize which historic period groups stand up out from the crowd, yous set out to build a stemplot:

The technique you are well-nigh to learn is feasible fifty-fifty if your dataset has hundreds of values in it. But to pull it off, you need to lay the groundwork offset.

And then, let's swoop right in.

Step #ane: Sort the values in ascending club.

To start with, sort your actual data in ascending club.

1. Select any cell within the dataset range (A2:A25).
2. Become to the Data tab.
3. Click the "Sort" push button.
4. In each dropdown menu, sort by the following:
   1. For "Column," select "Customer Age" (Column A).
   2. For "Sort On," select "Values" / "Prison cell Values."
   3. For "Order," select "Smallest to Largest."

Step #ii: Gear up a helper table.

One time the column of data has been sorted, set up a separate helper table for storing all the chart data as follows:

A few words on each chemical element of the table:

• Stem (Column C) – This will incorporate the beginning digit of all of the ages.
• Leaf (Column D) – This will contain the 2nd digit of all the ages.
• Leaf Position (Cavalcade East) – This helper cavalcade volition assist position the leaves on the chart.

Step #3: Find the Stalk values.

First, compute the Stem values (Column C) using the LEFT and VALUE functions. The LEFT function—which returns the specified number of characters from the start of a jail cell—will aid us extract the first digit from each value while the VALUE function formats the formula output as a number (that'southward crucial).

Enter this formula into cell C2:

Once y'all accept found your kickoff Stem value, drag the fill up handle to the lesser of the column to execute the formula for the remaining cells (C3:C25).

Step #4: Find the Leafage values.

Our next stride is finding the values for the Leafage column (Column D) by pulling the final digit of every number from the original data cavalcade (column A). Fortunately, the Correct function can do the muddied piece of work for you.

Type the following part into cell D2:

Once you lot have the formula in the cell, elevate it across the residual of the cells (D3:D25).

Step #5: Find the Leaf Position values.

As a scatter plot will be used for building the stem-and-leaf display, to brand everything fall in its identify, you lot need to assign to each leaf a number signifying its position on the chart with the assist of the COUNTIF part.

Input this formula into cell E2:

In manifestly English language, the formula compares every unmarried value in column Stem (Cavalcade C) with each other to spot and mark indistinguishable occurrences, effectively attributing unique identifiers to the leaves that share a common stem.

Again, re-create the formula into the rest of the cells (E3:E25).

Step #6: Build a scatter XY plot.

Y'all have now gathered all the puzzle pieces needed to create a scatter plot. Let's put them together.

1. Highlight all the values in columns Stem and Leaf Position by selecting the data cells from Column C then holding down the Control key as you select the data cells from Column E, leaving out the header row cells (C2:C25 and E2:E25). NOTE: You are not selecting Column D at this time.
2. Go to the Insert tab.
3. Click the "Insert Scatter (X, Y) or Bubble Chart" icon.
4. Choose "Scatter."

Step #7: Change the 10 and Y values.

Now, position the horizontal axis responsible for displaying the stems vertically. Right-click the chart plot and pick "Select Data" from the menu that appears.

Next, click the "Edit" button.

Once there, you demand to manually change the X and Y values:

1. For "Series X values," select all the values from column Leafage Position (E2:E25).
2. For "Series Y values," highlight all the values from column Stem (C2:C25).
3. Click "OK" for Edit Series dialog box.
4. Click "OK" for Select Information Source dialog box.

Pace #eight: Alter the vertical centrality.

After you accept rearranged the chart, you need to flip it again to sort the stems in ascending order. Right-click on the vertical axis and choose "Format Centrality."

In one case there, follow these simple steps:

1. Navigate to the Axis Options tab.
2. Modify the Maximum Bounds value to "six" because the biggest number in the dataset is 60.
3. Set the Major Units value to "1."
4. Cheque the "Values in reverse society" box.

Footstep #9: Add together and alter the centrality tick marks.

The tick marks placed along the vertical centrality volition be used equally a separator betwixt the stems and the leaves. Without closing the "Format Axis" chore pane, coil downwards to the Tick Marks section and next to "Major type," cull "Inside."

Make the tick marks stand up out by irresolute their color and width.

1. Navigate to the Make full & Line tab.
2. Under Line, click the "Make full Color" icon to open the color palette and cull black.
3. Set the Width to "3 pt."

You tin now remove the horizontal axis and gridlines. Merely right-click on each element and choose "Delete." Too, increase the font of the stalk numbers and make them bold so they are easier to see (select the vertical axis and navigate to Home > Font).

Step #ten: Add together information labels.

As you inch toward the finish line, let's add the leaves to the chart. To do that, correct-click on any dot representing Serial "Series 1" and choose "Add Data Labels."

Step #xi: Customize data labels.

In one case there, get rid of the default labels and add together the values from column Leaf (Column D) instead. Right-click on whatsoever data label and select "Format Data Labels."

When the task pane appears, follow a few simple steps:

1. Switch to the Characterization Options tab.
2. Check the "Value From Cells" box.
3. Highlight all the values in column Leaf (D2:D25).
4. Click "OK."
5. Uncheck the "Y value" box.
6. Uncheck the "Show Leader Lines" box.

Change the color and font size of the leaves to differentiate them from the stems—and don't forget to brand them bold too (Abode > Font).

Step #12: Hibernate the data markers.

The data markers (the dots) have served yous well, but you no longer need them, except as a means to position the data labels. So let's make them transparent.

Correct-click on any dot illustrating Series "Series one" and select "Format Data Serial."

Once the task pane pops up, do the post-obit:

1. Click the "Make full & Line" icon.
2. Switch to the Marker tab.
3. Nether "Marker Options," choose "None."

And don't forget to modify the nautical chart title.

Step #13: Add the centrality titles.

Utilise the centrality titles to characterization both elements of the chart.

1. Select the nautical chart plot.
2. Get to the Design tab.
3. Click "Add Chart Chemical element."
4. Select "Centrality Titles."
5. Cull "Primary Horizontal" and "Primary Vertical."

As you may see, the axis titles overlap the chart plot. To fix the issue, select the chart plot and adjust the handles to resize the plot area. Yous tin at present modify the centrality titles.

Step #fourteen: Add together a textbox.

Finally, add together a textbox with a central to make it easier to read the stemplot.

1. Select the chart plot.
2. Become to the Insert tab.
3. In the Text group, choose "Text Box."

In the textbox, give an example from your data explaining how the graph works, and y'all're all ready!

Download Stemplot Template

Download our costless Stemplot Template for Excel.

Download Now

courtneytheigiche42.blogspot.com

Source: https://www.automateexcel.com/charts/stem-and-leaf-template/

Share this post