Home > Excel Dashboards >
An Excel Tutorial:
Compare Metrics by Category
Using Excel Dot Plot Charts
Use these little-known Excel charting tricks to compare many
categories of performance data...clearly.
by Charley Kyd
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
struck me was that profits didn't follow about the same trend as
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
the data for the previous chart. The row and column addresses don't
matter, because I named each column with the label shown.
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
To set set up this column, first enter these two formulas in the
first two cells of the MaxMin column:
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
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
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:
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
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
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:
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
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
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.
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.
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
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.
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,
We have only one step left. We need to format the figure for
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.
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
Category Axis and
Bar-Line Combination Chart, by Jon Peltier, and Funchrt2.zip, by Steven
Finally, be sure to check out
extensive article about dot plots.