Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Excel Dashboards

 

Books at Amazon.com

 

Dot Plots

Peltier Tech Dot Plot UtilityThis tutorial shows how to create Dot Plots, including the specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes time, can be prone to error, and becomes tedious.

I have created the Peltier Tech Dot Plot Utility to create dot plots automatically from raw data. This utility, a standard Excel add-in, arranges data in the required layout, then constructs an embedded chart (not an in-cell chart) with the right combination of chart types. This is a commercial product, tested on hundreds of machines in a wide variety of configurations, which saves time and aggravation.

Please visit the Peltier Tech Dot Plot Utility page or the Peltier Tech Dot Plot Utility Documentation page for more information.

 

Often when a series of values and corresponding labels is to be plotted, the chart of choice is a bar chart. The table and bar chart below show a typical bar chart. This is a reasonably straightforward chart, easy to make and fairly clear in its presentation.

To simplify the manual creation of dot plots, I have created an Excel add-in which allows you to set up a simple data range, and a few simple formatting options through a dialog. For more information about the Professional Dot Plot Utility, visit PTS Dot Plot Utility, or read the Documentation for the PTS Dot Plot Utility.

Below right is a "Dot Plot", a way to chart the same information that is cleaner and according to research more easily understood (see for example Good Graphs for Better Business, by William S. Cleveland and N.I. Fisher, and Dot Plots: A Useful Alternative to Bar Charts, by Naomi B. Robbins, Ph.D.). Dot plots are a little more work to set up than bar charts, but it's well worth it. A variation of the dot chart, with the points connected by a line, is the answer to the elusive question, "How do I rotate my line chart so the labels go up the side?" (see below).

 
Dot Plot Data
 
Tabular Data

Bar Chart
Bar Chart

Dot Plot
Dot Plot

The construction of dot plots in Excel has been covered by Charley Kyd (Compare Metrics by Category Using Excel Dot Plot Charts) and by Kelly O'Day (Excel Dot Plots). The following is my take on dot plots, and at the end of this article through is a link to a commercial Excel utility that will build dot plots for you.

In-Cell Formula Approach

Lately there has been great interest in lightweight in-cell charting, using worksheet formulas and sometimes a special font, to display values graphically. John Walkenbach showed this technique a long time ago, and the Juice Analytics blog recently had an extensive discussion in Lightweight data exploration in Excel. My colleague Debra Dalgleish suggested I include a demonstration of cell formula dot plots, so I include a couple examples of in-cell bar charts and dot plots below.

Dot Plot

     

The configuration required to make these in-cell bar charts are as follows:

Bar Chart 1 (Column C)
Formula: =REPT("|",B2)
Font: Arial

The formula means "Repeat the character '|' as many times as the value in B2".
Enter the formula in cell C2 and fill it down to C17.

Bar Chart 2 (Column D)
Formula: =REPT("n",B2)
Font: Wingdings

The formula means "Repeat the character 'n' (or in Wingdings, 'n') as many times as the value in B2".
Enter the formula in cell D2 and fill it down to D17.

Dot Plot

     

The configuration required to make these in-cell dot plots are as follows:

Dot Plot 1 (Column E)
Formula: =REPT(" ",B2-1)&"o"
Font: Courier New

The formula means "Repeat the space character as many times as the value in B2 minus one, then the letter 'o'".
Enter the formula in cell E2 and fill it down to E17.

Dot Plot 2 (Column F)
Formula: =REPT(" ",B2-1)&"l"
Font: Wingdings

The formula means "Repeat the space character as many times as the value in B2 minus one, then the letter 'l' (or in Wingdings, 'l').
Enter the formula in cell F2 and fill it down to F17.

In-Cell Formula Charts covers this topic, with additional techniques involving scaling and conditional formatting of the in-cell charts.

Preparing the Data for an Excel Chart

Dot Plot Data

     

This technique requires three columns. In addition to the Labels column and the Values column, there is a column of calculated values ("Height"). The chart is a combination Bar-XY series. The Labels and Values columns are used to construct the bar chart; the Labels appear up the left side of the chart. The XY series uses the Values column for X and the Height column for Y. A formula is used in the Height column so the Heights are spaced uniformly between zero and one. The formula is:

    =(ROWS($A$2:$A$17)-ROW()+ROW($A$2:$A$17)-0.5)/ROWS($A$2:$A$17)

Constructing the Chart

 

Select the first two columns (including the top row containing "Labels" and "Values"). Start the chart Wizard, and create a bar chart (the choice of stacked or clustered does not matter).

Change the shape of the chart so it's taller than wide. Remove the legend, title, and gridlines; this can be done by selecting each and pressing Delete.

 

 

Select and copy the second and third columns (including the top row containing "Value" and "Height"), select the chart, and use Paste Special from the Edit menu to add the selection to the chart as a new series, with the series in columns, series names in the first row, and categories (X values) in the first column. This adds another bar series to the chart.

 

 

 

Double click the first series (dull blue in Excel's default colors), and on the Axes tab, choose the Secondary axis. The remaining series on the primary axis is now hidden behind the series on the secondary axis.

 

 

On the Chart menu, choose Chart Options, and click on the Axes tab. Check the box for the Secondary Category (X) Axis.

 

 

Select the series to the left (plum colored by default), go to the Chart menu, choose Chart Type, and select the XY chart type, subtype markers without lines.

 

 

Rescale the primary Y axis: double click on the axis, and on the Scale tab, uncheck the Auto box in front of the minimum setting to lock in zero, then type in 1 for the maximum setting, and leave the Auto box unchecked. This lines up the XY markers with the bars of the other series.

Now hide the primary Y axis, but do not delete it. Double click the left hand vertical axis, and on the Patterns tab, choose None wherever possible (line, major and minor tick marks, and tick mark labels).

 

 

Move the primary Y axis from the left to the right. Do this by double clicking the primary X axis, and on the Scale tab, checking the Value (Y) Axis Crosses at Maximum Value checkbox.

 

 

Move the vertical axis (secondary category, X, axis) labels to the left. This is done by double clicking on the secondary Y axis, along the top of the chart. On the Scale tab, uncheck the Category (X) Axis Crosses at Maximum Value checkbox.

Now hide the secondary Y axis, but do not delete it. Double click the top horizontal axis, and on the Patterns tab, choose None wherever possible (line, major and minor tick marks, and tick mark labels).

 

 

Fix up the left hand (secondary X) axis. Double click the axis, and on the Scale tab, check the Categories in Reverse Order checkbox, and uncheck the Value (Y) Axis Crosses at Maximum Category checkbox.

The chart now has bars and markers at exactly the same places. This can be useful for other chart types, such as Gantt charts.

 

 

Hide the bar chart series to finish the simple Dot Plot. Double click the bar series, and on the Patterns tab, choose None for Line and Area. Double click on the ugly pink squares, and choose a more appropriate combination of marker style and color on the Patterns tab.

Dot Plot

 

 

There are some optional features you can add to the Dot Plot. To create leader lines, double click the XY (dot) series. On the X Error Bars tab, click the Minus icon, select Percentage, and enter 100 in the percentage box. Double click on the error bars and format them as desired.

Dot Plot

 

 

Alternatively you can add gridlines. Go to Chart Options on the Chart Menu, and on the Gridlines tab, check the Major X Axis Gridlines box.

Dot Plot

 

 

Finally you can format the XY series so it has lines connecting the data markers. This is the answer to the elusive question, "How do I rotate my line chart so the labels go up the side?"

Dot Plot

 

PTS Dot Plot Utility

Peltier Technical Services Inc offers the PTS Dot Plot Utility which creates dot plots based on the protocol in the tutorial above, but it does all of the required calculations and chart manipulations automatically.

The Dot Plot Utility makes quick work of Dot Plots of one or more series, such as this population pyramid replacement showing US Male and Female population distribution by age group.

Dot Plot - US Population Distribution by Gender and Age Group

Like all PTS Chart Utilities, the Dot Plotter makes regular Excel charts, so you can format them to suit your needs.

Dot Plot - US Population Distribution by Gender and Age Group

Also like all PTS Chart Utilities, the Dot Plotter works in all Windows versions of Excel between 2000 and 2007. In “Classic” Excel, a Dot Plot menu item is added to the PTS Charts menu, and this menu is added if it is not already present. In Excel 2007, this Dot Plot item is added as a button to the PTS Charts ribbon tab, and this tab is added if it does not exist. A simple dialog allows the user to select some formatting options.

PTS Dot Plot Utility Dialog

For more information, follow these links to the PTS Dot Plot Utility and Documentation for the PTS Dot Plot Utility.

 

Page copy protected against web site content infringement by Copyscape

Peltier Tech Chart Utility

Create Excel dashboards quickly with Plug-N-Play reports.


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile