Excel 2007 Pivot Tables Recipes

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

Also see Beginning Pivot Tables in Excel 2007, which explains what Pivot Tables are, how you can benefit from using them, how to create them and modify them, and how to use their enhanced features


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.

Order the Book on

Note: Debra Dalgleish is an Amazon Associate. As an Amazon Associate I earn from qualifying purchases.

You can order your copy of Excel 2007 PivotTables Recipes: A Problem-Solution Approach from the website. The "Look Inside" feature is enabled, so you can see a preview of the contents.

Excel 2007 PivotTables Recipes

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.

How to Download the Sample Files

  1. Go to the Excel 2007 PivotTables Recipes page:
  2. Scroll down, and click the Source Code/Downloads tab, then click the Download Now link.
  3. Click OK when prompted to save the file, then select a folder in which to save the sample files zipped file.

get the sample files

Related Topics on Contextures Website:

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Summary Functions

Clear Old Items in Pivot Table


Get weekly Excel tips from Debra


Last updated: May 17, 2021 7:51 PM
Contextures RSS Feed