Contents

Introduction: Using Excel 2010 to Create Charts

Choosing the Right Chart Type

Using Excel as Your Charting Canvas

Topics Covered in This Book

This Book’s Objectives

Versions of Excel

Special Elements in This Book

1 Introducing Charts in Excel 2010

What’s New in Excel 2010 Charts

New Charting Tools and Menus

Using the Insert Tab to Select a Chart Type

Using the Expand Icon to Access a Gallery of All Chart Types

Understanding the Chart Thumbnail Icons

Using Gallery Controls

Creating a Chart

Selecting Contiguous Data to Chart

Selecting Noncontiguous Data to Chart

Creating a Chart by Using the Insert Tab Icons

Creating a Chart with One Keystroke

Working with Charts

Moving a Chart Within the Current Worksheet

Reversing the Series and Categories of a Chart

Changing the Data Sequence by Using Select Data

Leave Top-Left Cell Blank

Moving a Chart to a Different Sheet

Customizing a Chart by a Layout and Style on the Design Tab

Choosing a Chart Layout

Choosing a Color Scheme

Modifying a Color Scheme by Changing the Theme

Create Your Own Theme

Choose Effects for a Custom Theme from an Existing Theme

Understanding RGB Color Codes

Converting from Hexadecimal to RGB

Finding Complementary Colors

Specifying a Theme’s Colors

Specify Theme Fonts

Saving a Custom Theme

Using a Custom Theme on a New Document

Sharing a Theme with Others

Next Steps

2 Customizing Charts

Accessing Element Formatting Tools

Identifying Chart Elements

Chart Labels and Axis

Special Elements in a 3-D Chart

Analysis Elements

Formatting Chart Elements

Formatting a Chart Title

Formatting an Axis Title

Formatting a Legend

Adding Data Labels to a Chart

Adding a Data Table to a Chart

Formatting Axes

Formatting the Plot Area

Formatting the Chart Walls and Floor of a 3-D Chart

Controlling 3-D Rotation in a 3-D Chart

Forecasting with Trendlines

Adding Drop Lines to a Line or Area Chart

Adding Up/Down Bars to a Line Chart

Showing Acceptable Tolerances by Using Error Bars

Formatting a Series

Formatting a Single Data Point

Using the Format Tab

Converting Text to WordArt

Using the Shape Styles Gallery

Using the Shape Fill and Shape Effects

Using Preset Shape Effects

Replacing Data Markers with Clip Art or Shapes

Using Clip Art as a Data Marker

Using a Shape in Place of a Data Marker

3 Creating Charts That Show Trends

Choosing a Chart Type

Column Charts for Up to 12 Time Periods

Line Charts for Time Series Beyond 12 Periods

Area Charts to Highlight One Portion of the Line

High-Low-Close Charts for Stock Market Data

Bar Charts for Series with Long Category Labels

Pie Charts Make Horrible Time Comparisons

100 Percent Stacked Bar Chart Instead of Pie Charts

Understanding Date-Based Axis Versus Category-Based Axis in Trend Charts

Converting Text Dates to Dates

Plotting Data by Numeric Year

Using Dates Before 1900

Using a Workaround to Display a Time-Scale Axis

Communicate Effectively with Charts

Using a Long, Meaningful Title to Explain Your Point

Highlighting One Column

Replacing Columns with Arrows

Highlighting a Section of Chart by Adding a Second Series

Changing Line Type Midstream

Adding an Automatic Trendline to a Chart

Showing a Trend of Monthly Sales and Year-to-Date Sales

Understanding the Shortcomings of Stacked Column Charts

Using a Stacked Column Chart to Compare Current Sales to Prior-Year Sales

Shortcomings of Showing Many Trends on a Single Chart

Next Steps

4 Creating Charts That Show Differences

Comparing Entities

Using Bar Charts to Illustrate Item Comparisons

Adding a Second Series to Show a Time Comparison

Subdividing a Bar to Emphasize One Component

Showing Component Comparisons

Using Pie Charts

Switching to a 100 Percent Stacked Column Chart

Using a Doughnut Chart to Compare Two Pies

Dealing with Data Representation Problems in a Pie Chart

Using a Waterfall Chart to Tell the Story of Component Decomposition

Creating a Stacked and Clustered Chart

Next Steps

5 Creating Charts That Show Relationships

Comparing Two Variables on a Chart

Using XY Scatter Charts to Plot Pairs of Data Points

Adding a Trendline to a Scatter Chart

Adding Labels to a Scatter Chart

Joining the Points in a Scatter Chart with Lines

Adding a Second Series to an XY Chart

Drawing with a Scatter Chart

Using Charts to Show Relationships

Testing Correlation Using a Scatter Chart

Using Paired Bars to Show Relationships

Adding a Third Dimension with a Bubble Chart

Using a Frequency Distribution to Categorize Thousands of Points

Using Radar Charts to Create Performance Reviews

Using Surface Charts to Show Contrast

Using the Depth Axis

Controlling a Surface Chart Through 3-D Rotation

Next Steps

6 Creating Stock Analysis Charts

Overview of Stock Charts

Line Charts

OHLC Charts

Candlestick Charts

Obtaining Stock Data to Chart

Rearranging Columns in the Downloaded Data

Dealing with Splits Using the Adjusted Close Column

Creating a Line Chart to Show Closing Prices

Adding Volume as a Column Chart to the Line Chart

Creating OHLC Charts

Producing a High-Low-Close Chart

Creating an OHLC Chart

Adding Volume to a High-Low-Close Chart

Creating Candlestick Charts

Changing Colors in a Candlestick Chart

Adding Volume to a Candlestick Chart

Manually Creating a Candlestick Chart with Volume

Creating a Live Chart by Using a Web Connection

Creating a Bar Chart with Formulas to Extract Imported Data

Making Charts Small for Use in Dashboards

Next Steps

7 Advanced Chart Techniques

A Tool Chest of Advanced Charting Techniques

Mixing Two Chart Types on a Single Chart

Mixing Stacked Columns with Clustered Columns

Moving Charts from One Worksheet to Another

Using Shapes to Annotate a Chart

Making Columns or Bars Float

Using a Rogue XY Series to Label the Vertical Axis

Showing Several Charts on One Chart by Using a Rogue XY Series

Creating Dynamic Charts

Using the OFFSET Function to Specify a Range

Using VLOOKUP or MATCH to Find a Value in a Table

Combining INDEX and MATCH

Using Validation Drop-Downs to Create a Dynamic Chart

Using Dynamic Ranges in a Chart

Creating a Scrolling Chart

Modifying the Scrollbar Example to Show the Last 12 Months

Creating Advanced Charts

Thermometer Chart

Benchmark Chart

Delta Chart

Amazing Things People Do with Excel Charts

Next Steps

8 Creating and Using Pivot Charts

Creating Your First Pivot Chart

New in Excel 2010 Pivot Tables

Deciding Which Comes First: The Table or the Chart

Rules for Preparing Underlying Pivot Data

Creating Your First Pivot Chart

Changing the Chart Type and Formatting the Chart

Adding Additional Series to a Pivot Chart

Returning to a Pivot Table for Advanced Operations

Filtering a Pivot Table

Filtering Using Slicers

Using the Excel 2010 Filters for Axis and Legend Fields

Creating a Chart for Every Customer

Next Steps

9 Using Sparklines, Data Visualizations, and Other Nonchart Methods

Fitting a Chart Into the Size of a Cell with Sparklines

Creating a Group of SparkLines

Built-In Choices for Customizing Sparklines

Controlling Axis Values for Sparklines

Setting Up Win/Loss Sparklines

Showing Detail by Enlarging the Sparkline

Labeling a Sparkline

Using Data Bars to Create In-Cell Bar Charts

Creating Data Bars

Customizing Data Bars

Showing Data Bars for a Subset of Cells

Using Color Scales to Highlight Extremes

Customizing Color Scales

Using Icon Sets to Segregate Data

Setting Up an Icon Set

Moving Numbers Closer to Icons

Creating a Chart Using Conditional Formatting in Worksheet Cells

Creating a Chart Using the REPT Function

Next Steps

10 Presenting Excel Data on a Map Using Microsoft MapPoint

Plotting Data Geographically

Building a Map in Excel

Showing Numeric Data on a Map

Using Other Map Styles to Illustrate Data

Next Steps

11 Using SmartArt Graphics and Shapes

Using SmartArt

Elements Common Across Most SmartArt

A Tour of the SmartArt Categories

Inserting SmartArt

Micromanaging SmartArt Elements

Changing Text Formatting in One Element

Controlling SmartArt Shapes from the Text Pane

Adding Images to SmartArt

Special Considerations for Organization Charts

Choosing the Right Layout for Your Message

Exploring Business Charts That Use SmartArt Graphics

Illustrating a Pro/Con Decision by Using a Balance Chart

Illustrating Growth by Using an Upward Arrow

Showing an Iterative Process by Using a Basic Cycle Layout

Showing a Company’s Relationship to External Entities by Using a Diverging Radial Diagram

Illustrating Departments Within a Company by Using a Table List Diagram

Adjusting Venn Diagrams to Show Relationships

Understanding Labeled Hierarchy Charts

Using Other SmartArt Layouts

Using Shapes to Display Cell Contents

Working with Shapes

Using the Freeform Shape to Create a Custom Shape

Using WordArt for Interesting Titles and Headlines

Next Steps

12 Exporting Charts for Use Outside of Excel

Presenting Excel Charts in PowerPoint or Word

Copying a Document from Excel and Pasting to PowerPoint Sets Up an As-Needed Link

Copying and Pasting While Keeping Original Formatting

Pasting as Link to Capture Future Excel Formatting Changes

Copying a Chart as a Live Chart Linked to a Copy of the Original Workbook

Copying a Chart as a Picture

Creating a Chart in PowerPoint and Copying Data from Excel

Presenting Charts on the Web

Exporting Charts to Graphics

Using VBA to Export Charts as Images

Using Snag-It or Office 2010 Screen Clipping to Capture Charts

Converting to XPS or PDF

Next Steps

13 Using Excel VBA to Create Charts

Introducing VBA

Enabling VBA in Your Copy of Excel

Enabling the Developer Tab

The Visual Basic Editor

Visual Basic Tools

The Macro Recorder

Understanding Object-Oriented Code

Learning Tricks of the VBA Trade

Writing Code to Handle a Data Range of Any Size

Using Super-Variables: Object Variables

Using With and End With When Referring to an Object

Continuing a Line

Adding Comments to Code

Coding for New Charting Features in Excel 2010

Referencing Charts and Chart Objects in VBA Code

Creating a Chart

Specifying the Size and Location of a Chart

Later Referring to a Specific Chart

Coding Commands from the Design Tab

Specifying a Built-In Chart Type

Specifying a Template Chart Type

Changing a Chart’s Layout or Style

Using SetElement to Emulate Changes on the Layout Tab

Changing a Chart Title Using VBA

Emulating Changes on the Format Tab

Using the Format Method to Access New Formatting Options

Automating Changes in the Format Series Dialog

Controlling Gap Width and Series Separation in Column and Bar Charts

Moving a Series to a Secondary Axis

Spinning and Exploding Round Charts

Controlling the Bar of Pie and Pie of Pie Charts

Setting the Bubble Size

Controlling Radar and Surface Charts

Exporting a Chart as a Graphic

Creating a Dynamic Chart in a UserForm

Creating Pivot Charts

Creating Data Bars with VBA

Creating Sparklines with VBA

Next Steps

14 Knowing When Someone Is Lying to You with a Chart

Lying with Perspective

Lying with Shrinking Charts

Lying with Scale

Lying Because Excel Will Not Cooperate

Avoid Stacked Surface Charts

Asserting a Trend from Two Data Points

Deliberately Using Charts to Lie

Chart Something Else When Numbers Are Too Bad

Beware of Pictographs That Do Not Follow the Rules

A Charting References

Index