Excel® 2007 Charts

Excel® 2007 Charts

by John Walkenbach
Released October 2007
Publisher(s): Wiley
ISBN: 9780470044001

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Excel, the top number-crunching tool, now offers a vastly improved charting function to help you give those numbers dimension and relativity. John Walkenbach, a.k.a. Mr. Spreadsheet, clearly explains all these charting features and shows you how to choose the right chart for your needs. You’ll learn to modify data within the chart, deal with missing data, format your chart, use trend lines, construct “impossible” charts, create charts from pivot tables, dress them up with graphics, and more.

These links have been provided by the publisher.

Table of contents

  1. Copyright
  2. About the Author
  3. Acknowledgments
  4. Introduction
    1. What You Should Know
    2. What You Should Have
    3. Conventions Used in This Book
      1. Ribbon Commands
      2. Formula Listings
      3. VBA Code Listings
      4. Key Names
      5. Functions, Procedures, and Named Ranges
      6. Mouse Conventions
      7. What the Icons Mean
    4. How This Book Is Organized
    5. How to Use This Book
    6. About the Power Utility Pak Offer
    7. Reach Out
  5. I. Chart Basics
    1. 1. Introducing Excel Charts
      1. 1.1. What Is a Chart?
      2. 1.2. How Excel Handles Charts
        1. 1.2.1. Embedded Charts
        2. 1.2.2. Chart Sheets
      3. 1.3. Parts of a Chart
      4. 1.4. Basic Steps for Creating a Chart
        1. 1.4.1. Creating the Chart
        2. 1.4.2. Switching the Row and Column Orientation
        3. 1.4.3. Changing the Chart Type
        4. 1.4.4. Applying a Chart Layout
        5. 1.4.5. Applying a Chart Style
        6. 1.4.6. Adding and Deleting Chart Elements
        7. 1.4.7. Formatting Chart Elements
      5. 1.5. Working with Charts
        1. 1.5.1. Moving and Resizing a Chart
        2. 1.5.2. Converting an Embedded Chart to a Chart Sheet
        3. 1.5.3. Copying a Chart
        4. 1.5.4. Deleting a Chart
        5. 1.5.5. Adding Chart Elements
        6. 1.5.6. Moving and Deleting Chart Elements
        7. 1.5.7. Formatting Chart Elements
        8. 1.5.8. Copying a Chart's Formatting
        9. 1.5.9. Renaming a Chart
        10. 1.5.10. Printing Charts
    2. 2. Understanding Chart Types
      1. 2.1. Conveying a Message with a Chart
      2. 2.2. Choosing a Chart Type
      3. 2.3. Excel's Chart Types
        1. 2.3.1. Column Charts
        2. 2.3.2. Bar Charts
        3. 2.3.3. Line Charts
        4. 2.3.4. Pie Charts
        5. 2.3.5. Scatter Charts
        6. 2.3.6. Area Charts
        7. 2.3.7. Doughnut Charts
        8. 2.3.8. Radar Charts
        9. 2.3.9. Surface Charts
        10. 2.3.10. Bubble Charts
        11. 2.3.11. Stock Charts
      4. 2.4. Creating Combination Charts
      5. 2.5. Creating and Using Chart Templates
    3. 3. Working with Chart Series
      1. 3.1. Specifying the Data for Your Chart
      2. 3.2. Adding a New Series to a Chart
        1. 3.2.1. Adding a New Series by Copying a Range
        2. 3.2.2. Adding a New Series by Extending the Range Highlight
        3. 3.2.3. Adding a New Series Using the Select Source Data Dialog Box
        4. 3.2.4. Adding a New Series by Typing a New SERIES Formula
      3. 3.3. Deleting a Chart Series
      4. 3.4. Modifying the Data Range for a Chart Series
        1. 3.4.1. Using Range Highlighting to Change Series Data
        2. 3.4.2. Using the Select Data Source Dialog Box to Change Series Data
        3. 3.4.3. Editing the SERIES Formula to Change Series Data
      5. 3.5. Understanding Series Names
        1. 3.5.1. Changing a Series Name
        2. 3.5.2. Deleting a Series Name
      6. 3.6. Adjusting the Series Plot Order
      7. 3.7. Charting a Noncontiguous Range
      8. 3.8. Using Series on Different Sheets
      9. 3.9. Handling Missing Data
      10. 3.10. Controlling a Data Series by Hiding Data
      11. 3.11. Unlinking a Chart Series from Its Data Range
        1. 3.11.1. Converting a Chart to a Picture
        2. 3.11.2. Converting Range Reference to Arrays
      12. 3.12. Working with Multiple Axes
    4. 4. Formatting and Customizing Charts
      1. 4.1. Chart Formatting Overview
        1. 4.1.1. Selecting Chart Elements
        2. 4.1.2. Common Chart Elements
        3. 4.1.3. User Interface Choices for Formatting
      2. 4.2. Adjusting Fills and Borders: General Procedures
        1. 4.2.1. About the Fill Tab
        2. 4.2.2. Formatting Borders
      3. 4.3. Formatting Chart Background Elements
        1. 4.3.1. Working with the Chart Area
        2. 4.3.2. Working with the Plot Area
      4. 4.4. Formatting Chart Series
        1. 4.4.1. Basic Series Formatting
        2. 4.4.2. Using Pictures and Graphics for Series Formatting
        3. 4.4.3. Additional Series Options
      5. 4.5. Working with Chart Titles
        1. 4.5.1. Adding Titles to a Chart
        2. 4.5.2. Changing Title Text
        3. 4.5.3. Formatting Title Text
        4. 4.5.4. Linking Title Text to a Cell
      6. 4.6. Working with a Chart's Legend
        1. 4.6.1. Adding or Removing a Legend
        2. 4.6.2. Moving or Resizing a Legend
        3. 4.6.3. Formatting a Legend
        4. 4.6.4. Changing the Legend Text
        5. 4.6.5. Deleting a Legend Entry
      7. 4.7. Working with Chart Axes
        1. 4.7.1. Value Axis versus Category Axis
        2. 4.7.2. Value Axis Scales
        3. 4.7.3. Using Time-Scale Axes
        4. 4.7.4. Creating a Multiline Category Axis
        5. 4.7.5. Adding and Removing Axes
        6. 4.7.6. Axis Number Formats
      8. 4.8. Working with Gridlines
        1. 4.8.1. Adding or Removing Gridlines
      9. 4.9. Working with Data Labels
        1. 4.9.1. Adding or Removing Data Labels
        2. 4.9.2. Editing Data Labels
        3. 4.9.3. Problems and Limitations with Data Labels
      10. 4.10. Working with a Chart Data Table
        1. 4.10.1. Adding and Removing a Data Table
        2. 4.10.2. Problems and Limitations with Data Tables
      11. 4.11. Formatting 3-D Charts
        1. 4.11.1. Rotating 3-D Charts
        2. 4.11.2. Formatting a Surface Chart
    5. 5. Working with Trendlines and Error Bars
      1. 5.1. Working with Trendlines
        1. 5.1.1. Chart Types That Support Trendlines
        2. 5.1.2. Data Appropriate for a Trendline
        3. 5.1.3. Adding a Trendline
        4. 5.1.4. Formatting a Trendline
        5. 5.1.5. Formatting a Trendline Label
      2. 5.2. Linear Trendlines
        1. 5.2.1. Linear Forecasting
        2. 5.2.2. Getting the Trendline Values
      3. 5.3. Nonlinear Trendlines
        1. 5.3.1. Logarithmic Trendline
        2. 5.3.2. Power Trendline
        3. 5.3.3. Exponential Trendline
        4. 5.3.4. Polynomial Trendline
      4. 5.4. Displaying a Moving Average
        1. 5.4.1. Adding a Moving Average Line
        2. 5.4.2. Creating Your Own Moving Average Data Series
      5. 5.5. Using Error Bars in a Chart Series
        1. 5.5.1. Chart Types That Support Error Bars
        2. 5.5.2. Adding Error Bars to a Series
        3. 5.5.3. Using Custom Error Bars
        4. 5.5.4. Connecting Series Points to a Trendline with Error Bars
        5. 5.5.5. Error Bar Alternatives
      6. 5.6. Other Series Enhancements
        1. 5.6.1. Series Lines
        2. 5.6.2. Drop Lines
        3. 5.6.3. High–Low Lines
        4. 5.6.4. Up/Down Bars
    6. 6. Working with Shapes and Other Graphics
      1. 6.1. Using Shapes
        1. 6.1.1. Inserting Shapes
        2. 6.1.2. Adding Text to a Shape
        3. 6.1.3. Formatting Shapes
        4. 6.1.4. Selecting Multiple Objects
        5. 6.1.5. Grouping Objects
        6. 6.1.6. Moving Shapes
        7. 6.1.7. Copying Objects
        8. 6.1.8. Changing the Stack Order of Objects
        9. 6.1.9. Aligning and Spacing Objects
        10. 6.1.10. Changing a Shape to a Different Shape
        11. 6.1.11. Reshaping Shapes
        12. 6.1.12. Changing the Shape Defaults
        13. 6.1.13. Printing Objects
      2. 6.2. Working with SmartArt
        1. 6.2.1. Inserting SmartArt
        2. 6.2.2. Customizing SmartArt
        3. 6.2.3. Changing the Layout
        4. 6.2.4. Changing the Style
        5. 6.2.5. Finding Out More about SmartArt
      3. 6.3. Using WordArt
      4. 6.4. Working with Other Graphic Types
        1. 6.4.1. About Graphics Files
        2. 6.4.2. Using the Clip Art Task Pane
        3. 6.4.3. Inserting Graphics Files
        4. 6.4.4. Copying Graphics by Using the Clipboard
        5. 6.4.5. Displaying a Worksheet Background Image
      5. 6.5. A Gallery of Graphic Examples
        1. 6.5.1. Using Shapes and Pictures with Charts
        2. 6.5.2. Calling Attention to a Cell
        3. 6.5.3. Changing the Look of Cell Comments
        4. 6.5.4. Pasting Pictures of Cells
  6. II. Mastering Charts
    1. 7. Creating Interactive Charts
      1. 7.1. Introducing Interactive Charts
      2. 7.2. Hands–On: Creating a Self–Expanding Chart
        1. 7.2.1. Creating the Chart
        2. 7.2.2. Creating Named Formulas
        3. 7.2.3. Modifying the Series
        4. 7.2.4. Testing the Self–Expanding Chart
        5. 7.2.5. Understanding How the Self–Expanding Chart Works
      3. 7.3. Controlling a Series with a Scroll Bar
        1. 7.3.1. Creating the Chart
        2. 7.3.2. Defining the Names
        3. 7.3.3. Adding the Scroll Bar Control
      4. 7.4. Specifying the Beginning and End Point for a Series
        1. 7.4.1. Creating the Chart
        2. 7.4.2. Defining the Names
        3. 7.4.3. Adding Spinner Controls
      5. 7.5. Specifying the First Point and Number of Points for a Series
        1. 7.5.1. Creating the Chart
        2. 7.5.2. Defining the Names
        3. 7.5.3. Adding the User Interface Elements
      6. 7.6. Plotting the Last n Data Points in a Series
        1. 7.6.1. Creating the Chart
        2. 7.6.2. Defining the Names
      7. 7.7. Plotting Every nth Data Point in a Series
        1. 7.7.1. Using Filtering
        2. 7.7.2. Using Array Formulas
        3. 7.7.3. Creating Named Formulas
      8. 7.8. Using Check Boxes to Select Series to Plot
        1. 7.8.1. Creating the Chart
        2. 7.8.2. Adding the Check Box Controls
        3. 7.8.3. Defining the Names
        4. 7.8.4. Modifying the Chart Series
      9. 7.9. Creating a Very Interactive Chart
        1. 7.9.1. Getting the Data
        2. 7.9.2. Creating the Option Button Controls
        3. 7.9.3. Creating the City Lists
        4. 7.9.4. Creating the Chart's Data Range
        5. 7.9.5. Creating the Chart
    2. 8. Charting Techniques and Tricks
      1. 8.1. Adding Lines and Backgrounds to a Chart
        1. 8.1.1. Adding Horizontal Reference Lines to a Chart
        2. 8.1.2. Adding a Vertical Line to a Chart with an XY Series
        3. 8.1.3. Using Background Columns to Represent a Vertical Line
        4. 8.1.4. Adding Vertical or Horizontal "Bands"
        5. 8.1.5. Creating a Scatter Chart with Colored Quadrants
      2. 8.2. Charts That Use a Single Data Point
        1. 8.2.1. Creating a Thermometer Chart
        2. 8.2.2. Creating a Gauge Chart
      3. 8.3. Using a Dummy Axis
        1. 8.3.1. An Introductory Example
        2. 8.3.2. Labeling an Axis with Nonequal Intervals
      4. 8.4. Column and Bar Chart Variations
        1. 8.4.1. Stacked Column Chart Variations
        2. 8.4.2. Creating a Step Chart
        3. 8.4.3. Varying Column Widths
        4. 8.4.4. Conditional Column Colors
        5. 8.4.5. Creating a Comparative Histogram
        6. 8.4.6. Creating Gantt Charts
        7. 8.4.7. Identifying the Maximum and Minimum Values in a Series
        8. 8.4.8. Shading between Two Series in a Line Chart
      5. 8.5. Creating a Timeline
      6. 8.6. Creating "Impossible" Charts by Stacking and Overlaying
        1. 8.6.1. Stacking Charts
        2. 8.6.2. Overlaying Charts
        3. 8.6.3. Simulating a "Broken" Value Axis
      7. 8.7. Displaying Multiple Charts on a Chart Sheet
    3. 9. In-Cell Charting Techniques
      1. 9.1. Visualizing Data with Conditional Formatting
        1. 9.1.1. Data Bars
        2. 9.1.2. Color Scales
        3. 9.1.3. Icon Sets
      2. 9.2. Plotting Data without a Chart
      3. 9.3. Creating Sparkline Charts
        1. 9.3.1. Sparkline Charts from Text
        2. 9.3.2. Sparkline Charts from a Chart
        3. 9.3.3. Commercial Sparkline Add-Ins
    4. 10. Mathematical and Statistical Charting Techniques
      1. 10.1. Drawing a Circle with an XY Series
      2. 10.2. Connecting Scatter Chart Data Points to the Axes with Error Bars
      3. 10.3. Connecting XY Points to the Origin
      4. 10.4. Creating Frequency Distributions and Histograms
        1. 10.4.1. Using the FREQUENCY Function
        2. 10.4.2. Using the Analysis ToolPak to Create a Frequency Distribution
        3. 10.4.3. Using Adjustable Bins to Create a Histogram
      5. 10.5. Plotting a Normal Curve
      6. 10.6. Plotting Z–Scores with Standard Deviation Bands
      7. 10.7. Calculating the Area under a Curve
      8. 10.8. Creating a Box Plot
      9. 10.9. Plotting Mathematical Functions
        1. 10.9.1. Plotting Functions with One Variable
        2. 10.9.2. Plotting Functions with Two Variables
      10. 10.10. Creating a 3–D Scatter Plot
    5. 11. Using Pivot Charts
      1. 11.1. What Is a Pivot Table?
        1. 11.1.1. A Pivot Table Example
        2. 11.1.2. Data Appropriate for a Pivot Table
      2. 11.2. Creating a Pivot Table
        1. 11.2.1. Specifying the Data
        2. 11.2.2. Specifying the Location for the Pivot Table
        3. 11.2.3. Laying Out the Pivot Table
        4. 11.2.4. Formatting the Pivot Table
        5. 11.2.5. Modifying the Pivot Table
      3. 11.3. Working with Pivot Charts
        1. 11.3.1. Creating a Pivot Chart
        2. 11.3.2. A Pivot Chart Example
      4. 11.4. Pivot Chart Banking Examples
        1. 11.4.1. Question 1
        2. 11.4.2. Question 2
        3. 11.4.3. Question 3
        4. 11.4.4. Question 4
        5. 11.4.5. Question 5
        6. 11.4.6. Question 6
      5. 11.5. More Pivot Chart Examples
        1. 11.5.1. Creating a Quick Frequency Distribution Chart
        2. 11.5.2. Selecting Rows to Plot
        3. 11.5.3. Using Data from Multiple Sheets
    6. 12. Avoiding Common Chart-Making Mistakes
      1. 12.1. Know Your Audience
      2. 12.2. Chart Accuracy
        1. 12.2.1. Plotting Data Out of Context
        2. 12.2.2. Exaggerating Differences or Similarities
        3. 12.2.3. Plotting Percent Change versus Actual Change
        4. 12.2.4. Grouping with Unequal Bin Sizes
      3. 12.3. Problems with Chart Type Selection
        1. 12.3.1. Category versus Value Axis
        2. 12.3.2. Problems with Pie Charts
        3. 12.3.3. Consider Alternative Chart Types
        4. 12.3.4. Problems with Negative Values
        5. 12.3.5. Scatter Charts with the Smoothed Line Option
        6. 12.3.6. Don't Be Tempted by 3-D Charts
      4. 12.4. Chart Complexity
        1. 12.4.1. Just Plain Bad
        2. 12.4.2. Maximizing "Data Ink"
      5. 12.5. Chart Style
        1. 12.5.1. Text and Font Mistakes
      6. 12.6. A Chart Maker's Checklist
    7. 13. Just for Fun
      1. 13.1. Animating Stuff
        1. 13.1.1. Animated Shapes
        2. 13.1.2. Animated Charts
        3. 13.1.3. Doughnut Chart Wheel of Fortune
      2. 13.2. Fun with Trigonometry
        1. 13.2.1. A Simple Sine–versus–Cosine Plot
        2. 13.2.2. Hypocycloid Charts
        3. 13.2.3. Radar Chart Designs
      3. 13.3. Chart Art
        1. 13.3.1. A Mountain Range Chart
        2. 13.3.2. A Bubble Chart Mouse Head.
        3. 13.3.3. A Smile Chart
      4. 13.4. Scatter Chart Drawings
      5. 13.5. An Analog Clock Chart
      6. 13.6. Roll the Dice
      7. 13.7. Horse Race Chart
      8. 13.8. Using Clip Art
  7. III. Mastering Charts
    1. 14. VBA Overview and Basic Examples
      1. 14.1. VBA in a Nutshell
      2. 14.2. The Macro Recorder and Charts
      3. 14.3. The Chart Object Model
        1. 14.3.1. Using Object Variables
        2. 14.3.2. Using the With-End With construct
        3. 14.3.3. Finding Out More about the Chart Object Model
      4. 14.4. Simple Chart Macros
        1. 14.4.1. Activating and Deactivating a Chart
        2. 14.4.2. Determining whether a Chart Is Activated
        3. 14.4.3. Determining whether the Active Chart Is Embedded
        4. 14.4.4. Deleting from the ChartObjects or Charts Collection
      5. 14.5. Counting and Looping through Charts
        1. 14.5.1. Counting Chart Sheets
        2. 14.5.2. Counting Embedded Charts
        3. 14.5.3. Looping through All Charts
        4. 14.5.4. Changing the Location of All Charts
        5. 14.5.5. Printing All Embedded Charts
      6. 14.6. Setting Axis Values
      7. 14.7. Sizing and Aligning Charts
    2. 15. Advanced VBA Examples
      1. 15.1. Creating Charts with VBA
        1. 15.1.1. Creating an Embedded Chart
        2. 15.1.2. Creating a Chart on a Chart Sheet
      2. 15.2. Using VBA to Apply Chart Formatting
        1. 15.2.1. Formatting a Chart
        2. 15.2.2. More Chart Formatting Examples
      3. 15.3. Applying Data Labels
        1. 15.3.1. A Basic Data Label Macro
        2. 15.3.2. Applying Linked Data Labels
        3. 15.3.3. Prompting for a Range
      4. 15.4. Exporting Charts as GIF Files
      5. 15.5. Creating a Scrolling Chart
      6. 15.6. Creating a Word Cloud
      7. 15.7. Determining the Ranges Used by a Chart
      8. 15.8. Event Procedure Examples
        1. 15.8.1. Worksheet_Change Event Procedures
        2. 15.8.2. Selection_Change Event Procedures
    3. 16. Working with Colors
      1. 16.1. About Excel 2007 Colors
      2. 16.2. Specifying Colors
        1. 16.2.1. The RGB Color System
        2. 16.2.2. The HSL Color System
        3. 16.2.3. Converting Colors
      3. 16.3. Understanding Grayscale
        1. 16.3.1. Converting Colors to Gray
        2. 16.3.2. Viewing Charts as Grayscale
      4. 16.4. Experimenting with Colors
      5. 16.5. Understanding Document Themes
        1. 16.5.1. About Document Themes
        2. 16.5.2. Understanding Document Theme Colors
        3. 16.5.3. Displaying All Theme Colors
      6. 16.6. Working with Shape Objects
        1. 16.6.1. A Shape's Background Color
        2. 16.6.2. Using Other Fill Types with a Shape
        3. 16.6.3. Finding Out More about Shapes
      7. 16.7. Modifying Chart Colors
  8. IV. Appendixes
    1. A. What's on the CD–ROM?
      1. System Requirements
      2. Using the CD
      3. What's on the CD
        1. Sample Files for Excel 2007 Charts
        2. Chapter 01
        3. Chapter 02
        4. Chapter 04
        5. Chapter 05
        6. Chapter 06
        7. Chapter 07
        8. Chapter 08
        9. Chapter 09
        10. Chapter 10
        11. Chapter 11
        12. Chapter 12
        13. Chapter 13
        14. Chapter 14
        15. Chapter 15
        16. Chapter 16
        17. eBook version of Excel 2007 Charts
        18. Applications
      4. Troubleshooting
      5. Customer Care
    2. B. Other Charting Resources
      1. Excel's Help System
      2. Microsoft Technical Support
        1. Support Options
        2. Microsoft Knowledge Base
        3. Microsoft Excel Home Page
        4. Microsoft Office Home Page
      3. Internet Newsgroups
        1. Accessing Newsgroups by Using a Newsreader
        2. Accessing Newsgroups by Using a Web Browser
        3. Searching Newsgroups
      4. Internet Web Sites
        1. The Spreadsheet Page
        2. Daily Dose of Excel
        3. Jon Peltier's Excel Page
        4. Excel User
        5. Tushar Mehta Consulting
        6. Pearson Software Consulting
        7. Stephen Bullen's Excel Page
        8. David McRitchie's Excel Pages
        9. Mr. Excel
        10. Junk Charts
      5. Excel Charting Add–Ins and Enhancements
        1. The Chart Assistant
        2. Crystal Xcelsius
        3. Grab–It
        4. MicroCharts
        5. Power Utility Pak
        6. SparkMaker
        7. Speedometer Chart Creator
        8. XY Chart Labeler
  9. Wiley Publishing, Inc. End-User License Agreement

Product information

  • Title: Excel® 2007 Charts
  • Author(s): John Walkenbach
  • Release date: October 2007
  • Publisher(s): Wiley
  • ISBN: 9780470044001