Skip to content
Tutorials Center
My learning

Topics

Lesson 56 of 93 ยท Charts and Visualization

Scatter, Bubble, Frequency Polygon, Ogive and Statistical Plots

Create scatter and bubble charts with three variables, frequency polygons, ogives, stem-and-leaf plots, Bland-Altman and forest plots, plus two trendlines and their intersection.

Scatter charts are Excel's workhorse for relationships and distributions. With a few extra columns they turn into bubble charts (three variables at once), frequency polygons and ogives (distribution shapes), Bland-Altman plots (method agreement) and forest plots (study results). This lesson shows the data layout for each and how to read the line fits Excel can draw, including where two trendlines cross.

Excel cannot run on this site, so steps are real Excel but read-only. The TryIts are JavaScript stand-ins that compute the numbers each chart plots, such as class frequencies, cumulative percentages, bias and limits of agreement, and the intersection of two fitted lines. They do not draw charts, and the statistics formulas are the same ones you would write in cells. For the formulas behind the lines see Correlation and Forecasting.

Scatter and bubble charts

A scatter chart plots (x, y) pairs. Both axes are numeric, which is what separates it from a line chart, where the horizontal axis is a list of categories. Select two columns and choose Insert > Scatter. A bubble chart adds a third column that controls the size of each marker, so one chart shows three variables: say advertising spend (x), sales (y) and number of stores (bubble size).

  1. Arrange the data as columns X, Y, Size (you can add a Name column for labels).
  2. Select the three numeric columns and choose Insert > Scatter > Bubble.
  3. To label bubbles with names, add data labels, pick Value From Cells, and select the Name column.
  4. Set Format Data Series > Scale bubble size to (100% is default; reduce it if bubbles overlap) and decide whether size represents area (the default and the honest choice) or width.

If you need three variables but not area, a 3-axis look comes from using color as the third variable: build a scatter with several series (one per category), so each category gets its own color. A true 3-D scatter does not exist in Excel; Surface charts (Area, Surface, Radar, Stock, Sunburst, Treemap and Map Charts) are the closest.

Two trendlines and where they cross

Right-click a series, Add Trendline, tick Display Equation on chart. A scatter with two series and two linear trendlines gives two equations, y = m1 x + b1 and y = m2 x + b2. Their intersection is where m1 x + b1 = m2 x + b2, so:

TEXT
x = (b2 - b1) / (m1 - m2)        y = m1 * x + b1

Rather than copy rounded numbers from the chart, compute slopes and intercepts in cells with SLOPE and INTERCEPT, and then the crossing:

TEXT
Slope1       =SLOPE(C2:C10, B2:B10)
Intercept1   =INTERCEPT(C2:C10, B2:B10)
Slope2       =SLOPE(D2:D10, B2:B10)
Intercept2   =INTERCEPT(D2:D10, B2:B10)
X at cross   =(Intercept2 - Intercept1) / (Slope1 - Slope2)
Y at cross   =Slope1 * X + Intercept1

Break-even analysis is the classic use: cost line against revenue line.

JS Slope, intercept and the crossing point of two lines

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Frequency polygon and ogive

Both start from a frequency table of class intervals (see Frequency Distributions, Cumulative Frequency and Contingency Tables for how to build it with FREQUENCY).

A frequency polygon joins the class midpoints with straight lines. Build columns: Midpoint (=(lower+upper)/2) and Frequency. Add an empty class with frequency 0 before the first class and after the last, so the line comes down to the axis. Chart Midpoint (x) and Frequency (y) as Scatter with Straight Lines and Markers.

An ogive (cumulative frequency curve) plots the cumulative count against the upper class boundary. Cumulative frequency is a running total: =SUM($B$2:B2). Start the curve at the lower boundary of the first class with 0, and use a scatter with lines again. The median is where the curve crosses 50% of the total.

JS Class midpoints and cumulative frequency

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Stem-and-leaf plot

A stem-and-leaf plot keeps the actual values while showing the shape, which Excel's chart gallery does not offer. Build it with formulas: the stem is the tens digit and the leaf is the ones digit. For sorted values in A2:A21:

TEXT
Stem  =INT(A2/10)
Leaf  =MOD(A2,10)
Row   =TEXTJOIN(" ", TRUE, IF(INT($A$2:$A$21/10)=D2, MOD($A$2:$A$21,10), ""))

The last formula, with the stem number in D2, joins all leaves for that stem (array formulas need Ctrl+Shift+Enter before Microsoft 365). Rotating the result 90 degrees looks like a histogram, and both show the same distribution.

JS A stem-and-leaf plot from raw values

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Bland-Altman plot

A Bland-Altman plot checks whether two measurement methods agree. Each subject appears once. For each pair, compute the average (A+B)/2 (x-axis) and the difference A-B (y-axis). Then compute the mean difference (the bias) and the limits of agreement, bias ยฑ 1.96 ร— SD of the differences. Chart a scatter of average versus difference and add three horizontal lines, each as a scatter series with two points (or a line series): bias, upper limit, lower limit. If most points sit between the limits and show no trend, the methods agree well enough to interchange (the clinical judgment is yours).

TEXT
Average   =(B2+C2)/2
Diff      =B2-C2
Bias      =AVERAGE(E2:E21)
Upper     =Bias + 1.96*STDEV.S(E2:E21)
Lower     =Bias - 1.96*STDEV.S(E2:E21)
JS Bias and limits of agreement

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Forest plot

A forest plot shows several effect estimates, each with a confidence interval, such as one line per study. Layout: Study, Estimate, Lower, Upper. In Excel:

  1. Chart Estimate (x) against a position number 1, 2, 3... (y) as a scatter; reverse the Y axis so the first study is on top.
  2. Add horizontal error bars (Chart Elements > Error Bars > More Options > Custom) with the positive error =Upper-Estimate and the negative error =Estimate-Lower.
  3. Add a vertical reference line at 0 (or 1 for ratios) using a series with two points, and replace the Y labels with study names via Value From Cells data labels.

Survival curve (Kaplan-Meier)

A survival curve shows the share of subjects still "alive" (or still subscribed, still working, not yet failed) over time. Some subjects leave the study before the event happens; these are censored, and the Kaplan-Meier method handles them correctly. The curve is a step chart: it stays flat between events and drops at each one.

Build a table with one row per distinct time, sorted ascending, and four columns:

Column Meaning Formula idea
Time Month of an event or of leaving the study the sorted distinct values
At risk Subjects still in the study just before this time =COUNTIF(Months,">="&A2)
Events Subjects who had the event at this time =COUNTIFS(Months,A2,Event,1)
Survival Running product of 1 - events / at risk =B1*(1-D2/C2) (start the first row from 1)

Then draw the Time and Survival columns as a Scatter with Straight Lines and switch it to steps by repeating each time twice in a helper column (the same trick as the step chart in advanced business charts). Add a horizontal line at 0.5 to read the median survival, the first time the curve reaches or crosses 50%.

Excel cannot run on this site, so this JavaScript stand-in builds the same table from ten made-up patients. A value of 1 in the event field means the event happened; 0 means the patient was censored.

JS Kaplan-Meier table, the same columns as in Excel

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice that the censored patient at month 5 does not lower the curve, but it does shrink the at-risk count for later rows, which makes the later drops larger. The median here is 12 months.

Three variables on one chart

A chart has two axes, so a third variable has to be encoded some other way. Excel gives you four honest options:

  • Bubble chart: X and Y are two variables, bubble size is the third (see above). Best for a few dozen points.
  • Secondary axis: plot the third series as a line on the secondary vertical axis (Format Data Series > Secondary Axis) when its units differ, for example temperature, pressure and time, with time on the shared horizontal axis. Colour the axis titles to match their series so readers know which scale to read. A third scale cannot be added natively; normalize it (see normalization) or use a small multiple.
  • Several series: for categories plus two measures, a clustered column or multi-line chart with one series per variable, built from a table whose columns are the variables.
  • Colour or a slicer: colour the points of a scatter chart by group by giving each group its own series, or let a slicer filter one group at a time.

Avoid 3-D charts to show a third variable: the perspective distorts the values and makes them hard to compare. If the three variables do not share an axis, two small charts side by side communicate better than one crowded chart.

Tolerance and control charts

A tolerance chart shows measurements against the allowed limits: plot the measured values as a line or scatter and add the lower and upper tolerance as two constant helper columns drawn as dashed lines; highlight out-of-tolerance points with a second series that uses =IF(OR(B2<$F$1, B2>$F$2), B2, NA()). NA() leaves a gap instead of plotting zero (Error-Handling Formulas).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a bubble chart add compared with a scatter chart?

    A bubble chart plots x and y like a scatter and uses a third column for the size of each marker.
  2. 2. Two trendlines have equations y = 2x + 10 and y = 5x - 20. At what x do they cross?

    Set 2x + 10 = 5x - 20, so 30 = 3x and x = 10. In cells that is (b2 - b1) / (m1 - m2) = (-20 - 10) / (2 - 5) = 10.
  3. 3. What is plotted on the X axis of an ogive?

    An ogive plots cumulative frequency against the upper boundary of each class; a frequency polygon uses midpoints instead.
  4. 4. How are the limits of agreement in a Bland-Altman plot calculated?

    Assuming roughly normal differences, about 95% of them fall within the bias plus or minus 1.96 standard deviations.

Summary

  • Scatter plots need numeric X and Y; a bubble chart adds size from a third column.
  • Compute trendline parameters with SLOPE and INTERCEPT, and the crossing of two lines is x = (b2 - b1) / (m1 - m2).
  • A frequency polygon joins class midpoints; an ogive plots cumulative frequency against upper boundaries.
  • A stem-and-leaf plot is built with INT, MOD and TEXTJOIN.
  • Bland-Altman: average on X, difference on Y, bias and limits bias ยฑ 1.96 SD.
  • Forest plots use scatter points with custom horizontal error bars; tolerance charts use constant limit series and NA() to highlight breaches.

Next: Flowcharts, Org Charts, SmartArt and Infographics.