Excel 2007 Pivot Tables Recipes

This book, by Debra Dalgleish, provides pivot table troubleshooting tips and techniques, and programming examples.

It is the second of three pivot table books that Debra wrote for the technical publishing company, Apress.

NOTE: Because the content is out of date, these books are no longer available on the Apress site, or on Amazon.

   

Overview

Excel 2007 PivotTables Recipes: A Problem-Solution Approach by Debra Dalgleish, assumes that you know the basics of Excel and pivot tables, and provides troubleshooting tips and techniques, and programming examples.

This book is for anyone who uses pivot tables, and who only reads the manual when all else fails. It's designed to help you understand the advanced features and options that are available, as you need them. Experiment with pivot tables, and if you get stuck, search for the problem in this book. With luck, you'll find a solution, a workaround, or, occasionally, confirmation that pivot tables can't do what you want them to do.

Chapter List

1. Creating a Pivot Table: Issues you should consider when planning a pivot table and preparing the source data. Using data from multiple worksheets. Creating an Excel Table from the source data and understanding the new PivotTable Field List.
2. Sorting and Filtering Data: Understanding how data sorts, creating custom sort orders, and ensuring new items sort correctly. Filtering labels for text, dates, and values; applying multiple filters to a field; filtering for top items; and applying dynamic filters.
3. Calculations in a Pivot Table: Using the summary functions and custom calculations, creating calculated items and calculated fields to expand the built-in capabilities, modifying formulas, listing all formulas, and adjusting the solve order.
4. Formatting a Pivot Table: Applying and customizing PivotTable Styles, retaining formatting, applying Report Layouts, and formatting numbers. Applying conditional formatting, such as data bars, icon sets, and color scales.
5. Grouping and Totaling Data: Grouping dates, to compare results by year, quarter, month, or week. Grouping numbers or text labels, to summarize data. Preventing errors when grouping dates or numbers, creating multiple subtotals, and displaying multiple values for a field.
6. Modifying a Pivot Table: Changing the PivotTable layout, showing all items for a field, clearing old items from the field drop-downs, hiding items with no data, and allowing drag-and-drop in the worksheet layout.
7. Updating a Pivot Table: Refreshing the PivotTable, refreshing automatically, reconnecting to the source data, locating and changing the source data, and deferring a layout update.
8. Security, Limits and Performance: Preventing users from changing the PivotTable layout, connecting to a password protected data source, using security features, addressing privacy issues, and understanding limits.
9. Printing and Extracting Data: Printing headings on every page, adjusting the print area, and starting each item on a new page. Using the Show Details feature to extract underlying records, using the GetPivotData worksheet function to extract PivotTable data, turning off the GetPivotData feature, and using cell references in GetPivotData formulas.
10. Pivot Charts: Planning and creating a pivot chart, creating normal charts from PivotTable data, creating multiple series for years, creating a combination chart, and locating the source PivotTable.
11. Programming a Pivot Table: Recording and using macros, modifying recorded code. Sample code for automatically deleting created sheets, changing report filters in related PivotTables, preventing layout changes, refreshing automatically when source data changes, and identifying and changing the pivot cache.

Related Topics

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Summary Functions

Clear Old Items in Pivot Table