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
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
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 Amazon.com
You can order your copy of Excel
2007 PivotTables Recipes: A Problem-Solution Approach
from the Amazon.com website. The "Look
Inside" feature is enabled, so you can see a preview of the contents.
- 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.
- 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
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
- Go to the Excel 2007 PivotTables Recipes page:
- Scroll down, and click the Source Code/Downloads tab, then click
the Download Now link.
- Click OK when prompted to save the file, then select a folder
in which to save the sample files zipped file.
Related Topics on Contextures Website:
FAQs - Pivot Tables
Pivot Table Introduction
Clear Old Items in Pivot Table