Bill Jelen is MrExcel! He is principal behind the leading Excel website, MrExcel.com. He honed his Excel wizardry during his 12-year tenure as a financial analyst for a fastgrowing public computer firm. Armed with only a spreadsheet, he learned how to turn thousands of rows of transactional data into meaningful summaries in record time. He is an accomplished author of books on Excel and is a regular guest on The Lab on TechTV Canada. You can find Bill at your local accounting group chapter meeting entertaining audiences with his humorous and informative Power Excel seminar. His website hosts more than 12 million page views annually.
Introduction | p. 1 |
Choosing the Right Chart Type | p. 2 |
Using Excel as Your Charting Canvas | p. 3 |
This Book's Objectives | p. 4 |
A Note About Bugs | p. 5 |
Special Elements in This Book | p. 5 |
Next Steps | p. 6 |
Introducing Charts in Excel 2007 | p. 7 |
What's New in Excel 2007 Charts | p. 7 |
New Charting Tools and Menus | p. 8 |
Using the Insert Tab to Select a Chart Type | p. 9 |
Using the Expand Icon to Access a Gallery of All Chart Types | p. 10 |
Understanding the Chart Thumbnail Icons | p. 10 |
Using Gallery Controls | p. 13 |
Creating a Chart | p. 14 |
Selecting Contiguous Data to Chart | p. 14 |
Selecting Noncontiguous Data to Chart | p. 15 |
Creating a Chart by Using the Insert Ribbon Icons | p. 15 |
Creating a Chart with One Keystroke | p. 17 |
Working with Charts | p. 17 |
Moving a Chart Within the Current Worksheet | p. 17 |
Locating a Chart at the Top of Your Dataset | p. 19 |
Reversing the Series and Categories of a Chart | p. 20 |
Changing the Data Sequence by Using Select Data | p. 21 |
Leaving the Top-Left Cell Blank | p. 23 |
Moving a Chart to a Different Sheet | p. 24 |
Customizing a Chart by Using the Design Ribbon | p. 25 |
Choosing a Chart Layout | p. 25 |
Choosing a Color Scheme | p. 26 |
Modifying a Color Scheme by Changing the Theme | p. 27 |
Creating Your Own Theme | p. 28 |
Choosing Effects for a Custom Theme from an Existing Theme | p. 29 |
Understanding RGB Color Codes | p. 30 |
Converting from Hexadecimal to RGB | p. 30 |
Finding Complementary Colors | p. 31 |
Specifying a Theme's Colors | p. 32 |
Specifying a Theme's Fonts | p. 33 |
Saving a Custom Theme | p. 34 |
Using a Custom Theme on a New Document | p. 35 |
Sharing a Theme with Others | p. 35 |
Next Steps | p. 35 |
Customizing Charts | p. 37 |
Accessing Element Formatting Tools | p. 37 |
Identifying Chart Elements | p. 38 |
Chart Labels and Axis | p. 38 |
Special Elements in a 3-D Chart | p. 40 |
Analysis Elements | p. 40 |
Formatting Chart Elements | p. 41 |
Formatting a Chart Title | p. 42 |
Formatting an Axis Title | p. 44 |
Formatting a Legend | p. 45 |
Adding Data Labels to a Chart | p. 48 |
Adding a Data Table to a Chart | p. 50 |
Formatting Axes | p. 51 |
Using a Date-Based Axis to Represent Time | p. 56 |
Displaying and Formatting Gridlines | p. 59 |
Formatting the Plot Area | p. 61 |
Creating a Custom Gradient | p. 63 |
Formatting the Chart Walls and Floor of a 3-D Chart | p. 65 |
Controlling 3-D Rotation in a 3-D Chart | p. 66 |
Forecasting with Trendlines | p. 68 |
Adding Drop Lines to a Line or Area Chart | p. 71 |
Adding Up/Down Bars to a Line Chart | p. 72 |
Showing Acceptable Tolerances by Using Error Bars | p. 72 |
Formatting a Series | p. 73 |
Formatting a Single Data Point | p. 74 |
Using the Format Ribbon | p. 74 |
Converting Text to WordArt | p. 74 |
Using the Shape Styles Gallery | p. 74 |
Using the Shape Fill and Shape Effects | p. 75 |
Using Preset Shape Effects | p. 76 |
Replacing Data Markers with Clip Art or Shapes | p. 77 |
Using Clip Art as a Data Marker | p. 77 |
Using a Shape in Place of a Data Marker | p. 78 |
Creating a Chart Template | p. 79 |
Next Steps | p. 79 |
Creating Charts That Show Trends | p. 81 |
Choosing a Chart Type | p. 81 |
Understanding a Date-Based Axis Versus a Category-Based Axis | p. 84 |
Accurately Representing Data Using a Time-Based Axis | p. 84 |
Converting Text Dates to Dates | p. 86 |
Comparing Date Systems | p. 88 |
Dates Not Recognized as Dates: Numeric Years | p. 92 |
Dates Not Recognized as Dates: Dates Before 1900 | p. 93 |
Using a Workaround to Display a Time-Scale Axis | p. 98 |
Converting Dates to Text to Add a Decorative Chart Element | p. 100 |
Kyle Fletcher: Using a Decorative Element in a Chart | p. 101 |
Using a Chart to Communicate Effectively | p. 104 |
Using a Long, Meaningful Title to Explain Your Point | p. 104 |
Highlighting One Column | p. 108 |
Replacing Columns with Arrows | p. 109 |
Highlighting a Section of Chart by Adding a Second Series | p. 110 |
Changing Line Type Midstream | p. 111 |
Adding an Automatic Trendline to a Chart | p. 113 |
Showing a Trend of Monthly Sales and Year-to-Date Sales | p. 115 |
Understanding the Shortcomings of Stacked Column Charts | p. 116 |
Using a Stacked Column Chart to Compare Current Sales to Prior-Year Sales | p. 117 |
Shortcomings of Showing Many Trends on a Single Chart | p. 118 |
Using a Scatter Plot to Show a Trend | p. 119 |
Next Steps | p. 120 |
Creating Charts That Show Differences | p. 121 |
Comparing Entities | p. 121 |
Using Bar Charts to Illustrate Item Comparisons | p. 122 |
Adding a Second Series to Show a Time Comparison | p. 123 |
Subdividing a Bar to Emphasize One Component | p. 125 |
Showing Component Comparisons | p. 126 |
Using Pie Charts | p. 128 |
Switching to a 100% Stacked Column Chart | p. 134 |
Using a Doughnut Chart to Compare Two Pies | p. 135 |
Dealing with Data Representation Problems in a Pie Chart | p. 137 |
Creating a Pie of Pie Chart | p. 142 |
Using a Waterfall Chart to Tell the Story of Component Decomposition | p. 144 |
Creating a Waterfall Chart | p. 144 |
Next Steps | p. 146 |
Creating Charts That Show Relationships | p. 147 |
Comparing Two Variables on a Chart | p. 147 |
Using XY Scatter Charts to Plot Pairs of Data Points | p. 148 |
Adding a Trendline to a Scatter Chart | p. 149 |
Adding Labels to a Scatter Chart | p. 150 |
Joining the Points in a Scatter Chart with Lines | p. 152 |
Adding a Second Series to an XY Chart | p. 153 |
Drawing with a Scatter Chart | p. 155 |
Using Charts to Show Relationships | p. 156 |
Testing Correlation Using a Scatter Chart | p. 157 |
Using Paired Bars to Show Relationships | p. 159 |
Comparing the Relationship Between Discount and Sales | p. 162 |
Kathy Villella: Comparing Three Variables with a Paired Bar Chart | p. 165 |
Using Paired Matching Charts | p. 167 |
MAD Magazine: Creating a Paired Comparison Chart | p. 168 |
Adding a Third Dimension with a Bubble Chart | p. 170 |
Using a Frequency Distribution to Categorize Thousands of Points | p. 172 |
Using Radar Charts to Create Performance Reviews | p. 176 |
Manoj Sharma: Radar Charts | p. 178 |
A Chart from Gene Zelazny | p. 180 |
Gene Zelazny: Zelazny Chart | p. 180 |
Using Surface Charts to Show Contrast | p. 183 |
Using the Depth Axis | p. 185 |
Controlling a Surface Chart Through 3-D Rotation | p. 185 |
Next Steps | p. 185 |
Creating Stock Analysis Charts | p. 187 |
Overview of Stock Charts | p. 187 |
Line Charts | p. 187 |
OHLC Charts | p. 188 |
Candlestick Charts | p. 189 |
Obtaining Stock Data to Chart | p. 189 |
Rearranging Columns in the Downloaded Data | p. 191 |
Dealing with Splits Using the Adjusted Close Column | p. 191 |
Creating a Line Chart to Show Closing Prices | p. 193 |
Adding Volume as a Column Chart to the Line Chart | p. 194 |
Creating OHLC Charts | p. 197 |
Producing a High-Low-Close Chart | p. 197 |
Creating an OHLC Chart | p. 202 |
Adding Volume to a High-Low-Close Chart | p. 203 |
Creating Candlestick Charts | p. 209 |
Changing Colors in a Candlestick Chart | p. 210 |
Adding Volume to a Candlestick Chart | p. 210 |
Manually Creating a Candlestick Chart with Volume | p. 211 |
Creating a Candlestick Stock Chart Showing Volume and a Competitor | p. 213 |
Creating a Live Chart by Using a Web Connection | p. 216 |
Making Charts Small for Use in Dashboards | p. 219 |
Next Steps | p. 221 |
Advanced Chart Techniques | p. 223 |
A Tool Chest of Advanced Charting Techniques | p. 223 |
Mixing Two Chart Types on a Single Chart | p. 223 |
Moving Charts from One Worksheet to Another | p. 224 |
Using Shapes to Annotate a Chart | p. 225 |
Making Columns or Bars Float | p. 227 |
Using a Rogue XY Series to Label the Vertical Axis | p. 230 |
Converting a Series to Gridlines | p. 231 |
Showing Several Charts on One Chart by Using a Rogue XY Series | p. 236 |
Using Multiple XY Series to Create a Trellis Chart | p. 241 |
Creating Dynamic Charts | p. 245 |
Using the OFFSET Function to Specify a Range | p. 246 |
Using VLOOKUP or MATCH to Find a Value in a Table | p. 247 |
Combining INDEX and MATCH | p. 249 |
Using Validation Drop-Downs to Create a Dynamic Chart | p. 250 |
Using Dynamic Ranges in a Chart | p. 253 |
Creating a Scrolling Chart | p. 256 |
Modifying the Scrollbar Example to Show the Last 12 Months | p. 258 |
Creating Advanced Charts | p. 259 |
Thermometer Chart | p. 259 |
Benchmark Chart | p. 260 |
Delta Chart | p. 261 |
Amazing Things People Do with Excel Charts | p. 263 |
Next Steps | p. 265 |
Creating and Using Pivot Charts | p. 267 |
Creating Your First Pivot Chart | p. 267 |
What's New in Excel 2007 Pivot Tables | p. 267 |
Deciding Which Comes First: The Table or the Chart | p. 268 |
Rules for Preparing Underlying Pivot Data | p. 268 |
Creating Your First Pivot Chart | p. 269 |
Changing the Chart Type and Formatting the Chart | p. 271 |
Adding Additional Series to a Pivot Chart | p. 272 |
Returning to a Pivot Table for Advanced Operations | p. 273 |
Filtering a Pivot Table | p. 274 |
Filtering Using a Report Filter Field | p. 275 |
Using the Excel 2007 Filters for Axis and Legend Fields | p. 276 |
Creating a Chart for Every Customer | p. 278 |
Stratifying Invoice Amounts | p. 279 |
Next Steps | p. 280 |
Presenting Data Visually Without Charts | p. 281 |
Creating Charts in the Worksheet Cells | p. 281 |
Using Data Bars to Create In-Cell Bar Charts | p. 282 |
Customizing Data Bars | p. 282 |
Controlling the Size of the Smallest/Largest Bar | p. 284 |
Showing Data Bars for a Subset of Cells | p. 286 |
Using Color Scales to Highlight Extremes | p. 288 |
Converting to Monochromatic Data Bars | p. 288 |
Troubleshooting Color Scales | p. 290 |
Using Icon Sets to Segregate Data | p. 290 |
Setting Up an icon Set | p. 291 |
Moving Numbers Closer to Icons | p. 292 |
Reversing the Sequence of Icons | p. 293 |
Creating a Chart Using Conditional Formatting in Worhsheet Cells | p. 293 |
Creating a Chart Using the REPT Function | p. 296 |
Creating a Chart Using Scrollbar Controls | p. 297 |
Creating Stem-and-Leaf Plots | p. 301 |
Creating a Stem-and-Leaf Plot with X's as the Leaves | p. 301 |
Creating a Stem-and-Leaf Plot with Digits as the Leaves Using a Long Formula | p. 303 |
Creating a Stem-and-Leaf Plot with Digits as the Leaves Using Sorting and Formulas | p. 304 |
Next Steps | p. 306 |
Presenting Your Excel Data on a Map Using Microsoft MapPoint | p. 307 |
Plotting Data Geographically | p. 307 |
Building a Map in Excel | p. 308 |
Using a Chart on a Map | p. 312 |
Using Other Map Styles to Illustrate Data | p. 314 |
Mapping Your Customers | p. 315 |
Next Steps | p. 316 |
Using SmartArt Graphics and Shapes | p. 317 |
Understanding SmartArt Graphics and Shapes | p. 317 |
Using SmartArt | p. 318 |
Elements Common Across Most SmartArt | p. 319 |
A Tour of the SmartArt Categories | p. 320 |
Inserting SmartArt | p. 321 |
Micromanaging SmartArt Elements | p. 324 |
Controlling SmartArt Shapes from the Text Pane | p. 326 |
Adding Images to SmartArt | p. 328 |
Special Considerations for Organization Charts | p. 329 |
Using Limited SmartArt | p. 332 |
Choosing the Right Layout for Your Message | p. 333 |
Exploring Business Charts That Use SmartArt Graphics | p. 334 |
Illustrating a Pro/Con Decision by Using a Balance Chart | p. 334 |
Illustrating Growth by Using an Upward Arrow | p. 334 |
Showing an Iterative Process by Using a Basic Cycle Layout | p. 335 |
Showing a Company's Relationship to External Entities by Using a Diverging Radial Diagram | p. 335 |
Illustrating Departments Within a Company by Using a Table List Diagram | p. 336 |
Adjusting Venn Diagrams to Show Relationships | p. 336 |
Understanding Labeled Hierarchy Charts | p. 337 |
Using Other SmartArt Layouts | p. 338 |
Using Shapes to Display Cell Contents | p. 339 |
Working with Shapes | p. 341 |
Using the Freeform Shape to Create a Custom Shape | p. 341 |
Using WordArt for Interesting Titles and Headlines | p. 342 |
Converting SmartArt to Shapes to Allow Dynamic Diagrams | p. 343 |
Next Steps | p. 346 |
Exporting Your Charts for Use Outside of Excel | p. 347 |
Presenting Excel Charts in PowerPoint or Word | p. 347 |
Copying a Chart as a Live Chart Linked to the Original Workbook | p. 349 |
Copying a Chart as a Live Chart Linked to a Copy of the Original Workbook | p. 350 |
Copying a Chart as a Picture | p. 351 |
Pasting a Chart as a Linked Object | p. 352 |
Creating a Chart in PowerPoint and Copying Data from Excel | p. 353 |
Presenting Charts on the Web | p. 355 |
Exporting Charts to Graphics | p. 355 |
Using VBA to Export Charts as Images | p. 355 |
Using Snag-It or OneNote to Capture Charts | p. 356 |
Converting to XPS or PDF | p. 356 |
Next Steps | p. 356 |
Using Excel VBA to Create Charts | p. 357 |
Introducing VBA | p. 357 |
Enabling VBA in Your Copy of Excel | p. 358 |
Enabling the Developer Ribbon | p. 358 |
The Visual Basic Editor | p. 358 |
Visual Basic Tools | p. 359 |
The Macro Recorder | p. 360 |
Understanding Object-Oriented Code | p. 361 |
Learning Tricks of the VBA Trade | p. 361 |
Writing Code to Handle a Data Range of Any Size | p. 361 |
Using Super-Variables: Object Variables | p. 363 |
Using With and End With When Referring to an Object | p. 364 |
Continuing a Line | p. 364 |
Adding Comments to Code | p. 364 |
Coding for New Charting Features in Excel 2007 | p. 365 |
Referencing Charts and Chart Objects in VBA Code | p. 365 |
Creating a Chart | p. 366 |
Specifying the Size and Location of a Chart | p. 366 |
Later Referring to a Specific Chart | p. 367 |
Recording Commands from the Layout or Design Ribbons | p. 369 |
Specifying a Built-in Chart Type | p. 369 |
Specifying a Template Chart Type | p. 372 |
Changing a Chart's Layout or Style | p. 373 |
Using SetElement to Emulate Changes on the Layout Ribbon | p. 375 |
Changing a Chart Title Using VBA | p. 380 |
Emulating Changes on the Format Ribbon | p. 380 |
Using the Format Method to Access New Formatting Options | p. 380 |
Automating Changes in the Format Series Dialog | p. 397 |
Controlling Gap Width and Series Separation in Column and Bar Charts | p. 398 |
Moving a Series to a Secondary Axis | p. 400 |
Spinning and Exploding Round Charts | p. 401 |
Controlling the Bar of Pie and Pie of Pie Charts | p. 403 |
Setting the Bubble Size | p. 408 |
Controlling Radar and Surface Charts | p. 409 |
Using the Watch Window to Discover Object Settings | p. 410 |
Using the Watch Window to Learn Rotation Settings | p. 413 |
Exporting a Chart as a Graphic | p. 414 |
Creating a Dynamic Chart in a UserForm | p. 414 |
Creating Pivot Charts | p. 416 |
Printing a Chart for Each Customer | p. 418 |
Next Steps | p. 421 |
Knowing When Someone Is Lying to You with a Chart | p. 423 |
Lying with Perspective | p. 423 |
Lying with Shrinking Charts | p. 425 |
Lying with Scale | p. 426 |
Lying Because Excel Won't Cooperate | p. 426 |
Lying by Obscuring the Data | p. 427 |
Deliberately Using Charts to Lie | p. 428 |
Next Steps | p. 430 |
A Charting References | p. 431 |
Other Charting Resources | p. 431 |
Gene Zelazny: The Guru of Business Charting | p. 431 |
PowerFrameworks.com | p. 432 |
Books | p. 433 |
Websites with Charting Tutorials | p. 434 |
Interactive Training | p. 434 |
Live Training | p. 435 |
Blogs About Charting | p. 435 |
Visual Design Stores | p. 435 |
Professional Chart Designers | p. 436 |
Charting Utilities and Products | p. 436 |
Index | p. 439 |
Table of Contents provided by Ingram. All Rights Reserved. |
The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.
The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.