Excel Charts

Part I: Chart Basics - Page 33
Part I: Chart Basics - Page 34
Chapter 1: Introducing Excel Charts - Page 35
How Excel Handles Charts - Page 36
Embedded charts - Page 37
Chart sheets - Page 38
Parts of a Chart - Page 39
Parts of a Chart - Page 40
Parts of a Chart - Page 41
Creating Charts - Page 42
Using the Chart Wizard - Page 43
Hands On: Creating a Chart with the Chart Wizard - Page 44
Hands On: Creating a Chart with the Chart Wizard - Page 45
Chart Wizard – Step 1 of 4 - Page 46
Chart Wizard – Step 2 of 4 - Page 47
VERIFYING THE DATA RANGE - Page 48
SPECIFYING THE CATEGORY DATA AND SERIES DATA - Page 49
Chart Wizard – Step 3 of 4 - Page 50
Chart Wizard – Step 4 of 4 - Page 51
Chart Wizard – Step 4 of 4 - Page 52
Basic Chart Modifications - Page 53
Changing the chart type - Page 54
Other modifications - Page 55
Other modifications - Page 56
Chapter 2: Understanding Chart Types - Page 57
Choosing a Chart Type - Page 58
Standard Chart Types - Page 59
Column charts - Page 60
Column charts - Page 61
Bar charts - Page 62
Bar charts - Page 63
Bar charts - Page 64
Line charts - Page 65
Line charts - Page 66
Pie charts - Page 67
Pie charts - Page 68
Pie charts - Page 69
XY (scatter) charts - Page 70
Area charts - Page 71
Area charts - Page 72
Doughnut charts - Page 73
Radar charts - Page 74
Surface charts - Page 75
Bubble charts - Page 76
Stock charts - Page 77
Stock charts - Page 78
Cylinder, cone, and pyramid charts - Page 79
Custom Chart Types - Page 80
About custom chart types - Page 81
Creating your own custom chart types - Page 82
Creating your own custom chart types - Page 83
STEP 2: FORMAT THE CHART - Page 84
STEP 2: FORMAT THE CHART - Page 85
STEP 3: ADD THE CHART TO THE CUSTOM CHART TYPE GALLERY - Page 86
STEP 4: TEST AND REFINE - Page 87
STEP 4: TEST AND REFINE - Page 88
Chapter 3: Working with Chart Series - Page 89
Chapter 3: Working with Chart Series - Page 90
Chapter 3: Working with Chart Series - Page 91
Adding a New Series to a Chart - Page 92
Adding a new series using Paste Special - Page 93
Adding a new series using the Source Data dialog box - Page 94
Deleting a Chart Series - Page 95
Modifying the Data Range for a Chart Series - Page 96
Modifying the Data Range for a Chart Series - Page 97
Using the Source Data dialog box to change series data - Page 98
Editing the SERIES formula to change series data - Page 99
Editing the SERIES formula to change series data - Page 100
Understanding Series Names - Page 101
Changing a series name - Page 102
Adjusting the Series Plot Order - Page 103
Adjusting the Series Plot Order - Page 104
Charting a Noncontiguous Range - Page 105
Using Series on Different Sheets - Page 106
Handling Missing Data - Page 107
Handling Missing Data - Page 108
Controlling a Data Series by Hiding Data - Page 109
Using Range Names in a SERIES Formula - Page 110
Using Range Names in a SERIES Formula - Page 111
Using Range Names in a SERIES Formula - Page 112
Unlinking a Chart Series from Its Data Range - Page 113
EDITING PICTURES - Page 114
Converting range reference to arrays - Page 115
Using Combination Charts - Page 116
Working with multiple axes - Page 117
CREATING A SECONDARY VALUE AXIS - Page 118
CREATING A SECONDARY VALUE AXIS - Page 119
CREATING A SECONDARY VALUE AXIS - Page 120
Chapter 4: Formatting and Customizing Charts - Page 121
Selecting chart elements - Page 122
SELECTING WITH THE MOUSE - Page 123
SELECTING WITH THE KEYBOARD - Page 124
Common chart elements - Page 125
Common chart elements - Page 126
Using the Format dialog boxes - Page 127
Adjusting Borders and Areas: General Procedures - Page 128
Formatting borders - Page 129
Formatting borders - Page 130
Formatting Chart Background Elements - Page 131
Formatting Chart Background Elements - Page 132
Working with the Plot Area - Page 133
Working with the Plot Area - Page 134
Formatting Series - Page 135
Basic series formatting - Page 136
Using pictures and graphics for series formatting - Page 137
Using pictures and graphics for series formatting - Page 138
Working with Chart Titles - Page 139
Adding titles to a chart - Page 140
Linking title text to a cell - Page 141
Working with a Chart's Legend - Page 142
Adding or removing a legend - Page 143
Moving or resizing a legend - Page 144
Deleting a legend entry - Page 145
Value Axis versus Category Axis - Page 146
Value Axis versus Category Axis - Page 147
Value axis scales - Page 148
Value axis scales - Page 149
USING A LOGARITHMIC SCALE - Page 150
OTHER AXIS OPTIONS - Page 151
Using time-scale axes - Page 152
USING TIME VALUES - Page 153
Creating a multiline category axis - Page 154
Formatting axis patterns and tick marks - Page 155
Removing axes - Page 156
Axis number formats - Page 157
Working with Gridlines - Page 158
Formatting gridlines - Page 159
Working with Data Labels - Page 160
Editing data labels - Page 161
Problems and limitations with data labels - Page 162
Problems and limitations with data labels - Page 163
Problems and limitations with data labels - Page 164
Problems and limitations with data labels - Page 165
Working with a Chart Data Table - Page 166
Problems and limitations with data tables - Page 167
Modifying 3-D charts - Page 168
Modifying 3-D charts - Page 169
Formatting a surface chart - Page 170
Chapter 5: Working with Trendlines and Error Bars - Page 171
Chart types that support trendlines - Page 172
Adding a trendline - Page 173
Adding a trendline - Page 174
Adding a trendline - Page 175
Formatting a trendline - Page 176
Linear Trendlines - Page 177
Linear Trendlines - Page 178
Linear forecasting - Page 179
Getting the trendline values - Page 180
Getting the trendline values - Page 181
Nonlinear Trendlines - Page 182
Logarithmic trendline - Page 183
Power trendline - Page 184
Exponential trendline - Page 185
Polynomial trendline - Page 186
HIGHER-ORDER POLYNOMIAL TRENDLINES - Page 187
Displaying a Moving Average - Page 188
Creating your own moving average data series - Page 189
Using Error Bars in a Chart Series - Page 190
Chart types that support error bars - Page 191
Chart types that support error bars - Page 192
Formatting or modifying error bars - Page 193
Using custom error bars - Page 194
Connecting series points to a trendline - Page 195
Connecting series points to a trendline - Page 196
Error bar alternatives - Page 197
Other Series Enhancements - Page 198
Drop Lines - Page 199
High-low lines - Page 200
HOW UP/DOWN BARS WORK - Page 201
HOW UP/DOWN BARS WORK - Page 202
Chapter 6: Working with AutoShapes and Other Graphics - Page 203
Chapter 6: Working with AutoShapes and Other Graphics - Page 204
Inserting AutoShapes - Page 205
Adding text to an AutoShape - Page 206
Adding text to an AutoShape - Page 207
THE COLORS AND LINES TAB - Page 208
THE PROTECTION TAB - Page 209
Selecting multiple objects - Page 210
Grouping objects - Page 211
Using the Drawing Toolbar - Page 212
Aligning objects - Page 213
Spacing objects evenly - Page 214
Adding shadows and 3-D effects - Page 215
Changing the AutoShape defaults - Page 216
Changing the AutoShape defaults - Page 217
Using the Microsoft Clip Organizer - Page 218
Inserting graphics files - Page 219
Copying graphics by using the Clipboard - Page 220
Importing from a digital camera or scanner - Page 221
Using the Office Applets - Page 222
Using the Office Applets - Page 223
Using the Office Applets - Page 224
Creating WordArt - Page 225
Using AutoShapes and pictures with charts - Page 226
USING AN AUTOSHAPE AS A CHART BACKGROUND - Page 227
USING AN AUTOSHAPE AS A CHART BACKGROUND - Page 228
USING GRAPHICS IN A CHART SERIES - Page 229
USING GRAPHICS IN A CHART SERIES - Page 230
CREATING SEMITRANSPARENT BARS OR COLUMNS - Page 231
USING A PICTURE IN A CHART'S PLOT AREA OR CHART AREA - Page 232
USING A PICTURE IN A CHART'S PLOT AREA OR CHART AREA - Page 233
USING A PICTURE IN A CHART'S PLOT AREA OR CHART AREA - Page 234
Calling attention to a cell - Page 235
Creating flow diagrams - Page 236
Pasting pictures of cells - Page 237
Pasting pictures of cells - Page 238
Part II: Mastering Charts - Page 239
Part II: Mastering Charts - Page 240
Chapter 7: Creating Interactive Charts - Page 241
Hands-On: Creating a Self-Expanding Chart - Page 242
Hands-On: Creating a Self-Expanding Chart - Page 243
Creating the chart - Page 244
Modifying the series - Page 245
Modifying the series - Page 246
Testing it - Page 247
ABOUT THE OFFSET FUNCTION - Page 248
Controlling a Series with a Scroll Bar - Page 249
Creating the chart - Page 250
Creating the chart - Page 251
Specifying the Beginning and End Point for a Series - Page 252
Defining the names - Page 253
Specifying the Beginning and Number of Points for a Series - Page 254
Defining the names - Page 255
Adding the user interface elements - Page 256
Plotting the Last n Data Points in a Series - Page 257
Defining the names - Page 258
Using array formulas - Page 259
Using array formulas - Page 260
Creating named formulas - Page 261
Creating the chart - Page 262
Using a macro to force a recalculation - Page 263
Creating the chart - Page 264
Defining the names - Page 265
Creating the chart - Page 266
Defining the names - Page 267
Modifying the chart series - Page 268
Creating the Option Button controls - Page 269
Creating the chart's data range - Page 270
Creating the chart - Page 271
Creating the chart - Page 272
Chapter 8: Charting Techniques and Tricks - Page 273
Adding horizontal reference lines to a chart - Page 274
Adding horizontal reference lines to a chart - Page 275
Using background columns to represent a vertical line - Page 276
Adding horizontal or vertical "bands" - Page 277
Adding horizontal or vertical "bands" - Page 278
Creating an XY chart with colored quadrants - Page 279
Creating an XY chart with colored quadrants - Page 280
Charts That Use a Single Data Point - Page 281
Creating a gauge chart - Page 282
Creating a gauge chart - Page 283
Using a Dummy Axis - Page 284
ABOUT THIS CHART - Page 285
Labeling an axis with non-equal intervals - Page 286
Labeling an axis with non-equal intervals - Page 287
Column and Bar Chart Variations - Page 288
Column and Bar Chart Variations - Page 289
Creating a step chart - Page 290
Creating a step chart - Page 291
A bar-line combination chart - Page 292
Varying column widths - Page 293
Varying column widths - Page 294
Conditional colors - Page 295
Creating a population pyramid chart - Page 296
Creating Gantt charts - Page 297
Identifying the maximum and minimum values in a series - Page 298
Identifying the maximum and minimum values in a series - Page 299
Shading between two series in a line chart - Page 300
XY Chart Variations - Page 301
XY Chart Variations - Page 302
Drawing a circle around data points - Page 303
Connecting data points to the axes with error bars - Page 304
Displaying data points on axes - Page 305
Creating a timeline - Page 306
Mathematics and Statistics Applications - Page 307
USING THE ANALYSIS TOOLPAK TO CREATE A FREQUENCY DISTRIBUTION - Page 308
USING THE ANALYSIS TOOLPAK TO CREATE A FREQUENCY DISTRIBUTION - Page 309
USING ADJUSTABLE BINS TO CREATE A HISTOGRAM - Page 310
Plotting a normal curve - Page 311
Plotting a normal curve - Page 312
Plotting z-scores with standard deviation bands - Page 313
Calculating the area under a curve - Page 314
Calculating the area under a curve - Page 315
Creating a box plot - Page 316
Creating a box plot - Page 317
Plotting mathematical functions - Page 318
PLOTTING FUNCTIONS WITH ONE VARIABLE - Page 319
Plotting functions with two variables - Page 320
Plotting functions with two variables - Page 321
Creating a 3-D scatter plot - Page 322
Creating "Impossible" Charts by Stacking and Overlaying - Page 323
Overlaying charts - Page 324
A 2-D CHART COMBINED WITH A 3-D CHART - Page 325
CREATING A STACKED 3-D COLUMN CHART - Page 326
SIMULATING A "BROKEN" VALUE AXIS - Page 327
A Gradient Contour Chart - Page 328
Plotting Data without a Chart - Page 329
Plotting with ASCII characters - Page 330
Plotting with ASCII characters - Page 331
Plotting with ASCII characters - Page 332
Chapter 9: Using Pivot Charts - Page 333
A pivot table example - Page 334
A pivot table example - Page 335
Data appropriate for a pivot table - Page 336
Creating a Pivot Table - Page 337
Creating a Pivot Table - Page 338
Step1: Specifying the data location - Page 339
Step 3: Completing the pivot table - Page 340
USING A DIALOG BOX TO LAY OUT A PIVOT TABLE - Page 341
USING THE PIVOTTABLE FIELD LIST TOOLBAR TO LAY OUT A PIVOT TABLE - Page 342
THE FINISHED PRODUCT - Page 343
THE FINISHED PRODUCT - Page 344
THE FINISHED PRODUCT - Page 345
Grouping Pivot Table Items - Page 346
Grouping Pivot Table Items - Page 347
Working with Pivot Charts - Page 348
Understanding pivot charts - Page 349
Modifying the data displayed in a pivot chart - Page 350
Modifying the data displayed in a pivot chart - Page 351
Hiding the pivot chart field buttons - Page 352
Unlinking a pivot chart from a pivot table - Page 353
Method 2: Destroy the pivot table - Page 354
Method 4: Copy the pivot chart to a different workbook - Page 355
Method 4: Copy the pivot chart to a different workbook - Page 356
Selecting a row to plot - Page 357
Using data from multiple sheets - Page 358
Using data from multiple sheets - Page 359
Using data from multiple sheets - Page 360
Chapter 10: Using Excel Charts in Other Applications - Page 361
Excel Copy and Paste options - Page 362
Exporting a Chart to a GIF File - Page 363
Copy and paste to a graphics application - Page 364
Use a simple VBA macro - Page 365
Use a simple VBA macro - Page 366
Importing an Excel chart into MS Graph - Page 367
Importing an Excel chart into MS Graph - Page 368
Using Paste Special for more control - Page 369
Deciding which method is best - Page 370
Chapter 11: Avoiding Common Chart-Making Mistakes - Page 371
Chart Accuracy - Page 372
Exaggerating differences or similarities - Page 373
Exaggerating differences or similarities - Page 374
Exaggerating differences or similarities - Page 375
Problems with Chart Type Selection - Page 376
Problems with pie charts - Page 377
Problems with pie charts - Page 378
Problems with pie charts - Page 379
Problems with negative values - Page 380
XY charts with the Smoothed Line option - Page 381
XY charts with the Smoothed Line option - Page 382
Chart Complexity - Page 383
Chart Complexity - Page 384
Chart Style - Page 385
Be aware of grayscale conversion - Page 386
Be aware of grayscale conversion - Page 387
A Chart-Maker's Checklist - Page 388
A Chart-Maker's Checklist - Page 389
A Chart-Maker's Checklist - Page 390
Chapter 12: Just for Fun - Page 391
Animated AutoShapes - Page 392
Animated charts - Page 393
Fun with Trigonometry - Page 394
Hypocycloid charts - Page 395
Hypocycloid charts - Page 396
Radar chart designs - Page 397
Chart Art - Page 398
Chart Art - Page 399
An Analog Clock Chart - Page 400
XY Sketch - Page 401
Roll the Dice - Page 402
Create Your Own Clip Art? - Page 403
Create Your Own Clip Art? - Page 404
Create Your Own Clip Art? - Page 405
Create Your Own Clip Art? - Page 406
Part III: Using VBA with Charts - Page 407
Part III: Using VBA with Charts - Page 408
Chapter 13: Introducing VBA - Page 409
Chapter 13: Introducing VBA - Page 410
Chapter 13: Introducing VBA - Page 411
Introducing the Visual Basic Editor - Page 412
Activating the VB Editor - Page 413
The VB Editor components - Page 414
Using the Project window - Page 415
ADDING A NEW VBA MODULE - Page 416
MINIMIZING AND MAXIMIZING WINDOWS - Page 417
ENTERING CODE MANUALLY - Page 418
USING THE MACRO RECORDER - Page 419
USING THE MACRO RECORDER - Page 420
Chapter 14: VBA Programming Concepts - Page 421
An Introductory Example - Page 422
An Introductory Example - Page 423
Using Comments in Your Code - Page 424
Understanding data types - Page 425
Declaring variables - Page 426
Using constants - Page 427
USING BUILT-IN CONSTANTS - Page 428
Using Assignment Statements - Page 429
Using Arrays - Page 430
Using VBA's Built-In Functions - Page 431
Controlling Execution - Page 432
The Select Case construct - Page 433
Looping blocks of instructions - Page 434
FOR EACH-NEXT LOOPS - Page 435
Basic Error Handling - Page 436
Using Ranges - Page 437
Some useful properties of ranges - Page 438
Chapter 15: Understanding Objects, Collections, Properties, and Methods - Page 439
Chapter 15: Understanding Objects, Collections, Properties, and Methods - Page 440
More about collections - Page 441
Properties and Methods - Page 442
Properties and Methods - Page 443
Object methods - Page 444
Learning more - Page 445
The Chart Object Model - Page 446
The Chart Object Model - Page 447
The Chart Object Model - Page 448
Using the Macro Recorder - Page 449
Using the Macro Recorder - Page 450
Cleaning up recorded macros - Page 451
USING THE WITH-END WITH CONSTRUCT - Page 452
Watching the macro recorder - Page 453
Watching the macro recorder - Page 454
Recording options - Page 455
Executing Macros - Page 456
Assigning a macro to an object - Page 457
Assigning a macro to an object - Page 458
Chapter 16: Using VBA with Charts: Examples - Page 459
Activating a Chart - Page 460
Determining the Context for a Macro - Page 461
Determining whether the active chart is embedded - Page 462
Determining whether the active chart is embedded - Page 463
Counting and Looping through Charts - Page 464
Counting and Looping through Charts - Page 465
Changing the location of all charts - Page 466
Printing All Embedded Charts - Page 467
Basic Formatting and Customizing Examples - Page 468
Applying a random color gradient - Page 469
Conditional color formatting - Page 470
Conditional color formatting - Page 471
Setting axis values - Page 472
Freezing the text size of chart elements - Page 473
Pasting a semitransparent shape to a series - Page 474
Pasting a semitransparent shape to a series - Page 475
Creating Charts with VBA - Page 476
Recording a macro - Page 477
Executing the recorded macro - Page 478
Creating a chart from data on different worksheets - Page 479
Creating a chart from data on different worksheets - Page 480
Sizing and Aligning Charts - Page 481
Applying Data Labels - Page 482
Applying linked data labels - Page 483
Prompting for a range - Page 484
Exporting Charts as GIF Files - Page 485
Exporting Charts as GIF Files - Page 486
Exporting Charts as GIF Files - Page 487
Determining the Ranges Used in a Chart - Page 488
Determining the Ranges Used in a Chart - Page 489
Protecting Charts - Page 490
Chart protection properties - Page 491
Modifying Chart sheet protection properties directly - Page 492
Creating a Scrolling Chart - Page 493
Event Procedure Examples - Page 494
HIDING AND DISPLAYING CHARTS - Page 495
ADJUSTING AXIS SCALING - Page 496
Chart event procedures - Page 497
USING THE MOUSEDOWN EVENT - Page 498
USING THE MOUSEDOWN EVENT - Page 499
USING THE MOUSEDOWN EVENT - Page 500
USING THE MOUSEOVER EVENT - Page 501
USING THE MOUSEOVER EVENT - Page 502
USING THE MOUSEOVER EVENT - Page 503
USING THE MOUSEOVER EVENT - Page 504
Appendix: What's on the CD-ROM - Page 505
The Chart Gallery directory - Page 506
The Chapter Examples directory - Page 507
The Chapter Examples directory - Page 508
The Chapter Examples directory - Page 509
The Chapter Examples directory - Page 510
The Chapter Examples directory - Page 511
The Chapter Examples directory - Page 512
The Chapter Examples directory - Page 513
The Chapter Examples directory - Page 514
The Chapter Examples directory - Page 515
Troubleshooting - Page 516