How To Set Up A Stem And Leaf Plot

This tutorial will demonstrate how to create a stalk-and-leaf plot in all versions of Excel: 2007, 2010, 2022, 2022, and 2022.

Stalk-and-Leaf Plot – Free Template Download

Download our free Stalk-and-Leaf Plot Template for Excel.

Download At present

A stem-and-leaf display (likewise known every bit a stemplot) is a diagram designed to allow y'all to quickly appraise the distribution of a given dataset. Basically, the plot splits two-digit numbers in half:

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

As an instance, await at the chart below. The chart displays the historic period breakdown of a small population. The stem (black, y-axis) shows the first digit of the age, while the scarlet data points evidence the second digit.

You tin can speedily meet that there are six people in their twenties, which is the second most populous age group.

Nevertheless, the chart is not supported in Excel, pregnant yous will have to manually build it from the ground up. Check out our Nautical chart Creator Add together-In, a tool that allows you to put together impressive avant-garde Excel charts in just a few clicks.

In this stride-by-pace tutorial, you lot will acquire how to create a dynamic stem-and-foliage plot in Excel from scratch.

Getting Started

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

The technique you are about to learn is feasible even if your dataset has hundreds of values in it. But to pull it off, you demand to lay the groundwork offset.

And so, allow'southward dive right in.

Stride #1: Sort the values in ascending gild.

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

1. Select any jail cell within the dataset range (A2:A25).
2. Become to the Data tab.
3. Click the "Sort" button.
4. In each dropdown menu, sort by the post-obit:
   1. For "Column," select "Client Age" (Column A).
   2. For "Sort On," select "Values" / "Jail cell Values."
   3. For "Society," select "Smallest to Largest."

Step #2: Prepare upwardly a helper table.

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

A few words on each element of the table:

• Stem (Column C) – This will contain the first digit of all of the ages.
• Leafage (Column D) – This will contain the 2d digit of all the ages.
• Leaf Position (Cavalcade E) – This helper column will help position the leaves on the nautical chart.

Footstep #3: Find the Stem 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 starting time of a cell—volition assistance us excerpt the beginning digit from each value while the VALUE function formats the formula output as a number (that's crucial).

Enter this formula into prison cell C2:

=VALUE(LEFT(A2,one))

In one case you have institute your first Stem value, elevate the fill handle to the bottom of the column to execute the formula for the remaining cells (C3:C25).

Step #four: Find the Foliage values.

Our next stride is finding the values for the Leaf column (Column D) by pulling the terminal digit of every number from the original information column (column A). Fortunately, the RIGHT function tin can do the dirty work for yous.

Type the following function into prison cell D2:

=RIGHT(A2,1)

Once you have the formula in the cell, drag it across the rest of the cells (D3:D25).

Stride #5: Find the Leaf Position values.

As a scatter plot will exist used for building the stem-and-leaf display, to make everything fall in its place, you need to assign to each leaf a number signifying its position on the chart with the assistance of the COUNTIF function.

Input this formula into jail cell E2:

=COUNTIF($C$ii:C2,C2)

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

Again, copy the formula into the rest of the cells (E3:E25).

Footstep #half dozen: Build a scatter XY plot.

You have at present 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 Cavalcade C and then holding downwardly the Command cardinal as you select the information cells from Column E, leaving out the header row cells (C2:C25 and E2:E25). NOTE: Y'all are not selecting Column D at this time.
2. Go to the Insert tab.
3. Click the "Insert Besprinkle (X, Y) or Bubble Nautical chart" icon.
4. Cull "Scatter."

Step #vii: Change the X and Y values.

Now, position the horizontal centrality responsible for displaying the stems vertically. Correct-click the nautical chart plot and pick "Select Information" from the bill of fare that appears.

Next, click the "Edit" button.

One time at that place, you need to manually change the X and Y values:

1. For "Series Ten values," select all the values from column Leaf 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 Data Source dialog box.

Step #8: Modify the vertical centrality.

Later you have 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 Axis."

Once there, follow these simple steps:

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

Footstep #nine: Add and modify the axis tick marks.

The tick marks placed along the vertical axis will be used as a separator betwixt the stems and the leaves. Without closing the "Format Centrality" task pane, coil down to the Tick Marks department and side by side to "Major blazon," choose "Inside."

Make the tick marks stand up out by changing their colour and width.

1. Navigate to the Fill & Line tab.
2. Under Line, click the "Fill Color" icon to open the colour palette and choose blackness.
3. Set the Width to "3 pt."

Y'all can at present remove the horizontal axis and gridlines. Just correct-click on each chemical element and choose "Delete." As well, increase the font of the stem numbers and brand them assuming then they are easier to come across (select the vertical axis and navigate to Home > Font).

Footstep #10: Add information labels.

Equally you inch toward the terminate line, allow'south add the leaves to the nautical chart. To do that, right-click on any dot representing Series "Series ane" and choose "Add Data Labels."

Stride #11: Customize data labels.

Once in that location, get rid of the default labels and add the values from column Leafage (Cavalcade D) instead. Correct-click on any data label and select "Format Data Labels."

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

1. Switch to the Label Options tab.
2. Bank check the "Value From Cells" box.
3. Highlight all the values in column Leafage (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 as well (Habitation > Font).

Step #12: Hide the data markers.

The information markers (the dots) have served you well, but y'all no longer need them, except equally a means to position the data labels. And so permit's make them transparent.

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

Once the task pane pops upwardly, do the following:

1. Click the "Fill & Line" icon.
2. Switch to the Mark tab.
3. Under "Marker Options," cull "None."

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

Step #13: Add the axis titles.

Utilize the axis titles to label both elements of the chart.

1. Select the chart plot.
2. Go to the Design tab.
3. Click "Add together Chart Element."
4. Select "Centrality Titles."
5. Choose "Principal Horizontal" and "Primary Vertical."

As you may run into, the axis titles overlap the chart plot. To fix the issue, select the chart plot and conform the handles to resize the plot area. You can at present change the centrality titles.

Step #14: Add a textbox.

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

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

In the textbox, requite an example from your data explaining how the graph works, and you're all set!

Download Stemplot Template

Download our gratis Stemplot Template for Excel.

Download Now

How To Set Up A Stem And Leaf Plot,

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

Posted by: wallsfeweed.blogspot.com

Share this post