Charts and Graphs for Microsoft Office Excel 2007


After 15 years with no updates to the Excel charting engine, Microsoft has provided a complete rewrite of the chart rendering engine in Excel 2007. However, no amount of soft glow or glass bevel effects will help you communicate your point if you use the wrong chart type. This book helps you choose the right charting type and shows you how to make it look great.

This book shows you how to coax Excel to create many charts you might not have believed were possible. You'll learn ...

See more details below
Other sellers (Paperback)
  • All (13) from $2.96   
  • New (6) from $21.52   
  • Used (7) from $2.96   
Sending request ...


After 15 years with no updates to the Excel charting engine, Microsoft has provided a complete rewrite of the chart rendering engine in Excel 2007. However, no amount of soft glow or glass bevel effects will help you communicate your point if you use the wrong chart type. This book helps you choose the right charting type and shows you how to make it look great.

This book shows you how to coax Excel to create many charts you might not have believed were possible. You'll learn techniques that allow you to ditch the Microsoft defaults and actually create charts that communicate your point. You'll learn why the Excel stock charts are so restrictive and how you can easily turn any line chart into a stock chart-without any limitations. You'll also learn how to add invisible series to make columns float in midair. Learn how to create charts right in Excel cells using the new Excel 2007 data bars-or even the decades-old REPT function!

In no time, this book will have you creating charts that wow your audience and effectively communicate your message. Master effective visual display of data, Choose the right chart type to convey your message, Learn time-saving workarounds, Create charts that most people think you can't create with Excel, Understand what a Radar chart is and when you might use it, Summarize a million rows of data in a single pivot table chart, Present data graphically without charts, Employ SmartArt graphics to show process or relationship charts, Utilize VBA to create charts, Put your data on a map, Export your charts to the web or PowerPoint, Detect chart lies.

Read More Show Less

Product Details

  • ISBN-13: 9780789736109
  • Publisher: Que
  • Publication date: 5/8/2007
  • Series: Business Solutions Series
  • Pages: 461
  • Product dimensions: 6.80 (w) x 9.02 (h) x 0.99 (d)

Meet the Author

Bill Jelen is MrExcel! He is principal behind the leading Excel website, 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.

Read More Show Less

Table of Contents

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

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)