Index
Symbols
#######, dates displaying as, 92
‘ (apostrophe), comments in VBA, 367
_ (underscore) character, line continuation in VBA, 366
2-D exploded pie charts, 126
2-D pie charts, 126
3-D chart elements, 39-40, 67-68
3-D charts
icons for, 13
misleading with, 427-430
rotation, 67-70
3-D exploded pie charts, 126
3-D format settings, formatting in VBA, 393-398
3-D pie charts, 126
3-D rotation in surface charts, 187
3-D rotation settings, formatting in VBA, 388-393
100 percent stacked charts, 132-133
icons for, 12-13
as trend charts, 86
100+ year datasets, date-based axis for, 98-100
1900, dates before, 95-100
A
accessing custom themes, 36
AddChart method (VBA), 367, 368
Adjusted Close column in stock charts, accounting for stock splits, 193-195
adjusting. See changing; modifying
Alexander, Mike, 441
aligning icon sets with numbers, 297-298
Alt+F1 shortcut key, creating charts, 17
analysis chart elements, 40-41
annotating charts with shapes, 228-230
apostrophe (‘), comments in VBA, 367
applying
gradients in VBA, 385-386
patterns as fills, 385
SmartArt layouts, 321
ApplyLayout method (VBA), 367, 375
area charts
drop lines, adding, 72-73
as trend charts, 85
arrows
limitations in SmartArt, 329
replacing columns with, 109-110
Upward Arrow layout (SmartArt), 331-332
Attiliis, Andy, 443
AutoShapes. See shapes
axes, formatting, 52-60. See also horizontal axis; vertical axis
axis scale. See scale of axis
axis titles, formatting, 45. See also horizontal axis title; vertical axis title
B
back wall (3-D charts), 40, 67-68
backward compatibility of VBA, 359, 367
Baker, Lisa, 440
Balance layout (SmartArt), 331
bar charts. See also data bars
column charts versus, 118-120
as comparison charts, 118-121
adding second series for time comparison, 121-122
stacked bar charts, 122-123
creating
with conditional formatting, 299-301
with REPT function, 301-300
extracting imported data with, 219-221
gap width and series separation settings, 400-402
paired bar charts, 164-172
discount and sales relationship case study, 167-169
multiple variable comparisons case study, 170-172
as trend charts, 85
bar of pie charts, 126, 140-141, 406-411
bars, floating, 231-233
Basic Cycle layout (SmartArt), 332
Beautiful Evidence (Tufte), 279, 286, 423, 440-441
benchmark charts, creating, 259-260
Bernardo, Brett, 262-263
bevel settings, formatting in VBA, 393-398
bins, creating for frequence distribution charts, 177-179
Birthcer, Dan, 171
blank cells, filling with zeros, 267
blank top-left cell, 24-25
blogs on charting, 442
bubble size settings, 411-412
when to use, 150
BubbleScale property (VBA), 412
Bunzel, Tom, 441
C
candlestick charts, 191
adding volume to, 211-214
colors in, 210-211
column sequence for, 192-193
creating, 209-210
showing volume and competitor prices case study, 214-217
case studies
candlestick charts, showing volume and competitor prices, 214-217
color gradients, creating, 65
comparing date systems, 91
converting series to gridlines, 235-239
converting SmartArt to shapes, 341-342
customer mapping, 314
date-based axis for time representations, 57
discount and sales relationship (paired bar charts), 167-169
Gene Zelazny radar chart, 182-185
MAD Magazine (paired matching charts), 172
moving charts to top of datasets, 19-20
multiple variable comparisons (paired bar charts), 170-172
pie of pie charts, creating, 141
printing charts for each customer, 418-419
reversing icon order in icon sets, 298
stratifying invoice amounts, 277
time-based axis, accurate data representation with, 87
waterfall charts, creating, 143
categories
reversing to mislead, 436-437
reversing with series, 20-22
of SmartArt, 317-318
category-based axis, 52, 56-57
choosing, 88
date-based axis versus in trend charts, 86-89
cells
contents, displaying in shapes, 336
top-left cell, leaving blank, 24-25
Cells() method (VBA), 363
changing. See also modifying
axis scale, 57-60
chart title in VBA, 382
data sequence, 23-24
default charts, 17
legend arrangements, 47
line types mid-line, 111-112
pivot chart types, 269-270
SmartArt colors, 320-321
SmartArt layouts, 321-322
theme colors, 9
chart elements
formatting, 41-75
3-D chart elements, 67-68
3-D chart rotation, 67-70
axes, 52-60
axis titles, 45
chart templates, creating, 82
chart title, 41-44
data labels, 48-50
data markers, 80-82
data points, 76
data tables, 51-52
drop lines, 72-73
error bars, 74-75
gridlines, 60-61
legends, 45-48
plot area, 63-67
series, 75-76
trendlines, 70-72
up/down bars, 73-74
in VBA, 382
list of, 38-41
chart layouts, choosing, 26-27
Chart Layouts gallery, 26
chart layouts, choosing, 26-27
in VBA, 375-377
Chart object (VBA), 367-368
Chart Styles gallery
color schemes, choosing, 27-28
in VBA, 375-377
chart templates
creating, 82
saving, 204
in VBA, 372-375
chart title, 39
deleting, 106-107
formatting, 41-44
meaningful titles, 104-107
resizing, 105-106
in VBA, 382
chart types
choosing
with Expand icon, 10
with Insert tab, 9
for trend charts, 82-86
combining in single chart, 221-225
icons for, 11-13
for pivot charts, changing, 269-270
in VBA, 372-373
ChartFormat method (VBA), 367
ChartFormat object (VBA), 382
charting blogs, 442
charting tutorials, 441
charting utilities, 443
ChartObject object (VBA), 367-368
charts
annotating with shapes, 228-230
benchmark charts, creating, 259-260
blank top-left cell, 24-25
comparison charts. See comparison charts
creating, 14-17
with conditional formatting, 299-301
contiguous data, selecting, 15
with Insert tab, 16-17
noncontiguous data, selecting, 15-16
number of combinations possible, 77
in PowerPoint with data from Excel, 353-355
with REPT function, 301-300
with single keystroke, 17
in VBA, 368
data sequence, changing, 23-24
default charts, changing, 17
delta charts, creating, 260-261
dynamic charts. See dynamic charts
exporting
to Web pages, 355-356
as XPS or PDF, 357
formatting in VBA, 382
3-D format settings, 393-398
3-D rotation settings, 388-393
fills, 383-386
Format method, 382
glow settings, 386-387
line settings, 386
reflection settings, 387
shadow settings, 387
soft edge settings, 387-388
innovative examples of, 262
lying with. See misleading charts
moving, 17-20
to new sheet, 25-26
in VBA, 368-369
between worksheets, 228
multiple charts on single chart, 239-243
organization charts, creating, 326-329
pasting to PowerPoint/Word 2010
with embedded workbook, 351-352
as link, 345-349
with original formatting, 349-350
as picture, 352-353
selecting paste options, 341-345
with updated formatting, 350-351
pivot charts. See pivot charts
referencing in VBA, 367-368, 370-371
relationship charts. See relationship charts
resizing, 17, 220-221, 368-369
resources for information, 439-443
saving as templates, 204
series and categories, reversing, 20-22
thermometer charts, creating, 258-259
trend charts. See trend charts
ChartStyle property (VBA), 376-377
ChartType property (VBA), 372
choosing
axis type, 88
chart types
with Expand icon, 10
with Insert tab, 9
for trend charts, 82-86
color schemes, 27-28
colors for custom themes, 33-34
contiguous data, 15
effects for custom themes, 30-31
fonts for custom themes, 35
forecast methods, 72
noncontiguous data, 15-16
paste options, 341-345
SmartArt layouts, 330-331
themes, 28-29
ClearToMatchStyle method (VBA), 377
clip art as data markers, 80
clustered and stacked charts, creating, 144-150
combining stacked and clustered columns, 144-145, 226-228
deleting legend entries, 148
icons for, 11-12
manually adjusting vertical axis scale, 148-150
resizing columns, 147
shifting series with blank series, 145-146
color scales, 278
creating, 293
customizing, 293
color schemes
choosing, 27-28
modifying with themes, 28-29
colors
in candlestick charts, 210-211
complementary colors, finding, 32-33
for custom themes, choosing, 33-34
gradients for plot area, 63-65
highlighting pie slices, 131-132
RGB color codes, 31-32
in SmartArt, changing, 320-321
theme colors, changing, 9
column charts
bar charts versus, 118-120
gap width and series separation settings, 400-402
in line charts showing stock volume, 197-199
as sparkcolumns, 280
as trend charts, 83
column depth (3-D charts), 40
columns. See also data markers
floating, 231-233
highlighting single columns, 107-109
reordering for stock charts, 192-193
replacing with arrows, 109-110
combining
chart types in single chart, 221-225
stacked and clustered columns, 144-145, 226-228
comments in VBA, 367
communication tips for trend charts, 103-112
changing line types, 111-112
highlighting chart sections, 109-111
highlighting single columns, 107-109
long titles, 104-107
replacing columns with arrows, 109-110
comparing date systems, 91
comparison charts, 118. See also relationship charts
bar charts as, 118-121
adding second series for time comparison, 121-122
stacked bar charts, 122-123
component comparison charts, 123-125
100 percent stacked column charts, 132-133
doughnut charts, 133-134
waterfall charts, 142-150
complementary colors, finding, 32-33
component comparison charts, 123-125
100 percent stacked column charts, 132-133
doughnut charts, 133-134
component decomposition charts (waterfall charts), 142-150
case study, 143
combining stacked and clustered columns, 144-145
deleting legend entries, 148
manually adjusting vertical axis scale, 148-150
resizing columns, 147
shifting series with blank series, 145-146
conditional formatting
creating charts with, 299-301
in-cell data bars, 271, 288-292
cone charts, misleading with, 430-431
contiguous data, choosing, 15
contrast, showing with surface charts, 185-187
converting
hexadecimal color codes to RGB color codes, 31-32
series to gridlines, 233-239
SmartArt to shapes, 341-342
text dates to dates, 89-95
text to WordArt, 77
zero to NA, 433-434
copying sparklines, 288. See also pasting to PowerPoint/Word 2010
correlation, testing with scatter charts, 161-164
cost of MapPoint, 303
CurrentRegion property (VBA), 363
custom shapes, creating, 338-339
custom themes. See themes, creating
customer mapping case study, 314
customizing. See also formatting
color scales, 293
data bars, 290-292
sparklines, 282-283
cut-and-paste method, moving charts, 19
cycle category (SmartArt), 318
cylinders. See data markers
D
Daily Dose of Excel blog, 442
Dashboard Reporting with Excel, 443
dashboards, resizing charts for, 220-221
customizing, 290-292
Excel 2007 options, 289
in-cell data bars, 271, 288-292
negative data bars, 290-289
new features, 289-290
for subset of cells, 292
data labels, 39
formatting, 48-50
for pie charts, 127-128, 129-130
in scatter charts, 155-156
for vertical axis, using XY series, 233-239
data markers, formatting, 80-82
data points
formatting, 76
in scatter charts, joining with lines, 156-158
two data points, trendlines from, 435-436
data preparation for pivot tables, 266-267
data representation problems in pie charts, 135-141
bar of pie charts, 140-141
pie of pie charts, 136-141
summary slice, 136
data sequence, changing, 23-24
data series. See series
data visualizations. See color scales; data bars; icon sets
datasets, moving charts to top, 19-20
date filters for pivot tables, 274-275
DATE function, 94
date-based axis, 56-57
choosing, 88
dates before 1900, 95-100
numeric years, plotting data by, 95
text dates, converting to dates, 89-95
text-based axis versus in trend charts, 86-89
as time-scale axis, 100-103
dates
before 1900, 95-100
comparing date systems, 91
converting text dates to, 89-95
displaying as #######, 92
storage format for, 90-91
DATEVALUE function, 91
Debartolo, Dick, 124
default charts, changing, 17
deleting
chart title, 106-107
legend entries, 148
legends, 48
delta charts, creating, 260-261
demographic data in MapPoint, 313
depth axis
built-in choices for, 53
in surface charts, 187
depth rotation in 3-D charts, 69
Chart Layouts gallery. See Chart Layouts gallery
Chart Styles gallery. See Chart Styles gallery
chart types in VBA, 372-373
custom chart templates in VBA, 372-375
Developer tab, enabling, 360
diagrams. See SmartArt
Dialog Launcher icon, chart types, choosing, 10
difference charts. See comparison charts
discount and sales relationship case study (paired bar charts), 167-169
Diverging Radial layout (SmartArt), 333
dotted-line reporting relationships in organization charts, 328-329
doughnut charts, 133-134, 404-406
DoughnutHoleSize property (VBA), 406
downloading stock chart data, 191-192
drawing with scatter charts, 160-161
dynamic charts, 243
creating, 341-342
with dynamic ranges, 251-254
scrolling charts, creating, 254-257
in UserForms, 413-415
with validation drop-downs, 248-251
dynamic ranges, charts with, 251-254
E
effects for custom themes, choosing, 30-31
embedded workbook, pasting charts with, 351-352
enabling
Developer tab, 360
VBA (Visual Basic for Applications), 360
End With statement (VBA), 366
End+Down arrow, 364
End+Up arrow, 364
enlarging sparklines, 285-286
Envisioning Information (Tufte), 440
equations
in SmartArt, limitations, 330
trendline equations, adding to charts, 71-72
Excel, versions of, 4
Excel 2003, 4
Excel 2007, 4
data bar options, 289
filtering pivot tables, 274-275
pivot tables in, 264-266
Excel 2010, new features, 4-8, 264-266, 289-290
Expand icon, chart types, choosing, 10
exploding pie slices, 129-131, 404-406
Explosion property (VBA), 404-405
exponential trendlines, 72
ExportChart method (VBA), 413
exporting charts
to Web pages, 355-356
as XPS or PDF, 357
extracting imported data with bar charts, 219-221
F
F11 shortcut key, creating charts, 17
Few, Steven, 442
filling blank cells with zeros, 267
fills for chart objects in VBA, 383-386
filtering pivot tables, 273-275
creating pivot charts per value, 275-276
with Excel 2007 filters, 274-275
with slicers, 273-274
FIND function, 94
finding
complementary colors, 32-33
lookup table values, 245-247
FirstSliceAngle property (VBA), 404
five-icon sets, 295
flexible data ranges in VBA, 363-364
floating
columns or bars, 231-233
legends in plot area, 46-47
fonts for custom themes, choosing, 35
forecasting with trendlines, 70-72
Format Data Labels dialog box, 50
Format Gridlines dialog box, 61-62
Format Legend dialog box, 48
Format method (VBA), 382
Format Series dialog box, 400
bar of pie and pie of pie charts, 406-411
bubble size settings, 411-412
gap width and series separation settings, 400-402
radar and surface charts, 412-413
rotating and exploding round charts, 404-406
secondary axis for series, 403-404
chart title, formatting, 44
preset Shape Effects, 78-79
Shape Effects feature, 77-78
Shape Fill feature, 77-78
Shape Outline feature, 77-78
Shape Styles gallery, 77-78
WordArt, converting text to, 77
formatting. See also customizing
bar of pie charts, 406-411
bubble size settings, 411-412
chart elements, 41-75
3-D chart elements, 67-68
3-D chart rotation, 67-70
axes, 52-60
axis titles, 45
chart templates, creating, 82
chart title, 41-44
data labels, 48-50
data markers, 80-82
data points, 76
data tables, 51-52
drop lines, 72-73
error bars, 74-75
gridlines, 60-61
legends, 45-48
plot area, 63-67
series, 75-76
trendlines, 70-72
up/down bars, 73-74
conditional formatting, creating charts with, 299-301
original formatting, pasting charts with, 349-350
pie of pie charts, 406-411
radar and surface charts, 412-413
shapes, 338
SmartArt elements, 322-324-326
updated formatting, pasting charts with, 350-351
in VBA, 382
3-D format settings, 393-398
3-D rotation settings, 388-393
fills, 383-386
Format method, 382
glow settings, 386-387
line settings, 386
reflection settings, 387
shadow settings, 387
soft edge settings, 387-388
formulas for extracting imported data, 219-221
four-icon sets, 295
Franklin, Charles, 442
frequency distribution charts, 161, 176-180
FREQUENCY function, 161, 177-179
functions
DATE, 94
DATEVALUE, 91
FIND, 94
HEX2DEC, 32
INDEX, 247-248
LEFT, 94
MATCH, 245-248
OFFSET, 244-245
REPT, 301-300
RIGHT, 94
SERIES, 235-236
SUMIFS, 15
VLOOKUP, 245-247
funnel charts, limitations, 329
G
gallery controls, 14
gap width in column and bar charts, 400-402
gear charts, limitations, 329
Gemignani, Chris, 442
Gemignani, Zach, 442
geographic maps. See MapPoint
Glass, Ira, 142
glow settings, formatting in VBA, 386-387
gradients
applying in VBA, 385-386
for plot area, 63-65
graphics. See pictures; shapes; SmartArt; WordArt
gridlines. See also horizontal gridlines; vertical gridlines
avoiding with data labels, 49
converting series to, 233-239
formatting, 60-61
groups of sparklines, creating, 281-282. See also chart groups
H
Has3DShading property (VBA), 412
HasRadarAxisLabels property (VBA), 412
HasSeriesLines property (VBA), 409
heat maps. See color scales
height rotation in 3-D charts, 70
HEX2DEC function, 32
hexadecimal color codes, converting to RGB, 31-32
hierarchy category (SmartArt), 318
creating organization charts, 326-329
labeled hierarchy charts, creating, 334-335
highlighting
chart sections, 109-111
pie slices
with color, 131-132
by exploding, 129-131, 404-406
single columns, 107-109
high-low-close charts
adding volume to, 204-209
creating, 199-202
as trend charts, 85
historical stock data, downloading, 191-192
Home tab, chart title, formatting, 43-44
horizontal axis, 39
built-in choices for, 53
formatting, 52-60
horizontal gridlines, 39, 60-61
I
icon sets, 278
aligning with numbers, 297-298
reversing order of icons, 298
setting up, 295-296
types of, 293-295
icons for chart types, 11-13
images. See pictures; shapes; SmartArt; WordArt
imported data, extracting with bar charts, 219-221
in-cell data bars, 3
IncrementRotationHorizontal method (VBA), 393
IncrementRotationVertical method (VBA), 393
IncrementRotationX method (VBA), 389
IncrementRotationY method (VBA), 393
IncrementRotationZ method (VBA), 393
INDEX function, 247-248
Insert Screen Clipping feature (Office 2010), 356-357
Insert tab, 8
chart types, choosing, 9
charts, creating, 16-17
inserting
shapes, 337-338
SmartArt, 319-320
Instant Cognition blog, 442
interactive training resources, 441
interface
gallery controls, 14
icons for chart types, 11-13
tabs in, 8-9
J
joining scatter chart points with lines, 156-158
Juice Analytics blog, 442
K
keyboard shortcuts. See shortcut keys
Kusleika, Dick, 442
L
labeled hierarchy charts, creating, 334-335
labels. See data labels
layouts in SmartArt, 316-317. See also chart layouts
applying, 321
Balance layout, 331
Basic Cycle layout, 332
changing, 321-322
creating new, 335-336
Diverging Radial layout, 333
selecting, 330-331
Table List layout, 333
Upward Arrow layout, 331-332
LEFT function, 94
legacy versions of Excel, 4
legends, 39
deleting entries, 148
formatting, 45-48
in pivot charts, 270
Leveraging SmartArt Graphics in the 2007 Microsoft Office System (Ozgur), 336
lighting settings, formatting in VBA, 393-398
line charts
drop lines, adding, 72-73
line types, changing mid-line, 111-112
as sparklines, 280
as stock charts, 187-190
column sequence for, 192-193
creating, 195-197
volume as column chart in, 197-199
as trend charts, 83-84
up/down bars, adding, 73-74
line continuation in VBA, 366
line settings, formatting in VBA, 386
line types, changing mid-line, 111-112
LineFormat object (VBA), 386
lines, joining scatter chart points with, 156-158
links, pasting charts as, 345-349
list category (SmartArt), 317
live training, 441
live updates to stock charts, creating, 218-221
logarithm trendlines, 72
logarithmic axis, 54-55
lookup tables, finding values in, 245-247
lying with charts. See misleading charts
M
Mac OS, date/time storage format, 90-91
macro recorder, 362
macros. See VBA (Visual Basic for Applications)
MAD Magazine case study (paired matching charts), 172
major gridlines, spacing, 61
MapPoint, 300-314
cost of, 303
customer mapping case study, 314
map styles in, 313
maps
creating in Excel, 304-308
numeric data on, 308-312
saving files, 305
zip code fields, misidentification, 310-311
maps
creating in Excel, 304-308
customer mapping case study, 314
numeric data on, 308-312
styles in MapPoint, 313
zooming, 307-308
MATCH function, 245-248
material type settings, formatting in VBA, 393-398
matrix category (SmartArt), 318, 329
Michaelides, David, 442
Microsoft MapPoint 2010. See MapPoint
mini toolbar, chart title, formatting, 43
minor gridlines, spacing, 61
misleading charts, 427
due to Excel’s noncooperation, 433-434
hiding true numbers, 437
perspective in, 427-430
with pictographs, 437-438
with reversed categories, 436-437
scale of axis in, 432-433
shrinking in, 430-431
stacked area charts, 434-435
trendlines from two data points, 435-436
mixing. See combining
modifying. See also changing
color schemes with themes, 28-29
Venn diagrams, 334
monthly sales, plotting with YTD sales, 114-116
moving
chart title, 43
to new sheet, 25-26
in VBA, 368-369
between worksheets, 228
data labels to avoid gridlines, 49
series to secondary axis, 403-404
moving average trendlines, 72
multiple charts on single chart, 239-243
multiple trends in single chart, 118
multiple variable comparisons case study (paired bar charts), 170-172
N
navigation in MapPoint maps, 307-308
negative data bars, 290-289
noncontiguous data, choosing, 15-16
numbers, aligning icon sets with, 297-298
numeric data on maps, 308-312
numeric years, plotting data by, 95
O
object references in VBA, 366
object variables in VBA, 364-365
object-oriented code, 362-363
ObjectThemeColor property (VBA), 384
OFFSET function, 244-245
OHLC charts, 190-191. See also high-low-close charts
column sequence for, 192-193
creating, 202-204
OneColorGradient method (VBA), 385
open-high-low-close charts. See OHLC charts
organization charts, creating, 326-329
Overlap property (VBA), 402
Ozgur, Suat, 336
P
Page Layout tab, themes, choosing, 28-29
paired bar charts, 164-172
discount and sales relationship case study, 167-169
multiple variable comparisons case study, 170-172
paired matching charts, 161, 172
pasting to PowerPoint/Word 2010
with embedded workbook, 351-352
as link, 345-349
with original formatting, 349-350
as picture, 352-353
selecting paste options, 341-345
with updated formatting, 350-351
patterns, applying as fills, 385
PDF file format, exporting charts as, 357
performance review example (radar charts), 180-182
perspective in misleading charts, 427-430
perspective rotation in 3-D charts, 68-69
picture category (SmartArt), 318
pictures
adding to SmartArt, 325-326
as data markers, 80
exporting charts as, 356-357, 413-415
misleading with, 437-438
pasting charts as, 352-353
for plot area, 65
pie charts, 126-132
comparing with doughnut charts, 133-134
data labels for, 127-128, 129-130
data representation problems, 135-141
bar of pie charts, 140-141
pie of pie charts, 136-141
summary slice, 136
highlighting slices
with color, 131-132
by exploding, 129-131, 404-406
as trend charts, 86
types of, 126
when not to use, 123-125
pie of pie charts, 126, 136-141, 406-411
pivot charts, 264
adding series to, 270-271
adding to pivot tables, 266
advanced operations in pivot table, 271-272
changing chart type, 269-270
new features, 264-266
stratifying invoice amounts case study, 277
values in Report Filter field, creating charts for, 275-276
pivot tables, 264
adding pivot charts to, 266
advanced operations in, 271-272
data preparation for, 266-267
filtering, 273-275
creating pivot charts per value, 275-276
with Excel 2007 filters, 274-275
with slicers, 273-274
new features, 264-266
plot area, 38
floating legends in, 46-47
formatting, 63-67
PNG file format, exporting charts as, 355-357
points. See data points
Politikal Arithmetik blog, 442
polynomial trendlines, 72
Power Excel Seminar, 335
power trendlines, 72
PowerPoint/Word 2010
creating charts with data from Excel, 353-355
pasting to
with embedded workbook, 351-352
as link, 345-349
with original formatting, 349-350
as picture, 352-353
selecting paste options, 341-345
with updated formatting, 350-351
preparing data for pivot tables, 266-267
preset Shape Effects, 78-79
PresetGradient method (VBA), 386
PresetTextured method (VBA), 384
printing charts for each customer case study, 418-419
process category (SmartArt), 318
professional chart designers, 442
pyramid category (SmartArt), 318, 330
pyramid charts, misleading with, 430-431
R
radar charts, 161
formatting, 412-413
Gene Zelazny chart case study, 182-185
performance review example, 180-182
when to use, 150
ranges
dynamic ranges, charts with, 251-254
flexible data ranges in VBA, 363-364
specifying with OFFSET function, 244-245
referencing charts in VBA, 367-368, 370-371
reflection settings, formatting in VBA, 387
relationship category (SmartArt), 318
relationship charts, 150-152. See also comparison charts
frequency distribution charts, 161, 176-180
paired bar charts, 164-172
discount and sales relationship case study, 167-169
multiple variable comparisons case study, 170-172
paired matching charts, 161, 172
radar charts, 161
Gene Zelazny chart case study, 182-185
performance review example, 180-182
scatter charts, 152-161
adding second series to, 157-159
data labels in, 155-156
drawing with, 160-161
joining points with lines, 156-158
testing correlation with, 161-164
trendlines in, 153-154
surface charts, 185-187
3-D rotation in, 187
depth axis in, 187
removing. See deleting
reordering columns for stock charts, 192-193
Report Filter field, values in, creating pivot charts for, 275-276
REPT function, 300
ResetRotation method (VBA), 393
resizing
chart title, 105-106
legends, 47
resources for information, 439-443
reversing
categories to mislead, 436-437
icon order in icon sets, 298
series and categories, 20-22
RGB color codes, 31-32
RIGHT function, 94
rotation
in 3-D charts, 67-70
3-D rotation settings, formatting in VBA, 388-393
of pie charts, 128-129, 404-406
in surface charts, 187
RotationX property (VBA), 389
RotationY property (VBA), 389
R-squared values, 162-164
S
saving
charts as templates, 204
MapPoint files, 305
themes, 35-36
Say It with Charts Complete Toolkit (Zelazny), 104, 439
scale of axis
changing, 57-60
in misleading charts, 432-433
for sparklines, setting, 284-286
scaling. See resizing
scatter charts, 152-161
adding second series to, 157-159
data labels in, 155-156
drawing with, 160-161
joining points with lines, 156-158
testing correlation with, 161-164
trendlines in, 153-154
when to use, 150
screen capture tools, 356-357
scrolling charts, creating, 254-257
secondary axis for series, 403-404
SecondPlotSize property (VBA), 410
Select Data icon, changing data sequence, 23-24
selecting. See choosing
seminars, 39-42
series. See also Format Series dialog box; XY series
adding for time comparison, 121-122
adding to pivot charts, 270-271
adding to scatter charts, 157-159
in chart groups, 401
combining chart types in single chart, 221-225
converting to gridlines, 233-239
formatting, 75-76
highlighting chart sections with, 109-111
reversing with categories, 20-22
secondary axis for, 403-404
shifting with blank series, 145-146
SERIES function, 235-236
SetElement method (VBA), 367, 377-382
shaded area map style, 313
shadow settings, formatting in VBA, 387
Shape Effects feature, 77-78-79
Shape Fill feature, 77-78
Shape Outline feature, 77-78
Shape Styles gallery, 77-78
shapes, 314, 336-339. See also SmartArt
annotating charts with, 228-230
converting SmartArt to, 341-342
creating custom, 338-339
as data markers, 80-82
inserting, 337-338
limitations in SmartArt, 329-330
sharing themes, 36
sheets, moving charts to, 25-26
shortcut keys, creating charts, 17
shrinking in misleading charts, 430-431
side wall (3-D charts), 40, 67-68
slicers, filtering pivot tables with, 273-274
SmartArt, 314
adding pictures to, 325-326
categories of, 317-318
changing colors, 320-321
converting to shapes, 341-342
inserting, 319-320
labeled hierarchy charts, creating, 334-335
layouts, 316-317
applying, 321
Balance layout, 331
Basic Cycle layout, 332
changing, 321-322
creating new, 335-336
Diverging Radial layout, 333
selecting, 330-331
Table List layout, 333
Upward Arrow layout, 331-332
limitations, 329-330
new features, 316
organization charts, creating, 326-329
text pane, 324-326
Venn diagrams, modifying, 334
Snag-It, 356-357
soft edge settings, formatting in VBA, 387-388
spacing of gridlines, 61
sparkcolumns, 280
axis scale, setting, 284-286
copying, 288
creating groups of, 281-282
creating in VBA, 423-427
customizing, 282-283
enlarging, 285-286
types of, 280
win/loss charts, creating, 285
Speedometer Chart Creator, 68
splitting pie of pie charts, 140. See also stock splits
SplitType property (VBA), 407
SplitValue property (VBA), 407
stacked and clustered charts, creating, 144-150
combining stacked and clustered columns, 144-145, 226-228
deleting legend entries, 148
manually adjusting vertical axis scale, 148-150
resizing columns, 147
shifting series with blank series, 145-146
stacked area charts as misleading, 434-435
stacked bar charts as comparison charts, 122-123
stacked column charts, 132-133
icons for, 11-12
stock charts, 85
candlestick charts, 191
adding volume to, 211-214
colors in, 210-211
creating, 209-210
showing volume and competitor prices case study, 214-217
downloading data for, 191-192
high-low-close charts
adding volume to, 204-209
creating, 199-202
line charts as, 187-190
creating, 195-197
volume as column chart in, 197-199
live updates, creating, 218-221
reordering columns for, 192-193
stock splits, accounting for, 193-195
types of, 187
stock splits, accounting for, 193-195
storage format for dates and times, 90-91
stratifying invoice amounts case study, 277
styles. See also Chart Styles gallery
subset of cells, data bars for, 292
SUMIFS function, 15
summary data, creating, 14
summary pie slices, creating, 136
3-D rotation in, 187
depth axis in, 187
formatting, 412-413
surface settings, formatting in VBA, 393-398
SWIPE (visual design store), 56-58
switching. See reversing
swoosh arrows (SmartArt), 331-332
T
Table List layout (SmartArt), 333
tables. See lookup tables; pivot tables
tabs in interface, 8-9
Takasaki, Earl, 262
templates
creating, 82
saving, 204
in VBA, 372-375
testing correlation with scatter charts, 161-164
text. See also WordArt
converting to WordArt, 77
formatting in SmartArt elements, 322-323
in shapes, updating, 336
text boxes as chart title, 106-107
text dates, converting to dates, 89-95
text pane in SmartArt, 324-326
choosing, 88
date-based axis versus in trend charts, 86-89
textures for plot area, 65
theme colors, changing, 9
themes
choosing, 28-29
creating, 29-36
colors, choosing, 33-34
complementary colors, finding, 32-33
converting hexadecimal to RGB color codes, 31-32
effects, choosing, 30-31
fonts, choosing, 35
RGB color codes, 31
saving, 35-36
sharing, 36
using custom, 36
thermometer charts, creating, 258-259
three-icon sets, 295
three-variable relationships with bubble charts, 172-176
time charts. See trend charts
time comparisons in bar charts, 121-122
time-based axis, 52, 56-57. See also date-based axis
accurate data representation with, 87
date-based axis as, 100-103
times, storage format for, 90-91
time-series charts, creating, 309-312
titles. See chart title
tolerance levels, viewing with error bars, 74-75
top-left cell, leaving blank, 24-25
training opportunities, 39-42
transparency of chart area and plot area, 66-67
trend charts
chart types, choosing, 82-86
communication tips, 103-112
changing line types, 111-112
highlighting chart sections, 109-111
highlighting single columns, 107-109
long titles, 104-107
replacing columns with arrows, 109-110
date-based axis
converting text dates to dates, 89-95
dates before 1900, 95-100
plotting data by numeric years, 95
text-based axis versus, 86-89
as time-scale axis, 100-103
monthly and YTD sales, plotting together, 114-116
multiple trends in single chart, 118
stacked column charts, 116-117
trendlines, adding, 113-114
trendlines, 40
adding to charts, 113-114
formatting, 70-72
R-squared values, 162-164
in scatter charts, 153-154
from two data points, 435-436
types of, 72
Tufte, Edward, 80, 278, 440-441
tutorials, websites for, 25-36
TwoColorGradient method (VBA), 385
U
underscore (_) character, line continuation in VBA, 366
updated formatting, pasting charts with, 350-351
updates, live updates to stock charts, 218-221
updating text in shapes, 336
Upward Arrow layout (SmartArt), 331-332
user interface. See interface
UserForms, dynamic charts in, 413-415
UserPicture method (VBA), 385
utilities for charting, 65-73
V
validation drop-downs, dynamic charts with, 248-251
value filters for pivot tables, 275
variables, object variables in VBA, 364-365
VaryByCategories property (VBA), 402
VBA (Visual Basic for Applications), 357
backward compatibility, 359, 367
Chart Layouts gallery, 375-377
Chart Styles gallery, 375-377
chart titles in, 382
chart types, 372-373
comments in, 367
creating charts, 368
custom chart templates, 372-375
data bars, creating, 420-422
enabling, 360
With and End With statements, 366
exporting charts as graphics, 413-415
exporting charts as images, 356
flexible data ranges, 363-364
Format Series dialog box
bar of pie and pie of pie charts, 406-411
bubble size settings, 411-412
gap width and series separation settings, 400-402
radar and surface charts, 412-413
rotating and exploding round charts, 404-406
secondary axis for series, 403-404
formatting charts, 382
3-D format settings, 393-398
3-D rotation settings, 388-393
fills, 383-386
Format method, 382
glow settings, 386-387
line settings, 386
reflection settings, 387
shadow settings, 387
soft edge settings, 387-388
line continuation, 366
macro recorder, 362
object variables, 364-365
object-oriented code, 362-363
pivot charts, creating, 415-419
referencing charts, 367-368, 370-371
resizing and moving charts, 368-369
SetElement method, 377-382
sparklines, creating, 423-427
tools for, 361-362
Visual Basic Editor, 361
VBA and Macros for Microsoft Excel (Jelen), 359
Venn diagrams, modifying, 334
versions of Excel, 4
vertical axis, 39
built-in choices for, 54
formatting, 52-60
labeling with XY series, 233-239
manually adjusting scale of, 148-150
viewing tolerance levels with error bars, 74-75
Visual Basic Editor, 361
Visual Basic for Applications. See VBA (Visual Basic for Applications)
Visual Business Intelligence blog, 50
visual design stores, 56-58
The Visual Display of Quantitative Information (Tufte), 20
Visual Explanations: Images and Quantities, Evidence and Narrative (Tufte), 22
VLOOKUP function, 245-247
volume-high-low-close charts, creating, 204-206
volume-open-high-low-close charts, creating, 211-212
W
waterfall charts, 142-150
case study, 143
combining stacked and clustered columns, 144-145
deleting legend entries, 148
manually adjusting vertical axis scale, 148-150
resizing columns, 147
shifting series with blank series, 145-146
Web pages, exporting charts to, 355-356
websites
charting blogs, 442
charting tutorials, 441
charting utilities, 443
professional chart designers, 59-64
Windows, date/time storage format, 90-91
With statement (VBA), 366
Woodbridge, George, 172
Word/PowerPoint 2010
creating charts with data from Excel, 353-355
pasting to
with embedded workbook, 351-352
as link, 345-349
with original formatting, 349-350
as picture, 352-353
selecting paste options, 341-345
with updated formatting, 350-351
worksheets, moving charts between, 228
X
X rotation in 3-D charts, 67
x-axis. See horizontal axis
Xcelsius, 72
XPS file format, exporting charts as, 357
XY scatter charts. See scatter charts
XY series
labeling vertical axis with, 233-239
multiple charts on single chart, 239-243
Y
Y rotation in 3-D charts, 67
y-axis. See vertical axis
years
dates before 1900, 95-100
plotting data by, 95
YTD sales, plotting with monthly sales, 114-116
Z
Zelazny, Gene, 104, 151, 182-185, 439, 440, 441
zero
converting to NA, 433-434
filling blank cells with, 267
zip code fields, misidentification in MapPoint, 310-311
zooming in MapPoint maps, 307-308