how to make a stem and leaf plot in excel

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

Stalk-and-Leafage Plot – Free Template Download

Download our gratis Stem-and-Leaf Plot Template for Excel.

Download Now

A stem-and-foliage display (also known equally a stemplot) is a diagram designed to allow y'all to quickly assess the distribution of a given dataset. Basically, the plot splits two-digit numbers in half:

• Stems – The first digit
• Leaves – The 2nd digit

As an example, await at the chart below. The chart displays the age breakdown of a small-scale population. The stalk (black, y-axis) shows the first digit of the age, while the cerise data points bear witness the second digit.

You lot tin can quickly see that there are vi people in their twenties, which is the 2nd most populous historic period grouping.

Nonetheless, the chart is not supported in Excel, significant you 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 advanced Excel charts in just a few clicks.

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

Getting Started

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

The technique you lot are near to learn is feasible even if your dataset has hundreds of values in it. But to pull it off, you need to lay the groundwork first.

Then, let's swoop right in.

Step #i: Sort the values in ascending club.

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

1. Select any cell within the dataset range (A2:A25).
2. Go to the Data tab.
3. Click the "Sort" push.
4. In each dropdown menu, sort past the following:
   1. For "Cavalcade," select "Client Historic period" (Cavalcade A).
   2. For "Sort On," select "Values" / "Cell Values."
   3. For "Order," select "Smallest to Largest."

Step #two: Ready a helper table.

Once the column of data has been sorted, ready a divide helper tabular array for storing all the chart data as follows:

A few words on each element of the table:

• Stem (Column C) – This will contain the commencement digit of all of the ages.
• Leaf (Column D) – This will contain the second digit of all the ages.
• Leaf Position (Cavalcade Due east) – This helper column will help position the leaves on the chart.

Footstep #three: Detect the Stalk values.

Start, 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—will assist us extract the first digit from each value while the VALUE office formats the formula output every bit a number (that's crucial).

Enter this formula into cell C2:

One time yous have found your starting time Stem value, elevate the fill up handle to the bottom of the column to execute the formula for the remaining cells (C3:C25).

Step #4: Find the Leaf values.

Our next step is finding the values for the Leafage column (Column D) by pulling the final digit of every number from the original data column (column A). Fortunately, the Right function can practise the dirty work for y'all.

Type the post-obit function into cell D2:

In one case you have the formula in the cell, elevate information technology across the rest of the cells (D3:D25).

Step #v: Observe the Leafage Position values.

Every bit a scatter plot volition exist used for building the stem-and-foliage display, to make everything fall in its place, you need to assign to each leaf a number signifying its position on the nautical chart with the help of the COUNTIF function.

Input this formula into cell E2:

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

Once more, copy the formula into the balance of the cells (E3:E25).

Step #half dozen: Build a besprinkle XY plot.

You 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 past selecting the data cells from Column C and then holding downwards the Control fundamental as yous select the information cells from Column East, leaving out the header row cells (C2:C25 and E2:E25). Note: You are not selecting Column D at this time.
2. Get to the Insert tab.
3. Click the "Insert Scatter (Ten, Y) or Bubble Chart" icon.
4. Choose "Scatter."

Step #7: Change the X and Y values.

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

Next, click the "Edit" button.

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

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

Pace #8: Modify the vertical axis.

After y'all have rearranged the chart, you need to flip it again to sort the stems in ascending order. Right-click on the vertical axis and cull "Format Axis."

Once there, follow these unproblematic steps:

1. Navigate to the Centrality Options tab.
2. Change the Maximum Premises value to "half dozen" because the biggest number in the dataset is 60.
3. Ready the Major Units value to "1."
4. Check the "Values in opposite order" box.

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

The tick marks placed along the vertical axis will be used as a separator between the stems and the leaves. Without closing the "Format Centrality" chore pane, whorl downwardly to the Tick Marks section and side by side to "Major type," choose "Within."

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

1. Navigate to the Fill & Line tab.
2. Nether Line, click the "Make full Color" icon to open the color palette and choose black.
3. Prepare the Width to "3 pt."

You can at present remove the horizontal axis and gridlines. Simply right-click on each element and choose "Delete." As well, increase the font of the stalk numbers and brand them assuming so they are easier to encounter (select the vertical axis and navigate to Home > Font).

Footstep #10: Add data labels.

As you inch toward the end line, let'southward add the leaves to the nautical chart. To do that, right-click on whatsoever dot representing Series "Series 1" and cull "Add Information Labels."

Step #11: Customize data labels.

In one case in that location, get rid of the default labels and add together the values from cavalcade Leafage (Column D) instead. Right-click on any data characterization and select "Format Data Labels."

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

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

Alter the color and font size of the leaves to differentiate them from the stems—and don't forget to make them bold as well (Home > Font).

Step #12: Hibernate the data markers.

The data markers (the dots) have served yous well, merely y'all no longer demand them, except every bit a means to position the information labels. So let'southward make them transparent.

Right-click on whatsoever dot illustrating Serial "Serial 1" and select "Format Data Series."

One time the task pane pops up, do the following:

1. Click the "Fill up & Line" icon.
2. Switch to the Marker tab.
3. Under "Marking Options," choose "None."

And don't forget to modify the chart title.

Step #xiii: Add together the axis titles.

Use the centrality titles to label both elements of the nautical chart.

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

As you may meet, the axis titles overlap the chart plot. To fix the result, select the chart plot and arrange the handles to resize the plot surface area. Y'all can now modify the centrality titles.

Step #fourteen: Add together a textbox.

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

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

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

Download Stemplot Template

Download our complimentary Stemplot Template for Excel.

Download Now

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

Posted by: klattcaterneved45.blogspot.com

Share this post