Charts and Graphs for Microsoft(R) Office Excel 2007 (Business Solutions)

Charts and Graphs for Microsoft® Office Excel® 2007......Page 1
Contents......Page 4
Introduction......Page 16
Choosing the Right Chart Type......Page 17
Using Excel as Your Charting Canvas......Page 18
This Book’s Objectives......Page 19
Special Elements in This Book......Page 20
Next Steps......Page 21
What’s New in Excel 2007 Charts......Page 22
New Charting Tools and Menus......Page 23
Using the Insert Tab to Select a Chart Type......Page 24
Understanding the Chart Thumbnail Icons......Page 25
Using Gallery Controls......Page 28
Selecting Contiguous Data to Chart......Page 29
Creating a Chart by Using the Insert Ribbon Icons......Page 30
Moving a Chart Within the Current Worksheet......Page 32
Locating a Chart at the Top of Your Dataset......Page 34
Reversing the Series and Categories of a Chart......Page 35
Changing the Data Sequence by Using Select Data......Page 36
Leaving the Top-Left Cell Blank......Page 38
Moving a Chart to a Different Sheet......Page 39
Choosing a Chart Layout......Page 40
Choosing a Color Scheme......Page 41
Modifying a Color Scheme by Changing the Theme......Page 42
Creating Your Own Theme......Page 43
Choosing Effects for a Custom Theme from an Existing Theme......Page 44
Converting from Hexadecimal to RGB......Page 45
Finding Complementary Colors......Page 46
Specifying a Theme’s Colors......Page 47
Specifying a Theme’s Fonts......Page 48
Saving a Custom Theme......Page 49
Next Steps......Page 50
Accessing Element Formatting Tools......Page 52
Chart Labels and Axis......Page 53
Analysis Elements......Page 55
Formatting Chart Elements......Page 56
Formatting a Chart Title......Page 57
Formatting an Axis Title......Page 59
Formatting a Legend......Page 60
Adding Data Labels to a Chart......Page 63
Adding a Data Table to a Chart......Page 65
Formatting Axes......Page 66
Using a Date-Based Axis to Represent Time......Page 71
Displaying and Formatting Gridlines......Page 74
Formatting the Plot Area......Page 76
Creating a Custom Gradient......Page 78
Formatting the Chart Walls and Floor of a 3-D Chart......Page 80
Controlling 3-D Rotation in a 3-D Chart......Page 81
Forecasting with Trendlines......Page 83
Adding Drop Lines to a Line or Area Chart......Page 86
Showing Acceptable Tolerances by Using Error Bars......Page 87
Formatting a Series......Page 88
Using the Shape Styles Gallery......Page 89
Using the Shape Fill and Shape Effects......Page 90
Using Preset Shape Effects......Page 91
Using Clip Art as a Data Marker......Page 92
Using a Shape in Place of a Data Marker......Page 93
Next Steps......Page 94
Choosing a Chart Type......Page 96
Accurately Representing Data Using a Time-Based Axis......Page 99
Converting Text Dates to Dates......Page 101
Comparing Date Systems......Page 103
Dates Not Recognized as Dates: Numeric Years......Page 107
Dates Not Recognized as Dates: Dates Before 1900......Page 108
Using a Workaround to Display a Time-Scale Axis......Page 113
Converting Dates to Text to Add a Decorative Chart Element......Page 115
Kyle Fletcher: Using a Decorative Element in a Chart......Page 116
Using a Long, Meaningful Title to Explain Your Point......Page 119
Highlighting One Column......Page 123
Replacing Columns with Arrows......Page 124
Highlighting a Section of Chart by Adding a Second Series......Page 125
Changing Line Type Midstream......Page 126
Adding an Automatic Trendline to a Chart......Page 128
Showing a Trend of Monthly Sales and Year-to-Date Sales......Page 130
Understanding the Shortcomings of Stacked Column Charts......Page 131
Using a Stacked Column Chart to Compare Current Sales to Prior-Year Sales......Page 132
Shortcomings of Showing Many Trends on a Single Chart......Page 133
Using a Scatter Plot to Show a Trend......Page 134
Next Steps......Page 135
Comparing Entities......Page 136
Using Bar Charts to Illustrate Item Comparisons......Page 137
Adding a Second Series to Show a Time Comparison......Page 138
Subdividing a Bar to Emphasize One Component......Page 140
Showing Component Comparisons......Page 141
Using Pie Charts......Page 143
Switching to a 100% Stacked Column Chart......Page 149
Using a Doughnut Chart to Compare Two Pies......Page 150
Dealing with Data Representation Problems in a Pie Chart......Page 152
Creating a Pie of Pie Chart......Page 157
Creating a Waterfall Chart......Page 159
Next Steps......Page 161
Comparing Two Variables on a Chart......Page 162
Using XY Scatter Charts to Plot Pairs of Data Points......Page 163
Adding a Trendline to a Scatter Chart......Page 164
Adding Labels to a Scatter Chart......Page 165
Joining the Points in a Scatter Chart with Lines......Page 167
Adding a Second Series to an XY Chart......Page 168
Drawing with a Scatter Chart......Page 170
Using Charts to Show Relationships......Page 171
Testing Correlation Using a Scatter Chart......Page 172
Using Paired Bars to Show Relationships......Page 174
Comparing the Relationship Between Discount and Sales......Page 177
Kathy Villella: Comparing Three Variables with a Paired Bar Chart......Page 180
Using Paired Matching Charts......Page 182
MAD Magazine: Creating a Paired Comparison Chart......Page 183
Adding a Third Dimension with a Bubble Chart......Page 185
Using a Frequency Distribution to Categorize Thousands of Points......Page 187
Using Radar Charts to Create Performance Reviews......Page 191
Manoj Sharma: Radar Charts......Page 193
Gene Zelazny: Zelazny Chart......Page 195
Using Surface Charts to Show Contrast......Page 198
Next Steps......Page 200
Line Charts......Page 202
OHLC Charts......Page 203
Obtaining Stock Data to Chart......Page 204
Dealing with Splits Using the Adjusted Close Column......Page 206
Creating a Line Chart to Show Closing Prices......Page 208
Adding Volume as a Column Chart to the Line Chart......Page 209
Producing a High-Low-Close Chart......Page 212
Creating an OHLC Chart......Page 217
Adding Volume to a High-Low-Close Chart......Page 218
Creating Candlestick Charts......Page 224
Adding Volume to a Candlestick Chart......Page 225
Manually Creating a Candlestick Chart with Volume......Page 226
Creating a Candlestick Stock Chart Showing Volume and a Competitor......Page 228
Creating a Live Chart by Using a Web Connection......Page 231
Making Charts Small for Use in Dashboards......Page 234
Next Steps......Page 236
Mixing Two Chart Types on a Single Chart......Page 238
Moving Charts from One Worksheet to Another......Page 239
Using Shapes to Annotate a Chart......Page 240
Making Columns or Bars Float......Page 242
Using a Rogue XY Series to Label the Vertical Axis......Page 245
Converting a Series to Gridlines......Page 246
Showing Several Charts on One Chart by Using a Rogue XY Series......Page 251
Using Multiple XY Series to Create a Trellis Chart......Page 256
Creating Dynamic Charts......Page 260
Using the OFFSET Function to Specify a Range......Page 261
Using VLOOKUP or MATCH to Find a Value in a Table......Page 262
Combining INDEX and MATCH......Page 264
Using Validation Drop-Downs to Create a Dynamic Chart......Page 265
Using Dynamic Ranges in a Chart......Page 268
Creating a Scrolling Chart......Page 271
Modifying the Scrollbar Example to Show the Last 12 Months......Page 273
Thermometer Chart......Page 274
Benchmark Chart......Page 275
Delta Chart......Page 276
Amazing Things People Do with Excel Charts......Page 278
Next Steps......Page 280
What’s New in Excel 2007 Pivot Tables......Page 282
Rules for Preparing Underlying Pivot Data......Page 283
Creating Your First Pivot Chart......Page 284
Changing the Chart Type and Formatting the Chart......Page 286
Adding Additional Series to a Pivot Chart......Page 287
Returning to a Pivot Table for Advanced Operations......Page 288
Filtering a Pivot Table......Page 289
Filtering Using a Report Filter Field......Page 290
Using the Excel 2007 Filters for Axis and Legend Fields......Page 291
Creating a Chart for Every Customer......Page 293
Stratifying Invoice Amounts......Page 294
Next Steps......Page 295
Creating Charts in the Worksheet Cells......Page 296
Customizing Data Bars......Page 297
Controlling the Size of the Smallest/Largest Bar......Page 299
Showing Data Bars for a Subset of Cells......Page 301
Converting to Monochromatic Data Bars......Page 303
Using Icon Sets to Segregate Data......Page 305
Setting Up an Icon Set......Page 306
Moving Numbers Closer to Icons......Page 307
Creating a Chart Using Conditional Formatting in Worksheet Cells......Page 308
Creating a Chart Using the REPT Function......Page 311
Creating a Chart Using Scrollbar Controls......Page 312
Creating a Stem-and-Leaf Plot with X’s as the Leaves......Page 316
Creating a Stem-and-Leaf Plot with Digits as the Leaves Using a Long Formula......Page 318
Creating a Stem-and-Leaf Plot with Digits as the Leaves Using Sorting and Formulas......Page 319
Next Steps......Page 321
Plotting Data Geographically......Page 322
Building a Map in Excel......Page 323
Using a Chart on a Map......Page 327
Using Other Map Styles to Illustrate Data......Page 329
Mapping Your Customers......Page 330
Next Steps......Page 331
Understanding SmartArt Graphics and Shapes......Page 332
Using SmartArt......Page 333
Elements Common Across Most SmartArt......Page 334
A Tour of the SmartArt Categories......Page 335
Inserting SmartArt......Page 336
Micromanaging SmartArt Elements......Page 339
Controlling SmartArt Shapes from the Text Pane......Page 341
Adding Images to SmartArt......Page 343
Special Considerations for Organization Charts......Page 344
Using Limited SmartArt......Page 347
Choosing the Right Layout for Your Message......Page 348
Illustrating Growth by Using an Upward Arrow......Page 349
Showing a Company’s Relationship to External Entities by Using a Diverging Radial Diagram......Page 350
Adjusting Venn Diagrams to Show Relationships......Page 351
Understanding Labeled Hierarchy Charts......Page 352
Using Other SmartArt Layouts......Page 353
Using Shapes to Display Cell Contents......Page 354
Using the Freeform Shape to Create a Custom Shape......Page 356
Using WordArt for Interesting Titles and Headlines......Page 357
Converting SmartArt to Shapes to Allow Dynamic Diagrams......Page 358
Next Steps......Page 361
Presenting Excel Charts in PowerPoint or Word......Page 362
Copying a Chart as a Live Chart Linked to the Original Workbook......Page 364
Copying a Chart as a Live Chart Linked to a Copy of the Original Workbook......Page 365
Copying a Chart as a Picture......Page 366
Pasting a Chart as a Linked Object......Page 367
Creating a Chart in PowerPoint and Copying Data from Excel......Page 368
Using VBA to Export Charts as Images......Page 370
Next Steps......Page 371
Introducing VBA......Page 372
The Visual Basic Editor......Page 373
Visual Basic Tools......Page 374
The Macro Recorder......Page 375
Writing Code to Handle a Data Range of Any Size......Page 376
Using Super-Variables: Object Variables......Page 378
Adding Comments to Code......Page 379
Referencing Charts and Chart Objects in VBA Code......Page 380
Specifying the Size and Location of a Chart......Page 381
Later Referring to a Specific Chart......Page 382
Specifying a Built-in Chart Type......Page 384
Specifying a Template Chart Type......Page 387
Changing a Chart’s Layout or Style......Page 388
Using SetElement to Emulate Changes on the Layout Ribbon......Page 390
Using the Format Method to Access New Formatting Options......Page 395
Automating Changes in the Format Series Dialog......Page 412
Controlling Gap Width and Series Separation in Column and Bar Charts......Page 413
Moving a Series to a Secondary Axis......Page 415
Spinning and Exploding Round Charts......Page 416
Controlling the Bar of Pie and Pie of Pie Charts......Page 418
Setting the Bubble Size......Page 423
Controlling Radar and Surface Charts......Page 424
Using the Watch Window to Discover Object Settings......Page 425
Using the Watch Window to Learn Rotation Settings......Page 428
Creating a Dynamic Chart in a UserForm......Page 429
Creating Pivot Charts......Page 431
Printing a Chart for Each Customer......Page 433
Next Steps......Page 436
Lying with Perspective......Page 438
Lying with Shrinking Charts......Page 440
Lying Because Excel Won’t Cooperate......Page 441
Lying by Obscuring the Data......Page 442
Deliberately Using Charts to Lie......Page 443
Next Steps......Page 445
Gene Zelazny: The Guru of Business Charting......Page 446
PowerFrameworks.com......Page 447
Books by Edward Tufte......Page 448
Interactive Training......Page 449
Visual Design Stores......Page 450
Charting Utilities and Products......Page 451
A......Page 454
C......Page 455
D......Page 459
F......Page 461
H......Page 463
L......Page 464
M......Page 465
P......Page 466
Q–R......Page 468
S......Page 469
T......Page 472
V......Page 474
X......Page 475
Z......Page 476