Management reports often must compare measures of performance by some type of category.
For example, reports could compare sales by product, variance by department, sales by customer, profits by division, and so on.
Analysts typically display such information in tables. Unfortunately, this can make patterns in the data difficult to see. Often, therefore, we can’t just read tables of data; we must study them.
One excellent way to reduce this problem is to use Excel dot plots, as shown in these two figures.
The first figure shows yields for ten different varieties of barley in Grand Rapids County, Minnesota, in 1931 and 1932. You can see that the yields fell for every variety except Velvet. The second figure shows the top 15 public companies in Fortune 500 in 2005.
Notice that I sorted each figure by one of its measures. Where that’s possible to do, it tends to help the eye to find meaning in the data.
For example, what strikes you first when you view the figure at the right? What struck me was that profits didn’t follow about the same trend as revenues.
In fact, it turns out that although Wal-Mart had the most revenue, five other companies on this list had larger profits.
Also, notice that both figures use markers with different shapes and colors.
By changing both the color and shape of the marker we help the reader’s eye to distinguish between them. Also, of course, we help to make the figures readable when they’re printed in black and white.
From an Excel perspective, these dot plots are generated using Excel’s XY (scatter) chart feature. However, the “dot plot” (or “dotplot”) term has been used for years in business and scientific applications. You can learn more about this particular type of dot plot from the references at the end of this article.
Set Up the Data to Create Excel Dot Plots
Here’s the data for the previous chart. The row and column addresses don’t matter, because I named each column with the label shown.
To begin, enter the labels in a new worksheet, and then enter the data shown for Company, Revenue, and Profits.
To define the range names, select the entire table, then choose Insert, Name, Create. In the Create Names dialog, ensure that only Top Row is selected, then choose OK.
The MaxMin column contains the largest and smallest values in the two data columns.
To set set up this column, first enter these two formulas in the first two cells of the MaxMin column:
=MAX(Revenue:Profits)
=MIN(Revenue:Profits)
In the third cell of the MaxMin column, type the equal sign, click on the second cell (which contains 647 in this instance), and press Enter. Then copy this formula down the column.
The Step column determines the vertical position of the dots in the chart. This column always contains values like those shown. That is, the first cell always contains .5; the second always contains 1.5; and so on.
The easiest way to set this up is to type an equal sign in the second cell of the Step column, click on the cell that contains .5, type +1, and then press Enter. Then copy this cell down the column as shown.
Now, after the data is entered it’s time to create your chart.
Begin the Dot Plot Chart
To begin, save your workbook as “DotPlot500.xls”, because this is what I named my workbook. After you’re done, you can name your workbook anything you want.
The Dot Plot chart is a combination chart. It combines a bar chart structure with scatter chart markers. To begin, we need to create the bar chart structure. To do so, follow these steps:
1. Select the Company and MaxMin columns of data. (Do not select the title cells.)
2. Choose Insert, Chart. In Step 1 of the Chart Wizard, choose the bar chart type and the top-left sub type. Then choose Finish. After you do so, the chart will look something like this.
3. We want the Plot Area to be white, not gray or transparent. Select the Plot Area. Choose Format, Selected Plot Area. In the Area section of the Patterns tab, select the white color option, then choose OK.
Shortly, we’ll hide the bar chart’s bar of data, shown in the figure at the bottom of the Plot Area. But not yet.
Enter the Two Scatter Chart Series
We now need to add the other two data series. The easiest way to do so is to copy this formula:
=SERIES(“Profits”,DotPlot500.xls!Profits,DotPlot500.xls!Step,2)
Select the Chart Area, then paste the formula into your formula bar. (Excel won’t let you enter the formula if you haven’t named your workbook DotPlot500.xls, or if you haven’t assigned the names Profits and Step.)
After you enter the formula you’ll see virtually no change in your chart. We now need to fix that problem. After you enter the formula, Excel typically deselects it. To select the formula again, select the bar in your bar chart, then press the up arrow on your keyboard. You now should see the new SERIES formula in your formula bar.
Excel is displaying this SERIES formula as a bar chart. But we want to display it as a scatter chart. To do so, choose Chart, Chart Type. In the Standard Types tab choose Scatter; choose the top-left chart sub-type; then choose OK.
You now should see a sprinkling of square markers in your Plot Area, as shown here. At this point, the markers probably won’t line up with their labels, but that’s not a problem. (Also, your markers might have a different color. But again, no problem.)
Copy this formula:
=SERIES(“Revenue”,DotPlot500.xls!Revenue,DotPlot500.xls!Step,3)
Select the Chart Area; paste the formula into your formula bar; then press Enter. After you do so, your chart should look something like this.
Before going further, let’s take a brief look at the two SERIES formulas copied into the chart. The SERIES function has this format:
=SERIES(name_ref, categories, values, plot_order)
For the name_ref, we just entered the name as text. The categories for an XY (scatter) chart contain the X coordinate; here, the names Profits and Revenues define those ranges. The values arguments contain the Y coordinates; these are the Step values for both series. Finally, the plot_order is merely 2 for the first SERIES formula above, and 3 for the second.
Now, all we have to do is to clean up some formatting, and the dot plot will be done.
Begin to Format Your Chart
We kept the bar from Series 1 so we could select Series 2 easily. Because we now can select Series 2 and 3 directly, we can hide the bar. To do so, first select the bar. Choose Format, Selected Data Series. In the Patterns tab, choose None for both Border and Area. Then choose OK.
The legend now shows an empty spot for Series 1. To cure that problem, select the legend box. Select the legend for Series 1, as shown here. Then delete the legend for Series 1 by pressing the Delete key.
The original data is in millions of dollars. But the chart should display data in billions of dollars. We could change that formatting in either the chart or the data. Usually, it’s easier to format the data.
Therefore, select the MaxMin, Revenue, and Profits columns. Choose Format, Cells. In the Number tab, choose the Custom category. In the Type textbox enter: “#,##0,” (without the quotes). Make sure you include the ending comma, which tells Excel to shift the decimal point so that millions are displayed as billions.
When you recalculate, the chart should look something like this figure.
Now it’s time to fix the Step values.
In the chart, these values must start at zero and end one-half value above the largest step value. Because the largest value in the Step column of our data table is 14.5, the chart value must end at 15.
Therefore, select the Y axis at the right side of the Plot Area. (This is called the Secondary Value Axis.) In the Scale tab enter 0 as the Minimum value and 15 as the Maximum value. Make sure that neither checkbox for these items is checked. Then choose OK.
By default, your chart probably isn’t tall enough to display all 15 companies. You therefore need to change the size of the chart. Before you do so, however, let’s make two adjustments that will save you some time.
First, when you change the size of a chart object Excel often changes the size of the font. Generally, this isn’t something we want to occur. Therefore, to turn off this “feature” of Excel, select the chart; choose Format, Selected Chart Area. In the Font tab, deselect Auto Scale. Then choose OK.
Second, to save horizontal space Excel often displays the Y-axis labels at an angle. We don’t want this to happen. Therefore, select the Y-axis at the left of the Plot Area; choose Format, Selected Axis. In the Alignment tab, click one time on the up-arrow by the Degrees setting; then click the down-arrow one time. After you’ve done so, the Degrees setting should be at zero and the Automatic orientation setting should be deselected. Then choose OK.
Now, click and drag the bottom of the chart object downward until all companies pop into view. When they do, your chart should look something like this figure.
Check to make sure that the top and bottom X-axis labels are identical. The top labels determine the horizontal position of your data points; the bottom labels determine the position of the grid. They always must have exactly the same values.
Because both sets of X-axis labels aren’t needed, select the axis with the labels you want to hide. Choose Format, Selected Axis. In the Patterns tab, choose None for these three sets of controls: Major tick mark type, Minor tick mark type, and Tick mark labels.
Similarly, choose the Y axis at the right side of the Plot Area. Choose Format, Selected Axis. In the Patterns tab, choose None for the same three sets of controls.
Select the legend. To remove its border and its background, choose Format, Selected Legend. In the Patterns tab click on None in both the Border and the Area sections. Then choose the Placement tab. Choose Top, then choose OK.
When Excel moves the legend above or below the chart, the chart gets shorter and fatter. Probably, therefore, you will need to make the chart taller and thinner. This will take some trial and error, because if you make the chart too skinny, some of the Y-axis labels will disappear.
Here’s a tip: If you change the font for the Y-axis labels to Arial Narrow, you’ll be able to reduce the width of your chart significantly without causing your labels to disappear.
Finish Chart Formatting
We need to remove the line that Excel created automatically when we added the Revenues data series. To do so, select this data series in the chart, then choose Format, Selected Data Series. In the Patterns tab, choose None in the Line section. Then choose OK.
We now need to add horizontal gridlines. Select the Chart Area. Choose Chart, Chart Options. In the Gridlines tab, select both Major and Minor X-axis gridlines. Then choose OK.
The Major gridlines go between each set of markers; the Minor gridlines go through the markers. We want to hide the Major Gridlines and display only the Minor ones. Unfortunately, Excel doesn’t provide a direct way to do this. Therefore, we cheat.
Select any of the horizontal gridlines that don’t touch a marker. Choose Format, Selected Gridlines. In the Patterns tab, set the color to white. Then choose OK.
You’ll notice that the vertical gridlines show tiny gaps where the white horizontal gridlines cross. These gaps virtually disappear when we take the next step, which we need to do anyway.
When the gridlines are black they are too intense. Therefore, select the black horizontal gridlines; choose Format, Selected Gridlines. In the Patterns tab set the color to a light shade of gray. Then take the same step for the vertical gridlines.
Your display now should look something like the figure at the right, above.
We have only one step left. We need to format the figure for presentation.
Two tricks are involved.
The first trick is to rely on the cells behind the chart for much of your formatting. Here, for example, the title area and the buff-colored chart background use cell formatting, not chart formatting.
So that you can see the cells behind the chart, you need to remove the Chart Area’s formats. To do so, select the Chart Area. Choose Format, Selected Chart Area. In the Patterns tab, choose None for both the Border and the Area.
Remember when we turned the Plot Area from gray to white? We specified white rather than None so that the Plot Area would stand out from the cell background, as shown here.
The other trick that’s obvious in this figure is that the colors aren’t typical Excel colors. The article, Display Any Colors in Excel, explains how to set up colors like this in versions of Excel prior to Excel 2007.
Further Information
To learn more about the ideas behind dot plots, I recommend Dot Plots: A Useful Alternative to Bar Charts, by Naomi B. Robbins, Ph.D. Also, in Good Graphs for Better Business, William S. Cleveland and N.I. Fisher compare the use of dot plots to other display techniques. Robbins and Cleveland were co-workers at Bell Labs, where many of the techniques they describe were developed.
To learn more about creating charts that combine a bar-chart framework with scatter-chart markers, I recommend Vertical Category Axis and Bar-Line Combination Chart, by Jon Peltier, and Funchrt2.zip, by Steven Bullen.
Finally, be sure to check out Jon Peltier’s extensive article about dot plots.
