What’s a “Technical” Dot Plot?

This chart is crisp and well suited for publication in a technical journal, hence the name I’ve given it, “Technical” Dot Plot. The chart below show results of a hypothetical clinical trial, where the X values (categories) are three different cleansing approaches, and the Y values are the individual responses, where a lower value indicates fewer incidences of infection.

Columns of Dots Plot

This “technical” dot plot chart shows each individual response, to give you an idea of the distribution of results. This is more detailed than a simple average, or even a box plot, which simplifies the data distribution into its min, max, median, and quartiles. If desired, each category could have different marker (dot) shapes, sizes, or colors. However, that isn’t necessary.

Other Kinds of Dot Plot

If you Google “Dot Plot”, or search Wikipedia or any other resource, you’ll learn that the phrase “dot plot” can mean many different things. In addition to what I call the “Technical” Dot Plot shown above, there are also “Cleveland” Dot Plots, “Kindergarten” Dot Plots, and Scatter Plots. Technical Dot Plots, Cleveland Dot Plots, and Scatter Plots are all effective means of displaying data. Unfortunately people who display data effectively do not always come up with effective and unique names for their charts.

“Cleveland” Dot Plot

This type of graphic is named for William Cleveland who described them in a 1984 paper with coauthor Robert McGill. They were presented nicely by Naomi Robbins in Dot Plots: A Useful Alternative to Bar Charts.

Dot Plot per William Cleveland

Peltier Tech Charts for Excel can create this type of chart as easily as any built-in Excel chart.

These are often a better alternative to horizontally-oriented line charts, especially since the category labels can be reasonably long and still remain horizontal for improved readability.

Line Chart

“Kindergarten” Dot Plot

I call this the “Kindergarten” Dot Plot, because it feels more like a fingerpainted art project than a serious means of visualizing data. It is built by adding a dot to the chart every time you encounter the given value in a set of numbers. See another value, dip your finger in paint and put another blot on the paper.

Kindergarten Dot Plot

To me, the histogram below is a better representation of the distribution above. The simple bars show the values without distracting me into counting all those stupid little dots.

Histogram

Peltier Tech Charts for Excel can create histograms easily in Excel. Excel’s old Analysis Toolpak used to make column charts that were passed off as histograms, and Excel 2016 for Windows has finally introduced native histograms.

You can represent a histogram as a line chart, below left, often called a “Probability Polygon”. It’s not a terrible representation, though I prefer the histogram.

Often you’ll see a probability polygon shown with markers and without line segments, and it’s also called a dot plot, below right. I find this inferior, because the dots are unconnected and seem to be strewn across the chart.

Probability Polygon and another Dot Plot

Scatter Plot

A Scatter Plot (a/k/a XY Chart, Scatter Chart, etc.) is often called a Dot Plot because dots (markers) are used to indicate individual data points.

Scatter Plot

Make a Technical Dot Plot

I’ll describe a few different ways to create Technical Dot Plots. The end result is the same, but the protocol differs because you may have different data layouts available for use.

Regardless of data layout, we will make a combination chart, using a column chart type to get the nice horizontal axis labels and XY Scatter types to get the dots.

Data Layout A – Multiple X and Y Series

The first data layout has separate X and Y values for each category in the chart. The X and Y ranges for each set of dots may be next to each other:

or the X and Y ranges for each set of dots may be separated from each other:

You also need a small table with the category names and zero values. Select this small table, and insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Copy and select the orange shaded data range, either B2:C12 in the example with the X and Y ranges together or B2:B12 and E2:E12 in the example with the separated X and Y ranges. To select multiple areas, select the first area, then hold Ctrl while selecting additional areas.

Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as a new column series (below left). The categories are temporarily messed up: the three original categories are forced to the left because the new series has more points.

Right click on the added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Select the series of dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1 align the points above the first category along the horizontal axis (below left).

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly reduces steps needed for this in a combination chart like this.

Copy and select the gray shaded data range, either D2:E12 in the example with X and Y ranges together or C2:C12 and F2:F12 in the example with separated X and Y ranges. Select the chart, and use Paste Special to add the data as new series, values in columns, series name in first row, categories in first column. The result is a set of gray dots added to the chart (below right). Excel remembers that the previous added series was changed to an XY type with markers and no lines on the primary axis, so it uses these settings for the new series. The X values of 2 position the gray dots above the second category along the X axis.

Copy and select the gold shaded data range, either F2:G12 in the example with X and Y ranges together or D2:D12 and G2:G12 in the example with separated X and Y ranges. Select the chart, and use Paste Special to add the data as before. The result is a set of gold dots added to the chart (below left). Excel again applies an XY chart type with markers and no lines and assigns the series to the primary axis. The X values of 3 position the gold dots above the third category along the X axis.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

The technical dot plot is ready for publication.

Data Layout B – Single X with Multiple Y Series

The second data layout has a single set of X values with three sets of Y values, as shown below. This is easier than the previous data layout, because it requires only one Copy-Paste-Special cycle. Don’t worry about the blank cells; Excel will ignore them in the chart.

You also need a small table with the category names and zero values. From this small table, you insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Select and copy the data range (B2:E32 in the example above). Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as three new column series (below left). The categories are temporarily messed up: the new series have many more points, forcing the three original categories to the left.

Right click on the first added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Change the chart type of the second and third added series from column to XY Scatter, so that there are three sets of colored dots on the secondary axes (below left).

Select the first series of dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1 align the points above the first category along the horizontal axis (below right).

Format the second and third set of dots so they are also plotted on the primary axis (below left). The X values of 2 and 3 position the dots above the second and third categories on the X axis.

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly streamlines the process to build a combination chart like this.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

The technical dot plot is ready to go.

Data Layout C – Single X and Y Series

The third data layout has a single set of X values and a single set of Y values, as shown below. This is the easiest of all, because it requires only one Copy-Paste-Special cycle, and there is only one series to be modified once it’s been added to the chart.

You also need a small table with the category names and zero values. From this small table, you insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Select and copy the data range (B2:E32 in the example above). Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as a new column series (below left). The categories are temporarily messed up: the three original categories are forced to the left because the new series has way more points.

Right click on the added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Select the  series of orange dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1, 2, and 3 align the points above the first, second, and third category along the horizontal axis (below right).

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly simplifies formatting of a combination chart like this.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

Your new technical dot plot is good to go.

The post Make Technical Dot Plots in Excel appeared first on Peltier Tech Blog.