|
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
|
|
|
In Cell Charting with Worksheet Formulas
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. While reading my recent page on Dot Plots, my colleague Debra Dalgleish suggested I include a demonstration of cell formula dot plots, so I wrote some examples of in-cell bar charts and dot plots described below.
Note: Firefox 3 does not render "special" fonts like Symbol, Wingdings, or Webdings in a manner expected by most users. It has something to do with TTF fonts not being Unicode compatible. Bottom line: the Firefox developers have discontinued support for TTF fonts, and there seems to be no workaround. Pretend the lower case Ls below are black circles and the lower case Ns are black squares.
Bar Charts
| |
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.
|
Dot Plots
| |
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.
|
Changing Scales
| |
A |
B |
C |
D |
| 1 |
Labels |
Value |
Bar Chart 1 |
Bar Chart 2 |
| 2 |
Label A |
21 |
|||||||||||||||||||||||||||||||||||||||||| |
nnnnnnnnnn |
| 3 |
Label B |
20 |
|||||||||||||||||||||||||||||||||||||||| |
nnnnnnnnnn |
| 4 |
Label C |
16 |
|||||||||||||||||||||||||||||||| |
nnnnnnnn |
| 5 |
Label D |
19 |
|||||||||||||||||||||||||||||||||||||| |
nnnnnnnnn |
| 6 |
Label E |
16 |
|||||||||||||||||||||||||||||||| |
nnnnnnnn |
| 7 |
Label F |
19 |
|||||||||||||||||||||||||||||||||||||| |
nnnnnnnnn |
| 8 |
Label G |
13 |
|||||||||||||||||||||||||| |
nnnnnn |
| 9 |
Label H |
16 |
|||||||||||||||||||||||||||||||| |
nnnnnnnn |
| 10 |
Label I |
11 |
|||||||||||||||||||||| |
nnnnn |
| 11 |
Label J |
13 |
|||||||||||||||||||||||||| |
nnnnnn |
| 12 |
Label K |
10 |
|||||||||||||||||||| |
nnnnn |
| 13 |
Label L |
8 |
|||||||||||||||| |
nnnn |
| 14 |
Label M |
9 |
|||||||||||||||||| |
nnnn |
| 15 |
Label N |
9 |
|||||||||||||||||| |
nnnn |
| 16 |
Label O |
10 |
|||||||||||||||||||| |
nnnnn |
| 17 |
Label P |
5 |
|||||||||| |
nn |
There is a lot of flexibility in this approach. You can change the "scale" of the axis by using a multiplier. For example, the Arial pipe symbols in column C are rather thin, so you may want to double their number. Alternatively, you may want to halve the number of heavy wingdings blocks in column D, by using these adjusted formulas:
|
Bar Chart 1 (Column C)
=REPT("|",2*B2)
|
|
Bar Chart 2 (Column D)
=REPT("|",INT(B2/2))
|
Conditional Charts
| |
A |
B |
C |
E |
| 1 |
Labels |
Value |
Bar Chart 1 |
Dot Plot 1 |
| 2 |
Label A |
21 |
||||||||||||||||||||| |
+ |
| 3 |
Label B |
20 |
|||||||||||||||||||| |
+ |
| 4 |
Label C |
16 |
|||||||||||||||| |
o |
| 5 |
Label D |
19 |
||||||||||||||||||| |
o |
| 6 |
Label E |
16 |
|||||||||||||||| |
o |
| 7 |
Label F |
19 |
||||||||||||||||||| |
o |
| 8 |
Label G |
13 |
||||||||||||| |
o |
| 9 |
Label H |
16 |
|||||||||||||||| |
o |
| 10 |
Label I |
11 |
||||||||||| |
o |
| 11 |
Label J |
13 |
||||||||||||| |
o |
| 12 |
Label K |
10 |
|||||||||| |
o |
| 13 |
Label L |
8 |
|||||||| |
x |
| 14 |
Label M |
9 |
||||||||| |
x |
| 15 |
Label N |
9 |
||||||||| |
x |
| 16 |
Label O |
10 |
|||||||||| |
o |
| 17 |
Label P |
5 |
||||| |
x |
You can use worksheet conditional formatting to highlight certain values in different colors. For example, in the bar chart in column C, suppose you want values less than 10 to appear red, and those 20 and greater to appear blue. Select C2:C17, then choose Conditional Formatting from the Format menu. For Condition 1, select Formula Is in the dropdown, and enter the formula =$B2<10. Then click the Format button, and on the Font tab, change the color to red. Click the Add>> button, and for Condition 2, select Formula Is in the dropdown, and enter the formula =$B2>=20. Then click the Format button, and on the Font tab, change the color to blue. Press Enter, and you've got a conditional chart.
You can produce a conditional chart with different markers with an expanded formula. The dot plot in column E can be changed to show "x" for numbers less than 10 and "+" for numbers at least 20 by using this formula in cell E2 and filling it down:
=REPT(" ",B2-1)&IF(B2<10,"x",IF(B2>=20,"+","o"))
Naturally you could combine both approaches.
|
|
Waterfall Chart
Box and Whisker
