Create Dynamic Charts in Microsoft® Office Excel® 2007 and Beyond

Create Dynamic Charts in Microsoft® Office Excel® 2007 and Beyond

by Reinhold Scheck
Released December 2008
Publisher(s): Microsoft Press
ISBN: 9780735625440

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

Extend your Excel 2007 skills—and create more-powerful and compelling charts in less time. Guided by an Excel expert, you’ll learn how to turn flat, static charts into dynamic solutions—where you can visualize and manipulate data countless ways with a simple mouse click. Get the hands-on practice and examples you need to produce your own, professional-quality results. No programming required!
Maximize the impact of your ideas and data!

  • Learn how your design decisions affect perception and comprehension

  • Match the right chart type to your communication objective

  • Visualize—then build—your solution using the author’s five-step approach

  • Apply the science of color to make the right things pop

  • Add controls—such as drop-down lists and scroll bars—without coding

  • Use conditional formatting to dynamically highlight and analyze data

  • Unleash your chart-making creativity—and bring numbers to life!


    • CD includes:

  • More than 150 sample, customizable charts for various business scenarios

  • Helpful worksheets and job aids

  • Bonus content and resources

  • Fully searchable eBook

    • For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.

    Table of contents

    1. How to Use this Book
      1. Why Would Anyone Need This Book in the First Place?
      2. Version Restriction: Excel 2007 and beyond
        1. It’s Not All New, but Much of It Is Significantly Different
      3. Objectives—Approaches—Possibilities
        1. What Realistic Goals Can I Set Myself?
        2. How Successful Can I Hope to Be?
        3. What Prior Knowledge Do I Require?
      4. Use of the rS1.Method
      5. Design and Materials
        1. Notations
        2. Examples and Materials
      6. Hardware and Software Requirements
        1. Indexes
      7. Support for This Book
        1. Questions and Comments
    2. 1. Basic Information—Basic Techniques
      1. Excel 2007: New Look—New Structures
        1. Design of the Access Options
        2. Formula Bar
        3. Worksheets
        4. Sheet Tabs and Status Bar
        5. Options
          1. Popular Category
            1. Show Mini Toolbar on selection
            2. Enable Live Preview
            3. Show Developer tab in the Ribbon
            4. ScreenTip Style
            5. When creating new workbooks
          2. Formulas Category
            1. Calculation Options
            2. Working with Formulas
            3. Error Checking
          3. Proofing Category
          4. Save Category
          5. Advanced Category
            1. Editing Options
            2. Cut, Copy and, Paste
            3. Display
            4. Display Options for Selected Workbooks and Worksheets
            5. Formulas
            6. Calculation Options and General Information
          6. Customize Category
          7. Add-Ins Category
          8. Trust Center Category
          9. Resources Category
        6. The Ribbon
        7. Quick Access Toolbar
        8. Using the Keyboard to Access the Ribbon
        9. The Office Button
      2. Overviews and Materials
      3. Basic Concepts and Structures
        1. Chart Types and Data Sources
          1. Chart Type
          2. Data Sources
            1. Chart Data Range
            2. Legend Entries
            3. Axis Labels
        2. Formattable Elements
          1. Chart Area
          2. Plot Area
          3. Data Series
          4. Data Point
          5. Labels
            1. Chart Title
            2. Axis Title
            3. Legend
            4. Data Labels
            5. Data Table
          6. Axes
            1. Vertical and Horizontal Primary Axis
            2. Gridlines
          7. Analysis Elements
            1. High-Low Lines, Drop Lines, Trendlines
            2. Error Bars
            3. Up/Down Bars
      4. Basic Approach
        1. Ribbon Commands
          1. Calling Formatting Dialog Boxes
          2. Access Using the Ribbon Tabs
          3. Access Using the Context Menu
          4. Access Using Keys
    3. 2. New Approaches—Getting Started
      1. Charts Are Presentation Objects
      2. The Basic Model
        1. Structures of the Excel Workbook
          1. Step A
          2. Step B
          3. Step C
          4. Step D
          5. Step E
        2. Conceptualizing from E to A—Implementing from A to E
        3. Problems and Their Solutions
      3. Digression: important Functions and Formulas
        1. The INDEX Function
        2. The VLOOKUP Function
        3. The OFFSET Function
        4. Suggestions for Improvement
      4. Model Structures—A Practical example
        1. Scenario
        2. Structure of the Workbook
        3. Structural Components in Detail
          1. NamesIndex Worksheet
          2. Parameters 1 Worksheet
          3. Data 1 Worksheet
          4. Lists 1 Worksheet
          5. Basis 1 Worksheet
          6. Focus 1 Worksheet
      5. A Solution Emerges
        1. Choosing a Theme
        2. What’s This About—And How Do I Do It?
        3. Is All the Groundwork Done?
        4. Defining the Parameters 1 Worksheet
          1. Setting Up Auxiliary Rows and Columns
          2. Assigning a Range Name
        5. Defining the Data 1 Worksheet
          1. Freezing Panes
          2. Entering Formulas
          3. Defining the Node
          4. The Data 1 Worksheet: A Summary
        6. Defining and Preparing the Lists 1 Worksheet
          1. Defining Names
          2. Entering Formulas
          3. Generating and Using Controls
        7. Defining and Preparing the Basis 1 Worksheet
          1. Formulas of the Variable Chart Basis
          2. A Dynamic Chart Emerges
        8. Defining the Focus 1 Worksheet
          1. Formatting the Chart Area and Plot Area
          2. Formatting the Variable Data Series
          3. Formatting the Primary Vertical Axis
          4. Formatting the Static Data Series and Gridlines
          5. Formatting the Primary Horizontal Axis
      6. Summary
    4. 3. Perceiving, Interpreting, Understanding
      1. Perception and the Laws of Perception
        1. Determining Our Limitations
        2. 100 Million Years
        3. How Does Visual Perception Work?
        4. The Most Important Laws of Perception
          1. The Law of Figure and Background
            1. Examples
            2. Consequences of Designing your Charts
          2. The Law of Simplicity or “Good Figure”
            1. Examples
            2. Consequences of Designing your Charts
          3. The Law of Proximity
            1. Examples
            2. Consequences of Designing Your Charts
        5. The Law of Closure
          1. Example
          2. Consequences of Designing Your Charts
          3. The Law of Continuity
            1. Examples
            2. Consequences of Designing Your Charts
        6. Summary
      2. Which Charts for Which Purpose
        1. Drawbacks of 3-D Charts, Cones, and Pyramids
        2. Essential Types, Their Symbolism and Applications
          1. Columns
            1. Its Symbolism
            2. Clustered Columns
            3. Stacked Columns
            4. Stacked Columns (100 percent)
            5. Variants
            6. Two Examples
          2. Bars
            1. Its symbolism
            2. Clustered Bars
            3. Stacked Bars
            4. Stacked Bars (100 percent)
          3. Lines
            1. Its symbolism
            2. Example 1
            3. Example 2
            4. Example 3
          4. Pie (Circle)
            1. Its Symbolism
            2. Examples 1 and 2
            3. Example 3
            4. Example 4
          5. XY (Scatter)
            1. Its Symbolism
            2. Example 1
            3. Example 2
            4. Example 3
          6. Other Chart Types
            1. Area
            2. Doughnut
            3. Radar
            4. Bubble
            5. Surface
            6. Stock Chart and Mixed Chart Types
    5. 4. Colors, Areas, and Outlines
      1. Colors and Color Systems
        1. How We See
        2. Spectrum and Color Receptors
        3. Calibrating Your Screen
        4. Color Models and Their Use in Excel 2007
      2. Coloring Elements
        1. The Power of Color Effects
        2. General Information About the Approach
        3. Coloring Table Structures
          1. Character Font
          2. Cell Ranges
        4. Coloring Chart Elements
          1. Coloring Areas
            1. Using and Changing Integrated Styles
            2. Dialog Box for Coloring Areas with Solid Fills
            3. Using the Dialog Box for Coloring Areas with Gradient Fills
            4. Putting Color Fills into Practice
            5. Picture and Texture Fill
            6. Marker Fill
          2. Coloring Lines and Outlines
      3. Creating and Changing Lines and Outlines
      4. Shading Areas and Lines
      5. Assigning “Shape Styles”
        1. Combined Basic Formats for Shape, Fill, and Outlines
        2. Shape Fill
        3. Shape Outline
        4. Shape Effects
      6. Size and Properties
    6. 5. Graphical Objects
      1. Object Types and Basic Techniques
        1. Object Types
          1. Drawing Objects
          2. Picture Objects
            1. Charts
            2. Controls
            3. Object Combinations
        2. Using the Practice Material
        3. Creating, Inserting, and Selecting Objects
        4. Moving and Scaling Objects
        5. Determining Positioning Properties
        6. Duplicating Objects
        7. Arranging Objects
          1. Foreground and Background
          2. Grouping Objects
        8. Rotating Objects
        9. Using Drawing Tools and Picture Tools
      2. Drawing Objects and Drawing Tools
        1. Changing the Design and Content of Integrated Shapes
          1. Design Changes Using the Mouse
          2. Changing Shapes into Other Shapes
          3. Other Commands of the Edit Shape Category
        2. Adding Text to Shapes
        3. Text Boxes and Their Properties
        4. Formatting Shapes and Changing Formats
          1. Assigning Formats with Drawing Tools
          2. Combined Formats, Theme Fills, Shape Fills, Shape Outlines
          3. Shape Effects
            1. Presets
            2. Shadow, Glow, Soft Edges
            3. Reflection
            4. Bevel
            5. 3-D Rotation
          4. Assigning Formats Using Dialog Boxes
        5. Lines, Arrows, and Connectors
          1. Differences in Line Types
          2. Formatting Lines and Arrows
          3. Creating and Formatting Connectors
        6. WordArt
          1. Inserting WordArt, Editing, and Formatting Text
          2. Assigning Formats with Drawing Tools
          3. Table Cell as Text Source for WordArt
          4. Transforming WordArt
        7. SmartArt
      3. Picture Objects and Picture Tools
        1. Inserting and Adjusting Pictures
      4. Charts in Combination with Graphical Objects
    7. 6. What, For Whom, How, and With What?
      1. How are Results and Approaches Determined?
        1. From Assigning Tasks to Defining Objectives
          1. Scenario and Assignment of Tasks
          2. Defining Objectives
            1. Cognitive Objectives
            2. Affective Objectives
            3. Pragmatic Objectives
          3. Implementing the Objectives in Our Example
        2. The Six Ws and one H
        3. For Your Own Information or for Management?
        4. Have You Got Something to Say?
      2. Models
        1. Sample Templates
        2. The Excel Template
        3. The Chart Template
      3. The Importance of Looking Good
        1. Which Layout?
          1. Chart Layout for Print Publications
          2. Layout for Presentation Charts
          3. What Does Excel 2007 Offer in Terms of Design Layout?
          4. Screen Setup Commands
        2. Type Area and Layout Grid
        3. Area Design
          1. Area Relationships
        4. Dimensions
        5. Brightness and Colors
        6. Lines and Borders
        7. A Question of Ergonomics
          1. Model A
          2. Model B
      4. Do Your Figures Need More Format?
        1. Appearances Can Be Deceptive
        2. Creating and Assigning Custom Formats
        3. Documenting and Copying Number Formats
        4. Number Formatting in Charts
    8. 7. Elements of Dynamization
      1. Theme and Variations
        1. Purpose and Structure of the Model
          1. Which Analyses Can We Run?
          2. How Is the Workbook Structured?
            1. NamesIndex Worksheet
            2. Lists 1 Worksheet
            3. Data 1 Worksheet
            4. Basis 1 Worksheet
            5. Focus 1 Worksheet
          3. So What’s New in Variant 2?
      2. Variant 1 (Basic Model)
        1. Form Controls
          1. Basics
          2. Using Form Controls
          3. Configuring Form Controls
            1. Controls for Selecting a Period
            2. Control for Selecting an Indicator Category
            3. Controls for Displaying Additional Data Series
          4. Properties of Selected Form Controls
          5. Common Properties of Controls
        2. Formulas
          1. Formulas in the Data 1 Worksheet
          2. Formulas in the Basis 1 Worksheet
          3. Formulas in the Focus 1 Worksheet
      3. Variant 2 (Advanced Model)
        1. How Is the Workbook Structured?
          1. NamesIndex Worksheet
          2. Parameters 1 Worksheet
          3. Lists 1 Worksheet
          4. Data 1 Worksheet
          5. Basis 1 Worksheet
          6. Focus 1 Worksheet
        2. ActiveX Controls
          1. Basics of ActiveX Controls
          2. Recommended Method
          3. Creating ActiveX Controls
          4. Enabling/Disabling Design Mode
          5. Modifying ActiveX Controls
          6. Properties When Using the rS1.Method
          7. Configuring ActiveX Controls
            1. Controls for Selecting Indicator Category and Period
            2. Controls for Displaying Additional Data Series
        3. Formulas
          1. Formulas in the Lists 1 Worksheet
          2. Formulas in the Data 1 Worksheet
          3. Formulas in the Basis 1 Worksheet
          4. Formulas in the Focus 1 Worksheet
        4. Conditional Formatting
          1. Basics
          2. Conditional Formatting: A Real-Life Example
            1. Data 1 Worksheet
            2. Focus 1 Worksheet
    9. 8. Chart Types—Conventional and Exceptional
      1. Key Moves
        1. Model 1 (Standard Model)
        2. Model 2 (Presentation Variant)
      2. Column Charts
        1. Axis Options
          1. Settings of the Primary Vertical Axis
          2. Settings of the Primary Horizontal Axis
          3. Multiline and Multicolumn Axis Labels
            1. Example 1, Two-Line, Irregular Segmentation
            2. Example 2, Three-Line, Regular Segmentation
            3. Example 3, Three-Line, with Calculation Results in the Label
          4. Data Labels
          5. Series Overlaps
          6. Column Chart to Bar Chart
      3. Bar Charts
        1. Charts to Evaluate Orders and Ranks
        2. Mapping High-Lows
        3. Mapping Changes
      4. Line Charts
        1. Mapping the Development of Values
        2. Mapping Connections and Differences
        3. User-Defined Positioning of the Category Axis
        4. Linear Data Series as a Category Alert
        5. The Line as an Interval of Time
        6. Complex Solutions
      5. Mixed Chart Types
      6. Charts with Secondary Axes
        1. Two Value Axes with Unequal Scaling
        2. Using Secondary Axes for Gridline Formatting
      7. Circular Charts
        1. Angles and Pie Explosion
          1. Angles
          2. Point Explosion
        2. Series Split and Second Plot Area
        3. Mapping Parts with Gaps and Transparencies
        4. Rings and Inner Rings, Circles and Semicircles
      8. XY (Scatter) Charts
        1. Point Cloud in a Filter Chart
        2. Grade Distribution in a Scatter Line Chart
        3. Quality and Quantity in a Single-Point Chart
    10. 9. Dynamic Chart Formatting and Other Tricks
      1. Effects with Dynamic Chart Elements
        1. Using a Spin Button to Change the Chart Type
        2. Automatic Highlighting of Limits
        3. Dynamic Multiline Axis Labels
        4. Horizontal Scales
        5. Pointer Elements in Column Charts
      2. Gauges in a Test Model
        1. Functionality and Structure
        2. Creation and Application
          1. The Thermometer Chart
          2. The Measuring Staff Chart
          3. The Line Chart
      3. Additional Lines as Eye-Catcher
        1. Crossed Lines and Ladder Chart
        2. A Shifting Eye-Catcher
        3. A Practical Example
          1. Digression 1: Error Bars
          2. Digression 2: The Excel Camera
          3. Technical Background of the Model
      4. Pseudocharts and Window Charts
        1. Pseudocharts Based on Conditional Formatting
        2. Window Chart
      5. Automatic Sorting of Chart Content
        1. Using Controls for Sorting
        2. Using Formulas for Sorting
    11. 10. Presentation Solutions That Pack a Punch
      1. Sorting? You can do without
        1. The Task and the Problem
        2. Organization of the Source Data
        3. Controls with Text Output
        4. Formulas of the Chart Basis
        5. Specials in the Focus Worksheet
      2. A Little Data; a Lot of Information
      3. A Lot of Data—Compressed Information
        1. Chart of Key Data Over 10 Years
        2. Filtering with the Filter—and Filtering with Controls
          1. Variant 1: The Complex Analysis Module
          2. Variant 2: The Easy-to-Use Report Module
      4. Not That It’s Absolutely Essential
      5. Profile Comparisons on the Radar Chart
    12. 11. Fulfilling Special Requirements
      1. Cumulation with Controls
      2. Bubble Charts for Customer Analysis
      3. XY (Scatter) Charts for Publication, Presentation, and Analysis
        1. Formatting for Publication
        2. Variable Analysis with Scatterplots
        3. Box-and-Whisker Plot (Boxplot)
      4. Costing Scenario
    13. 12. More Than Numbers
      1. Using Character Codes
        1. Using the Character Map Program
        2. Direct Access in Excel
        3. Keyboard Input
      2. Navigation with Hyperlinks
        1. Pictorial Graphics Objects for Scrolling in the Workbook
        2. Setting Up Central Navigation
      3. Printing and PDF Conversion
        1. Questions on Preparing Charts for Publication
        2. Creating PDF Documents
      4. SmartArt
        1. Inserting SmartArt Graphics
        2. Basics and Formatting Options
        3. Changing SmartArt Graphics and Their Elements
        4. Variations
      5. Interaction with Other Programs
        1. Creating Charts in PowerPoint 2007
        2. Exporting Excel Charts to PowerPoint
        3. Exporting Excel Charts as Bitmap Files
      6. Before I Forget
    14. A. List of Sample Files
    15. B. About the Author
    16. C. Choose the Right Book for You
      1. Plain & Simple
      2. Step by Step
      3. Inside Out
      4. Other Titles
    17. Index
    18. About the Author
    19. Copyright

    Product information

    • Title: Create Dynamic Charts in Microsoft® Office Excel® 2007 and Beyond
    • Author(s): Reinhold Scheck
    • Release date: December 2008
    • Publisher(s): Microsoft Press
    • ISBN: 9780735625440