Search Contextures Sites
Custom Search

More Books

Beginning Pivot Tables in Excel 2007  

Excel Pivot Tables Recipe Book  

Contextures
Excel news
by email

 

 


 


 

 

Excel 2007 Pivot Tables Recipes

This book 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

Overview

Order the Book on Amazon.com

Chapter List

How To Download the Sample Files

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.

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.

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.

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

GetPivotData

Grouping Pivot Data

Multiple Consolidation Ranges

Printing   

Custom Calculations 

Pivot Cache     

Pivot Tables -- Protection  

Grand Totals

Running Totals  

Filter Source Data  

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: December 27, 2015 10:54 AM