Excel® 2007 PivotTables and PivotCharts

Excel® 2007 PivotTables and PivotCharts

by Paul McFedries Web Developer
Released November 2007
Publisher(s): Wiley
ISBN: 9780470132319

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

Though many consumers know the basics of using Excel, few users understand the powerful capabilities of the PivotTable and PivotChart features. These tools can help turn long lists of unreadable data into dynamically generated, easy-to-read tables and charts that highlight useful and pertinent business information. Companies rely on this type of data from marketing, accounting, and finance to help make better decisions about products, sales and even human resources. Charts are interactive, allowing the consumer to tweak results with the click of a mouse and PivotTables allow you to fuse data from several sources into one document. This book shows users how to work with PivotTables and PivotCharts to make sense of their data. It covers features such as: understanding PivotTable benefits and uses; creating and customizing PivotTables; using PivotTables to analyze business data; building custom calculations; linking to external data sources, including Access databases, Word tables, Web pages, XML data, SQL Server databases, and OLAP cubes; publishing PivotTables to the Web; multiple consolidation of data, for example, summarizing annual fiscal data by quarters; preparing financial reporting, budgeting, and data analysis across departments; and using VBA to create macros that automate frequently used PivotTable tasks.

Table of contents

  1. Copyright
  2. About the Author
  3. Author's Acknowledgments
  4. How to Use this Book
    1. Who Needs This Book
    2. Book Organization
    3. What You Need to Use This Book
    4. The Conventions in This Book
    5. What's on the Web Site
  5. 1. Understanding PivotTables
    1. Understanding Data Analysis
      1. Data
        1. Data Entry
        2. Imported Data
        3. Table
      2. Data Models
        1. Formulas
        2. Functions
      3. What‐If Analysis
        1. Data Tables
        2. Goal Seek
        3. Solver
        4. Scenarios
    2. Introducing the PivotTable
      1. Database Analysis
        1. Lookup and Retrieval
        2. Criteria and Table Functions
        3. Multiple Variables
      2. What PivotTables Do
        1. Grouping
        2. Summarizing
        3. Filtering
      3. PivotTable Imitations
    3. Learn PivotTable Benefits
      1. PivotTables Save Time
        1. Easy
        2. Fast
        3. Updateable
      2. PivotTables are Flexible
        1. Dynamic
        2. Manipulable
        3. Versatile
      3. PivotTables Suit Your Needs
        1. Customizing
        2. Formatting
    4. Survey PivotTable Uses
      1. Data Structure
        1. Tabular Data
        2. Consistent and Repeated Data
        3. Transactional Data
      2. Analysis Required
        1. Unique Values
        2. Summary
        3. Relationships
        4. Trends
      3. Reporting Needs
        1. Flexibility
        2. Frequent Changes
    5. Explore PivotTable Features
      1. PivotTable Glossary
        1. Data
        2. Drop Area
        3. External Data
        4. Labels
        5. Outer Field and Inner Field
        6. Pivot
        7. Pivot Cache
        8. Source Data
        9. Summary Calculation
    6. Introducing the PivotChart
      1. PivotChart Concepts
        1. Chart Categories (X‐Axis)
        2. Chart Data Series
        3. Chart Values (Y‐Axis)
        4. Dynamic PivotCharts
        5. Filtering
        6. Pros and Cons
      2. Explore PivotChart Features
  6. 2. Building A PivotTable
    1. Prepare Your Worksheet Data
      1. Organize Your Data
        1. Row‐and‐Column Format
        2. Unique Column Headings
        3. Incorporate Labels as Columns
      2. Prepare Your Data
        1. Accurate Data
        2. Automatic Subtotals
        3. Blank Rows
        4. Consistent Data
        5. Filtered Data
        6. Repeated Data
    2. Create a Table for a PivotTable Report
      1. Create a Table for a PivotTable Report
    3. Build a PivotTable from an Excel Table
      1. Build a PivotTable from an Excel Table
    4. Build a PivotTable from an Excel Range
      1. Build a PivotTable from an Excel Range
    5. Re‐create an Existing PivotTable
      1. Re‐create an Existing PivotTable
  7. 3. Manipulating Your PivotTable
    1. Turn the PivotTable Field List On and Off
      1. Turn the PivotTable Field List on and Off
    2. Customize the PivotTable Field List
      1. Customize the PivotTable Field List
    3. Select PivotTable Items
      1. Select PivotTable Items
    4. Remove a PivotTable Field
      1. Remove a PivotTable Field
    5. Refresh PivotTable Data
      1. Refresh PivotTable Data
    6. Display the Details Behind PivotTable Data
      1. Display the Details Behind PivotTable Data
    7. Create a Chart from PivotTable Data
      1. Create a Chart from PivotTable Data
    8. Enable the Classic PivotTable Layout
      1. Enable the Classic PivotTable Layout
    9. Add Multiple Fields to the Row or Column Area
      1. Add Multiple Fields to the Row or Column Area
    10. Add Multiple Fields to the Data Area
      1. Add Multiple Fields to the Data Area
    11. Add Multiple Fields to the Page Area
      1. Add Multiple Fields to the Page Area
    12. Publish a PivotTable to a Web Page
      1. Publish a PivotTable to a Web Page
    13. Convert a PivotTable to Regular Data
      1. Convert a PivotTable to Regular Data
    14. Delete a PivotTable
      1. Delete a PivotTable
  8. 4. Changing the PivotTable View
    1. Move a Field to a Different Area
      1. Move a Field to a Different Area
    2. Display a Different Page
      1. Display a Different Page
    3. Change the Order of Fields Within an Area
      1. Change the Order of Fields Within an Area
    4. Change the Report Layout
      1. Change the Report Layout
    5. Sort PivotTable Data with AutoSort
      1. Sort PivotTable Data with AutoSort
    6. Move Row and Column Items
      1. Move Row and Column Items
    7. Filter Row or Column Items
      1. Filter Row or Column Items
    8. Filter PivotTable Values
      1. Filter PivotTable Values
    9. Hide Items in a Row or Column Field
      1. Hide Items in a Row or Column Field
    10. Show Hidden Items in a Row or Column Field
      1. Show Hidden Items In a Row or Column Field
    11. Show Pages as Worksheets
      1. Show Pages as Worksheets
    12. Group Numeric Values
      1. Group Numeric Values
    13. Group Date and Time Values
      1. Group Date and Time Values
    14. Group Text Values
      1. Group Text Values
    15. Hide Group Details
      1. Hide Group Details
    16. Show Group Details
      1. Show Group Details
    17. Ungroup Values
      1. Ungroup Values
    18. Change the Page Area Layout
      1. Change the Page Area Layout
  9. 5. Customizing PivotTable Fields
    1. Rename a PivotTable Field
      1. Rename a PivotTable Field
    2. Rename a PivotTable Item
      1. Rename a PivotTable Item
    3. Format a PivotTable Cell
      1. Format a PivotTable Cell
    4. Apply a Numeric Format to PivotTable Data
      1. Apply a Numeric Format to PivotTable Data
    5. Apply a Date Format to PivotTable Data
      1. Apply a Date Format to PivotTable Data
    6. Apply a Conditional Format to PivotTable Data
      1. Apply a Conditional Format to PivotTable Data
    7. Show Items with No Data
      1. Show Items with No Data
    8. Exclude Items from a Page Field
      1. Exclude Items from a Page Field
  10. 6. Setting PivotTable Options
    1. Apply a PivotTable Quick Style
      1. Apply a PivotTable Quick Style
    2. Create a Custom PivotTable Quick Style
      1. Create a Custom PivotTable Quick Style
    3. Preserve PivotTable Formatting
      1. Preserve PivotTable Formatting
    4. Rename the PivotTable
      1. Rename the PivotTable
    5. Turn Off Grand Totals
      1. Turn Off Grand Totals
    6. Merge Item Labels
      1. Merge Item Labels
    7. Specify Characters for Errors and Empty Cells
      1. Specify Characters for Errors and Empty Cells
    8. Protect a PivotTable
      1. Protect a PivotTable
  11. 7. Performing PivotTable Calculations
    1. Change the PivotTable Summary Calculation
      1. Change the PivotTable Summary Calculation
    2. Create a Difference Summary Calculation
      1. Create a Difference Summary Calculation
    3. Create a Percentage Summary Calculation
      1. Create a Percentage Summary Calculation
    4. Create a Running Total Summary Calculation
      1. Create a Running Total Summary Calculation
    5. Create an Index Summary Calculation
      1. Create an Index Summary Calculation
    6. Turn Off Subtotals for a Field
      1. Turn Off Subtotals for a Field
    7. Display Multiple Subtotals for a Field
      1. Display Multiple Subtotals for a Field
  12. 8. Creating Custom PivotTable Calculations
    1. Introducing Custom Calculations
      1. Formula Basics
        1. Operands
        2. Operators
      2. Custom Calculation Types
        1. Calculated Field
        2. Calculated Item
    2. Understanding Custom Calculation Limitations
      1. General Limitations
      2. Calculated Item Limitations
      3. Calculated Field Limitations
        1. Field References
        2. Field Reference Problems
    3. Insert a Custom Calculated Field
      1. Insert a Custom Calculated Field
    4. Insert a Custom Calculated Item
      1. Insert a Custom Calculated Item
    5. Edit a Custom Calculation
      1. Edit a Custom Calculation
    6. Change the Solve Order of Calculated Items
      1. Change the Solve Order of Calculated Items
    7. List Your Custom Calculations
      1. List Your Custom Calculations
    8. Delete a Custom Calculation
      1. Delete a Custom Calculation
  13. 9. Creating a PivotChart
    1. Understanding PivotChart Limitations
      1. PivotTables Versus PivotCharts
        1. Row Area Versus Category Area
        2. Column Area Versus Series Area
      2. PivotChart Limitations
        1. Chart Types
        2. Adding and Removing Fields
        3. Pivoting Fields
    2. Create a PivotChart from a PivotTable
      1. Create a PivotChart From a PivotTable
    3. Create a PivotChart Beside a PivotTable
      1. Create a PivotChart Beside a PivotTable
    4. Create a PivotChart from an Excel Table
      1. Create a PivotChart From an Excel Table
    5. Move a PivotChart to Another Sheet
      1. Move a PivotChart to Another Sheet
    6. Filter a PivotChart
      1. Filter a PivotChart
    7. Change the PivotChart Type
      1. Change the PivotChart Type
    8. Sort the PivotChart
      1. Sort the PivotChart
    9. Add PivotChart Titles
      1. Add Chart Titles
    10. Move the PivotChart Legend
      1. Move the PivotChart Legend
    11. Display a Data Table with the PivotChart
      1. Display a Data Table with the PivotChart
    12. Print a PivotChart
      1. Print a PivotChart
    13. Delete a PivotChart
      1. Delete a PivotChart
  14. 10. Building More Advanced PivotTables
    1. Create a PivotTable from Multiple Consolidation Ranges
      1. Create a PivotTable from MultipleConsolidation Ranges
    2. Create a PivotTable from an Existing PivotTable
      1. Create a PivotTable from an Existing PivotTable
    3. Create a PivotTable from External Data
      1. Create a PivotTable from External Data
    4. Automatically Refresh a PivotTable that Uses External Data
      1. Automatically Refresh a PivotTable that Uses External Data
    5. Save Your Password with an External Data Connection
      1. Save Your Password with an External Data Connection
    6. Export an Access PivotTable Form to Excel
      1. Export an Access PivotTable Form to Excel
    7. Reduce the Size of PivotTable Workbooks
      1. Reduce the Size of PivotTable Workbooks
    8. Use a PivotTable Value in a Formula
      1. Use a PivotTable Value in a Formula
  15. 11. Building a PivotTable from an Olap Cube
    1. Understanding OLAP
      1. Data Warehouse
        1. Fact Table
        2. Measure
        3. Dimension
      2. OLAP Cube
      3. OLAP PivotTable Limitations
        1. Calculations
        2. Layout
        3. Other Differences
    2. Create an OLAP Cube Data Source
      1. Create an OLAP Cube Data Source
        1. Start the Data Source
        2. Connect to an OLAP Server
        3. Connect to a Cube File
        4. Complete the Data Source
    3. Create a PivotTable from an OLAP Cube
      1. Create a PivotTable from an OLAP Cube
    4. Show and Hide Details for Dimensions and Levels
      1. Show and Hide Details for Dimensions and Levels
        1. Show Details
        2. Hide Details
    5. Hide Levels
      1. Hide Levels
    6. Display Selected Levels and Members
      1. Display Selected Levels and Members
    7. Display Multiple Page Field Items
      1. Display Multiple Page Field Items
    8. Include Hidden Items in PivotTable Totals
      1. Include Hidden Items In PivotTable Totals
    9. Create an Offline OLAP Cube
      1. Create an Offline OLAP Cube
  16. 1. Building Formulas for PivotTables
    1. Introducing Formulas
      1. Operands
        1. Constants
        2. Worksheet Functions
        3. PivotTable Fields
        4. PivotTable Items
          1. Direct Reference
          2. Positional Reference
      2. Operators
    2. Understanding Formula Types
      1. Arithmetic Formulas
      2. Comparison Formulas
      3. Operator Precedence
    3. Introducing Worksheet Functions
      1. Function Advantages
      2. Function Structure
        1. Function Name
        2. Arguments
    4. Understanding Function Types
      1. Mathemtical Functions
      2. Statistical Functions
      3. Financial Functions
      4. Logical Functions
    5. Build a Function
      1. Build a Function
    6. Build a Formula
      1. Build a Formula
    7. Work with Custom Numeric and Date Formats
      1. Custom Numeric Formats
      2. Custom Date and Time Formats
      3. Examples
  17. 2. Using Microsoft Query with PivotTables
    1. Understanding Microsoft Query
      1. Data Source
      2. Database Queries
        1. Select Tables and Fields
        2. Filter Records
        3. Sort Records
      3. Query Criteria
      4. Microsoft Query
    2. Define a Data Source
      1. Define a Data Source
    3. Start Microsoft Query
      1. Start Microsoft Query
    4. Tour the Microsoft Query Window
    5. Add a Table to the Query
      1. Add a Table to the Query
    6. Add Fields to the Query
      1. Add Fields to the Query
    7. Filter the Records with Query Criteria
      1. Filter the Records with Query Criteria
    8. Sort the Query Records
      1. Sort the Query Records
    9. Return the Query Results
      1. Return the Query Results
  18. 3. Importing Data for PivotTables
    1. Understanding External Data
      1. External Data Types
        1. Data Source File
        2. Access Table
        3. Word Table
        4. Text File
        5. Web Page
        6. XML
      2. Access to External Data
        1. Location
        2. Login
      3. Import Data
        1. Import to PivotTable
        2. Import to Worksheet
    2. Import Data from a Data Source
      1. Import Data from a Data Source
    3. Import Data from an Access Table
      1. Import Data from an Access Table
    4. Import Data from a Word Table
      1. Import Data from a Word Table
    5. Import Data from a Text File
      1. Import Data from a Text File
    6. Import Data from a Web Page
      1. Import Data from a Web Page
    7. Import Data from an XML File
      1. Import Data from an XML File
    8. Refresh Imported Data
      1. Refresh Imported Data
  19. 4. Learning VBA Basics for PivotTables
    1. Record a Macro
      1. Record a Macro
    2. Open the Visual Basic Editor
      1. Open the Visual Basic Editor
    3. Explore the PivotTable Object Model
      1. PivotTable Object
        1. PivotTable Properties
        2. PivotTable Methods
      2. PivotField Object
        1. PivotField Properties
        2. PivotField Methods
      3. PivotItem Object
        1. PivotItem Properties
        2. PivotItem Method
    4. Add a Macro to a Module
      1. Add a Macro to a Module
    5. Run a Macro
      1. Run a Macro
    6. Set Macro Security
      1. Set Macro Security
    7. Assign a Shortcut Key to a Macro
      1. Assign a Shortcut Key to a Macro
    8. Assign a Macro to the Quick Access Toolbar
      1. Assign a Macro to the Quick Access Toolbar
  20. 5. Glossary of PivotTable Terms

Product information

  • Title: Excel® 2007 PivotTables and PivotCharts
  • Author(s): Paul McFedries Web Developer
  • Release date: November 2007
  • Publisher(s): Wiley
  • ISBN: 9780470132319