Search Contextures Sites
My Pivot Table Books:
Excel 2007 Pivot Tables Recipes
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.
Publication Date: December 10, 2007
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 filters.
- 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.
- 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:
Pivot Tables Introduction
Create a Pivot Table in Excel 2007
Pivot Data Field Layout
Show and Hide Pivot Items
Clear Old Pivot Items
Pivot Field Settings
Grouping Pivot Data
Multiple Consolidation Ranges
Pivot Tables -- Protection
Filter Source Data
Contextures Inc., Copyright ©2012
All rights reserved.
Last updated: August 7, 2011 10:10 AM