book

Excel® 2007 Charts

by John Walkenbach

October 2007

Intermediate to advanced

555 pages

12h 1m

English

Wiley

Read now

Unlock full access

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
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.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.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.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.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.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
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.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.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.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.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.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.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
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.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.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
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
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

Content preview from Excel® 2007 Charts

Chapter 11. Using Pivot Charts

In This Chapter

An introduction to pivot tables
How to create a pivot table
How to create a pivot chart
Differences between pivot charts and standard charts
Pivot chart examples

Many people (author included) consider pivot tables to be the most innovative and powerful analytical feature in Excel. A pivot table can instantly convert a mass of data into a nicely summarized table. Pivot tables have been around since Excel 5. Beginning with Excel 2000, this feature was augmented to include charting capabilities. And, in Excel 2007, pivot charts are better than ever.

This chapter starts out with an introductory overview of pivot tables (for the uninitiated) and then moves on to cover pivot charts. If you're already familiar with pivot tables, you can skip the introductory material and jump straight to the section "Working with Pivot Charts," later in this chapter.

What Is a Pivot Table?

A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data.

For example, a pivot table can create frequency distributions and cross–tabulations of several different data dimensions. In addition, you can display subtotals and any level of detail that you want. Perhaps the most innovative aspect of a pivot table is its interactivity. After you create a pivot table, ...