|
Excel Books
| |
Books that I own and use while developing in Excel
|
|
Goods and Services
| |
Excel or charting related products and services which I use or feel are worthwhile additions
|
|
|
Dot Plots
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.
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).
|
|
| |
A |
B |
| 1 |
Labels |
Value |
| 2 |
Label A |
21 |
| 3 |
Label B |
20 |
| 4 |
Label C |
16 |
| 5 |
Label D |
19 |
| 6 |
Label E |
16 |
| 7 |
Label F |
19 |
| 8 |
Label G |
13 |
| 9 |
Label H |
16 |
| 10 |
Label I |
11 |
| 11 |
Label J |
13 |
| 12 |
Label K |
10 |
| 13 |
Label L |
8 |
| 14 |
Label M |
9 |
| 15 |
Label N |
9 |
| 16 |
Label O |
10 |
| 17 |
Label P |
5 |
Tabular Data
|
Bar Chart
|
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 the reward for reading this article through is a link to a download of a simple Excel add-in 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.
| |
A |
B |
C |
D |
| 1 |
Labels |
Value |
Bar Chart 1 |
Bar Chart 2 |
| 2 |
Label A |
21 |
||||||||||||||||||||| |
nnnnnnnnnnnnnnnnnnnnn |
| 3 |
Label B |
20 |
|||||||||||||||||||| |
nnnnnnnnnnnnnnnnnnnn |
| 4 |
Label C |
16 |
|||||||||||||||| |
nnnnnnnnnnnnnnnn |
| 5 |
Label D |
19 |
||||||||||||||||||| |
nnnnnnnnnnnnnnnnnnn |
| 6 |
Label E |
16 |
|||||||||||||||| |
nnnnnnnnnnnnnnnn |
| 7 |
Label F |
19 |
||||||||||||||||||| |
nnnnnnnnnnnnnnnnnnn |
| 8 |
Label G |
13 |
||||||||||||| |
nnnnnnnnnnnnn |
| 9 |
Label H |
16 |
|||||||||||||||| |
nnnnnnnnnnnnnnnn |
| 10 |
Label I |
11 |
||||||||||| |
nnnnnnnnnnn |
| 11 |
Label J |
13 |
||||||||||||| |
nnnnnnnnnnnnn |
| 12 |
Label K |
10 |
|||||||||| |
nnnnnnnnnn |
| 13 |
Label L |
8 |
|||||||| |
nnnnnnnn |
| 14 |
Label M |
9 |
||||||||| |
nnnnnnnnn |
| 15 |
Label N |
9 |
||||||||| |
nnnnnnnnn |
| 16 |
Label O |
10 |
|||||||||| |
nnnnnnnnnn |
| 17 |
Label P |
5 |
||||| |
nnnnn |
|
|
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.
|
| |
A |
B |
E |
F |
| 1 |
Labels |
Value |
Dot Plot 1 |
Dot Plot 2 |
| 2 |
Label A |
21 |
o |
l |
| 3 |
Label B |
20 |
o |
l |
| 4 |
Label C |
16 |
o |
l |
| 5 |
Label D |
19 |
o |
l |
| 6 |
Label E |
16 |
o |
l |
| 7 |
Label F |
19 |
o |
l |
| 8 |
Label G |
13 |
o |
l |
| 9 |
Label H |
16 |
o |
l |
| 10 |
Label I |
11 |
o |
l |
| 11 |
Label J |
13 |
o |
l |
| 12 |
Label K |
10 |
o |
l |
| 13 |
Label L |
8 |
o |
l |
| 14 |
Label M |
9 |
o |
l |
| 15 |
Label N |
9 |
o |
l |
| 16 |
Label O |
10 |
o |
l |
| 17 |
Label P |
5 |
o |
l |
|
|
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
| |
A |
B |
C |
| 1 |
Labels |
Value |
Height |
| 2 |
Label A |
21 |
0.96875 |
| 3 |
Label B |
20 |
0.90625 |
| 4 |
Label C |
16 |
0.84375 |
| 5 |
Label D |
19 |
0.78125 |
| 6 |
Label E |
16 |
0.71875 |
| 7 |
Label F |
19 |
0.65625 |
| 8 |
Label G |
13 |
0.59375 |
| 9 |
Label H |
16 |
0.53125 |
| 10 |
Label I |
11 |
0.46875 |
| 11 |
Label J |
13 |
0.40625 |
| 12 |
Label K |
10 |
0.34375 |
| 13 |
Label L |
8 |
0.28125 |
| 14 |
Label M |
9 |
0.21875 |
| 15 |
Label N |
9 |
0.15625 |
| 16 |
Label O |
10 |
0.09375 |
| 17 |
Label P |
5 |
0.03125 |
|
|
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.
|
|
|
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.
|
|
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.
|
|
|
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 Plotter Utility
In this page I have described techniques for constructing dot plots in Excel. I have also written a Dot Plotter utility that allows the user to construct dot plots directly from the data; the utility adds the necessary data to the table and then creates and formats the chart from this table. The utility is designed to work in Excel versions 2000, 2002, and 2003; it has not been tested in any Macintosh version or in Excel 97 or 2007, it may or may not work on these versions. The Dot Plotter utility is a beta version, available at no cost. Ultimately it will be incorporated into a commercial Advanced Charting utility. The Dot Plotter dialog and a sample Dot Plot it has created are shown below:
Click here to read about the Dot Plotter Utility.
|
|
Waterfall Chart
Box and Whisker
