Dot Plot Formula: Wilkinson Vertical Stack Y-Index & Cleveland Category Dot Gap

Turn overlapping ties into neat vertical dot stacks using an expanding COUNTIF anchor!

Standard XY scatter plots overplot identical values on top of each other so three tied observations at X = 14 look like a single dot! Leland Wilkinson’s (1999, The American Statistician) Dot Plot solves tie overplotting by assigning each observation at X = B_i a 1-based vertical stack height Y_i = COUNTIF($B$2:B_i, B_i): the first 14 sits at Y = 1, the second 14 stacks cleanly at Y = 2, and the third 14 sits at Y = 3!

Meanwhile, William S. Cleveland’s (1984, JASA) Multiway Dot Plot replaces heavy ink-filled bar charts with clean horizontal category positions (plotting Metric A vs Metric B along a shared horizontal scale without forcing a zero baseline), where each row’s signed difference A - B and peak tie height MAX of stack counts summarize spread and shift!

Wilkinson Vertical Dot-Stack Height Y_i via Expanding COUNTIF($B$2:B5, B5)

Evaluates the 1-based vertical Y coordinate for row 5 by locking top anchor $B$2 and letting bottom reference B5 expand down the column.

=IF(B5 < 0, "Invalid Negative Observation", COUNTIF($B$2:B5, B5))

Total Mode Stack Height at Target Value X_target

Counts the full vertical dot-column height across B2:B8 at query value E2.

=IF(E2 < 0, "Invalid Query Value", COUNTIF(B2:B8, E2))

Further reading

• NIST Dataplot — Dot Plot Exploratory Graphics Reference