book

Excel® 2007 Charts

Name: Excel® 2007 Charts
Author: John Walkenbach
ISBN: 9780470044001

by John Walkenbach

October 2007

Intermediate to advanced

555 pages

12h 1m

English

Wiley

Read now

Unlock full access

Copyright
About the Author
Acknowledgments
Introduction
What You Should KnowWhat You Should HaveConventions Used in This BookRibbon CommandsFormula ListingsVBA Code ListingsKey NamesFunctions, Procedures, and Named RangesMouse ConventionsWhat the Icons MeanHow This Book Is OrganizedHow to Use This BookAbout the Power Utility Pak OfferReach Out
I. Chart Basics
1. Introducing Excel Charts
1.1. What Is a Chart?1.2. How Excel Handles Charts1.2.1. Embedded Charts1.2.2. Chart Sheets1.3. Parts of a Chart1.4. Basic Steps for Creating a Chart1.4.1. Creating the Chart1.4.2. Switching the Row and Column Orientation1.4.3. Changing the Chart Type1.4.4. Applying a Chart Layout1.4.5. Applying a Chart Style1.4.6. Adding and Deleting Chart Elements1.4.7. Formatting Chart Elements1.5. Working with Charts1.5.1. Moving and Resizing a Chart1.5.2. Converting an Embedded Chart to a Chart Sheet1.5.3. Copying a Chart1.5.4. Deleting a Chart1.5.5. Adding Chart Elements1.5.6. Moving and Deleting Chart Elements1.5.7. Formatting Chart Elements1.5.8. Copying a Chart's Formatting1.5.9. Renaming a Chart1.5.10. Printing Charts
2. Understanding Chart Types
2.1. Conveying a Message with a Chart2.2. Choosing a Chart Type2.3. Excel's Chart Types2.3.1. Column Charts2.3.2. Bar Charts2.3.3. Line Charts2.3.4. Pie Charts2.3.5. Scatter Charts2.3.6. Area Charts2.3.7. Doughnut Charts2.3.8. Radar Charts2.3.9. Surface Charts2.3.10. Bubble Charts2.3.11. Stock Charts2.4. Creating Combination Charts2.5. Creating and Using Chart Templates
3. Working with Chart Series
3.1. Specifying the Data for Your Chart3.2. Adding a New Series to a Chart3.2.1. Adding a New Series by Copying a Range3.2.2. Adding a New Series by Extending the Range Highlight3.2.3. Adding a New Series Using the Select Source Data Dialog Box3.2.4. Adding a New Series by Typing a New SERIES Formula3.3. Deleting a Chart Series3.4. Modifying the Data Range for a Chart Series3.4.1. Using Range Highlighting to Change Series Data3.4.2. Using the Select Data Source Dialog Box to Change Series Data3.4.3. Editing the SERIES Formula to Change Series Data3.5. Understanding Series Names3.5.1. Changing a Series Name3.5.2. Deleting a Series Name3.6. Adjusting the Series Plot Order3.7. Charting a Noncontiguous Range3.8. Using Series on Different Sheets3.9. Handling Missing Data3.10. Controlling a Data Series by Hiding Data3.11. Unlinking a Chart Series from Its Data Range3.11.1. Converting a Chart to a Picture3.11.2. Converting Range Reference to Arrays3.12. Working with Multiple Axes
4. Formatting and Customizing Charts
4.1. Chart Formatting Overview4.1.1. Selecting Chart Elements4.1.2. Common Chart Elements4.1.3. User Interface Choices for Formatting4.2. Adjusting Fills and Borders: General Procedures4.2.1. About the Fill Tab4.2.2. Formatting Borders4.3. Formatting Chart Background Elements4.3.1. Working with the Chart Area4.3.2. Working with the Plot Area4.4. Formatting Chart Series4.4.1. Basic Series Formatting4.4.2. Using Pictures and Graphics for Series Formatting4.4.3. Additional Series Options4.5. Working with Chart Titles4.5.1. Adding Titles to a Chart4.5.2. Changing Title Text4.5.3. Formatting Title Text4.5.4. Linking Title Text to a Cell4.6. Working with a Chart's Legend4.6.1. Adding or Removing a Legend4.6.2. Moving or Resizing a Legend4.6.3. Formatting a Legend4.6.4. Changing the Legend Text4.6.5. Deleting a Legend Entry4.7. Working with Chart Axes4.7.1. Value Axis versus Category Axis4.7.2. Value Axis Scales4.7.3. Using Time-Scale Axes4.7.4. Creating a Multiline Category Axis4.7.5. Adding and Removing Axes4.7.6. Axis Number Formats4.8. Working with Gridlines4.8.1. Adding or Removing Gridlines4.9. Working with Data Labels4.9.1. Adding or Removing Data Labels4.9.2. Editing Data Labels4.9.3. Problems and Limitations with Data Labels4.10. Working with a Chart Data Table4.10.1. Adding and Removing a Data Table4.10.2. Problems and Limitations with Data Tables4.11. Formatting 3-D Charts4.11.1. Rotating 3-D Charts4.11.2. Formatting a Surface Chart
5. Working with Trendlines and Error Bars
5.1. Working with Trendlines5.1.1. Chart Types That Support Trendlines5.1.2. Data Appropriate for a Trendline5.1.3. Adding a Trendline5.1.4. Formatting a Trendline5.1.5. Formatting a Trendline Label5.2. Linear Trendlines5.2.1. Linear Forecasting5.2.2. Getting the Trendline Values5.3. Nonlinear Trendlines5.3.1. Logarithmic Trendline5.3.2. Power Trendline5.3.3. Exponential Trendline5.3.4. Polynomial Trendline5.4. Displaying a Moving Average5.4.1. Adding a Moving Average Line5.4.2. Creating Your Own Moving Average Data Series5.5. Using Error Bars in a Chart Series5.5.1. Chart Types That Support Error Bars5.5.2. Adding Error Bars to a Series5.5.3. Using Custom Error Bars5.5.4. Connecting Series Points to a Trendline with Error Bars5.5.5. Error Bar Alternatives5.6. Other Series Enhancements5.6.1. Series Lines5.6.2. Drop Lines5.6.3. High–Low Lines5.6.4. Up/Down Bars

6. Working with Shapes and Other Graphics
6.1. Using Shapes6.1.1. Inserting Shapes6.1.2. Adding Text to a Shape6.1.3. Formatting Shapes6.1.4. Selecting Multiple Objects6.1.5. Grouping Objects6.1.6. Moving Shapes6.1.7. Copying Objects6.1.8. Changing the Stack Order of Objects6.1.9. Aligning and Spacing Objects6.1.10. Changing a Shape to a Different Shape6.1.11. Reshaping Shapes6.1.12. Changing the Shape Defaults6.1.13. Printing Objects6.2. Working with SmartArt6.2.1. Inserting SmartArt6.2.2. Customizing SmartArt6.2.3. Changing the Layout6.2.4. Changing the Style6.2.5. Finding Out More about SmartArt6.3. Using WordArt6.4. Working with Other Graphic Types6.4.1. About Graphics Files6.4.2. Using the Clip Art Task Pane6.4.3. Inserting Graphics Files6.4.4. Copying Graphics by Using the Clipboard6.4.5. Displaying a Worksheet Background Image6.5. A Gallery of Graphic Examples6.5.1. Using Shapes and Pictures with Charts6.5.2. Calling Attention to a Cell6.5.3. Changing the Look of Cell Comments6.5.4. Pasting Pictures of Cells
II. Mastering Charts
7. Creating Interactive Charts
7.1. Introducing Interactive Charts7.2. Hands–On: Creating a Self–Expanding Chart7.2.1. Creating the Chart7.2.2. Creating Named Formulas7.2.3. Modifying the Series7.2.4. Testing the Self–Expanding Chart7.2.5. Understanding How the Self–Expanding Chart Works7.3. Controlling a Series with a Scroll Bar7.3.1. Creating the Chart7.3.2. Defining the Names7.3.3. Adding the Scroll Bar Control7.4. Specifying the Beginning and End Point for a Series7.4.1. Creating the Chart7.4.2. Defining the Names7.4.3. Adding Spinner Controls7.5. Specifying the First Point and Number of Points for a Series7.5.1. Creating the Chart7.5.2. Defining the Names7.5.3. Adding the User Interface Elements7.6. Plotting the Last n Data Points in a Series7.6.1. Creating the Chart7.6.2. Defining the Names7.7. Plotting Every nth Data Point in a Series7.7.1. Using Filtering7.7.2. Using Array Formulas7.7.3. Creating Named Formulas7.8. Using Check Boxes to Select Series to Plot7.8.1. Creating the Chart7.8.2. Adding the Check Box Controls7.8.3. Defining the Names7.8.4. Modifying the Chart Series7.9. Creating a Very Interactive Chart7.9.1. Getting the Data7.9.2. Creating the Option Button Controls7.9.3. Creating the City Lists7.9.4. Creating the Chart's Data Range7.9.5. Creating the Chart
8. Charting Techniques and Tricks
8.1. Adding Lines and Backgrounds to a Chart8.1.1. Adding Horizontal Reference Lines to a Chart8.1.2. Adding a Vertical Line to a Chart with an XY Series8.1.3. Using Background Columns to Represent a Vertical Line8.1.4. Adding Vertical or Horizontal "Bands"8.1.5. Creating a Scatter Chart with Colored Quadrants8.2. Charts That Use a Single Data Point8.2.1. Creating a Thermometer Chart8.2.2. Creating a Gauge Chart8.3. Using a Dummy Axis8.3.1. An Introductory Example8.3.2. Labeling an Axis with Nonequal Intervals8.4. Column and Bar Chart Variations8.4.1. Stacked Column Chart Variations8.4.2. Creating a Step Chart8.4.3. Varying Column Widths8.4.4. Conditional Column Colors8.4.5. Creating a Comparative Histogram8.4.6. Creating Gantt Charts8.4.7. Identifying the Maximum and Minimum Values in a Series8.4.8. Shading between Two Series in a Line Chart8.5. Creating a Timeline8.6. Creating "Impossible" Charts by Stacking and Overlaying8.6.1. Stacking Charts8.6.2. Overlaying Charts8.6.3. Simulating a "Broken" Value Axis8.7. Displaying Multiple Charts on a Chart Sheet
9. In-Cell Charting Techniques
9.1. Visualizing Data with Conditional Formatting9.1.1. Data Bars9.1.2. Color Scales9.1.3. Icon Sets9.2. Plotting Data without a Chart9.3. Creating Sparkline Charts9.3.1. Sparkline Charts from Text9.3.2. Sparkline Charts from a Chart9.3.3. Commercial Sparkline Add-Ins
10. Mathematical and Statistical Charting Techniques
10.1. Drawing a Circle with an XY Series10.2. Connecting Scatter Chart Data Points to the Axes with Error Bars10.3. Connecting XY Points to the Origin10.4. Creating Frequency Distributions and Histograms10.4.1. Using the FREQUENCY Function10.4.2. Using the Analysis ToolPak to Create a Frequency Distribution10.4.3. Using Adjustable Bins to Create a Histogram10.5. Plotting a Normal Curve10.6. Plotting Z–Scores with Standard Deviation Bands10.7. Calculating the Area under a Curve10.8. Creating a Box Plot10.9. Plotting Mathematical Functions10.9.1. Plotting Functions with One Variable10.9.2. Plotting Functions with Two Variables10.10. Creating a 3–D Scatter Plot
11. Using Pivot Charts
11.1. What Is a Pivot Table?11.1.1. A Pivot Table Example11.1.2. Data Appropriate for a Pivot Table11.2. Creating a Pivot Table11.2.1. Specifying the Data11.2.2. Specifying the Location for the Pivot Table11.2.3. Laying Out the Pivot Table11.2.4. Formatting the Pivot Table11.2.5. Modifying the Pivot Table11.3. Working with Pivot Charts11.3.1. Creating a Pivot Chart11.3.2. A Pivot Chart Example11.4. Pivot Chart Banking Examples11.4.1. Question 111.4.2. Question 211.4.3. Question 311.4.4. Question 411.4.5. Question 511.4.6. Question 611.5. More Pivot Chart Examples11.5.1. Creating a Quick Frequency Distribution Chart11.5.2. Selecting Rows to Plot11.5.3. Using Data from Multiple Sheets
12. Avoiding Common Chart-Making Mistakes
12.1. Know Your Audience12.2. Chart Accuracy12.2.1. Plotting Data Out of Context12.2.2. Exaggerating Differences or Similarities12.2.3. Plotting Percent Change versus Actual Change12.2.4. Grouping with Unequal Bin Sizes12.3. Problems with Chart Type Selection12.3.1. Category versus Value Axis12.3.2. Problems with Pie Charts12.3.3. Consider Alternative Chart Types12.3.4. Problems with Negative Values12.3.5. Scatter Charts with the Smoothed Line Option12.3.6. Don't Be Tempted by 3-D Charts12.4. Chart Complexity12.4.1. Just Plain Bad12.4.2. Maximizing "Data Ink"12.5. Chart Style12.5.1. Text and Font Mistakes12.6. A Chart Maker's Checklist
13. Just for Fun
13.1. Animating Stuff13.1.1. Animated Shapes13.1.2. Animated Charts13.1.3. Doughnut Chart Wheel of Fortune13.2. Fun with Trigonometry13.2.1. A Simple Sine–versus–Cosine Plot13.2.2. Hypocycloid Charts13.2.3. Radar Chart Designs13.3. Chart Art13.3.1. A Mountain Range Chart13.3.2. A Bubble Chart Mouse Head.13.3.3. A Smile Chart13.4. Scatter Chart Drawings13.5. An Analog Clock Chart13.6. Roll the Dice13.7. Horse Race Chart13.8. Using Clip Art
III. Mastering Charts
14. VBA Overview and Basic Examples
14.1. VBA in a Nutshell14.2. The Macro Recorder and Charts14.3. The Chart Object Model14.3.1. Using Object Variables14.3.2. Using the With-End With construct14.3.3. Finding Out More about the Chart Object Model14.4. Simple Chart Macros14.4.1. Activating and Deactivating a Chart14.4.2. Determining whether a Chart Is Activated14.4.3. Determining whether the Active Chart Is Embedded14.4.4. Deleting from the ChartObjects or Charts Collection14.5. Counting and Looping through Charts14.5.1. Counting Chart Sheets14.5.2. Counting Embedded Charts14.5.3. Looping through All Charts14.5.4. Changing the Location of All Charts14.5.5. Printing All Embedded Charts14.6. Setting Axis Values14.7. Sizing and Aligning Charts
15. Advanced VBA Examples
15.1. Creating Charts with VBA15.1.1. Creating an Embedded Chart15.1.2. Creating a Chart on a Chart Sheet15.2. Using VBA to Apply Chart Formatting15.2.1. Formatting a Chart15.2.2. More Chart Formatting Examples15.3. Applying Data Labels15.3.1. A Basic Data Label Macro15.3.2. Applying Linked Data Labels15.3.3. Prompting for a Range15.4. Exporting Charts as GIF Files15.5. Creating a Scrolling Chart15.6. Creating a Word Cloud15.7. Determining the Ranges Used by a Chart15.8. Event Procedure Examples15.8.1. Worksheet_Change Event Procedures15.8.2. Selection_Change Event Procedures
16. Working with Colors
16.1. About Excel 2007 Colors16.2. Specifying Colors16.2.1. The RGB Color System16.2.2. The HSL Color System16.2.3. Converting Colors16.3. Understanding Grayscale16.3.1. Converting Colors to Gray16.3.2. Viewing Charts as Grayscale16.4. Experimenting with Colors16.5. Understanding Document Themes16.5.1. About Document Themes16.5.2. Understanding Document Theme Colors16.5.3. Displaying All Theme Colors16.6. Working with Shape Objects16.6.1. A Shape's Background Color16.6.2. Using Other Fill Types with a Shape16.6.3. Finding Out More about Shapes16.7. Modifying Chart Colors
IV. Appendixes
A. What's on the CD–ROM?
System RequirementsUsing the CDWhat's on the CDSample Files for Excel 2007 ChartsChapter 01Chapter 02Chapter 04Chapter 05Chapter 06Chapter 07Chapter 08Chapter 09Chapter 10Chapter 11Chapter 12Chapter 13Chapter 14Chapter 15Chapter 16eBook version of Excel 2007 ChartsApplicationsTroubleshootingCustomer Care
B. Other Charting Resources
Excel's Help SystemMicrosoft Technical SupportSupport OptionsMicrosoft Knowledge BaseMicrosoft Excel Home PageMicrosoft Office Home PageInternet NewsgroupsAccessing Newsgroups by Using a NewsreaderAccessing Newsgroups by Using a Web BrowserSearching NewsgroupsInternet Web SitesThe Spreadsheet PageDaily Dose of ExcelJon Peltier's Excel PageExcel UserTushar Mehta ConsultingPearson Software ConsultingStephen Bullen's Excel PageDavid McRitchie's Excel PagesMr. ExcelJunk ChartsExcel Charting Add–Ins and EnhancementsThe Chart AssistantCrystal XcelsiusGrab–ItMicroChartsPower Utility PakSparkMakerSpeedometer Chart CreatorXY Chart Labeler
Wiley Publishing, Inc. End-User License Agreement

Overview

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.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470044001errata

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills