Beginning Pivot Tables in Excel 2007

Beginning Pivot Tables in Excel 2007, by Debra Dalgleish, 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

Beginning Pivot Tables in Excel 2007 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.

• Carefully explains the benefits of using Pivot Tables for fast data analysis
• Provides a step-by-step approach to those new to Pivot Tables
• If upgrading from a previous version of Excel, you'll learn tips and tricks for working with pivot tables in Excel 2007

What you'll learn in this book, for both new users, and those upgrading from previous versions of Excel:

• Summarize thousands of records with a few clicks of the mouse.
• Quickly change the layout to view a different summary of the data.
• Use filters to focus on a specific region or just the top 10 products.
• Add conditional colors or icon sets to highlight the high or low results.
• Use custom calculations and formulas to enhance the summaries.
• Create visual impact and get the message across with a pivot chart.

Order the Book on Amazon.com

You can order your copy of Beginning PivotTables in Excel 2007 from the Amazon.com website. The "Look Inside" feature is enabled, so you can see a preview of the contents.

Download the Sample Files

Note: In the sample files download, the sample InsurancePolicies.xlsx file in the Chapter 01 folder is incorrect (an Excel Table has already been created). You can download a zipped copy of the correct version by clicking this link: InsurancePolicies.zip

1. To download the sample files, go to the Beginning PivotTables in Excel 2007 page on the Apress web site:
• Beginning PivotTables in Excel 2007 page
2. Scroll down, and click on the Source Code/Downloads tab, then click Download Now



3. Click OK when prompted, then select the folder in which you want to save the sample files zipped file.

Chapter List

Here's a brief overview of the chapter contents.

1. Introducing Pivot Tables: Understanding what a pivot table is, and what benefits it provides. Preparing to create a pivot table by organizing the data in a formatted Excel table. Exploring the numerous features and benefits of using a formatted Excel table.
2. Creating a Pivot Table: Taking the first steps in building a simple pivot table from your organized data. Changing the layout and creating a simple pivot chart from the summarized data.
3. Modifying a Pivot Table: Using one or more report filters to limit the data that has been summarized. Updating the summary when source data is added, deleted, or changed. Changing the summary function that is used in the pivot table. Applying a pivot style to quickly enhance the report's appearance.
4. Summarizing Data in a Pivot Table: Using different summary functions to report on the data. Showing and hiding the row and column grand totals. Creating pivot field subtotals and changing the subtotal function. Grouping the numbers and dates in the report.
5. Formatting a Pivot Table: Using report layouts and pivot styles to change the pivot table's appearance. Creating and applying custom pivot styles. Using themes to affect the workbook's overall colors and fonts.
6. Sorting and Filtering: Adding and arranging multiple report filters. Filtering the row and column labels and removing filters. Filtering for a dynamic date range, such as Last Month or This Year. Filtering values in the total column. Applying manual filters and filtering by selection. Showing a selection of top or bottom items. Sorting the labels and values.
7. Creating a Pivot Table from External Data: Creating a pivot table from the data in a text file, an Access query, or an OLAP cube. Modifying the connection. Creating a pivot table from an existing connection in the workbook.
8. Updating a Pivot Table: Updating a pivot table when the source data changes. Refreshing all the pivot tables in a workbook. Changing the Access data source. Refreshing at regular intervals. Saving the source data with the Excel file. Retaining deleted items in the filter lists.
9. Creating Calculations: Creating custom calculations, such as running total, percent of row or column, and difference from. Creating and modifying calculated fields and calculated items. Listing the formulas used in a pivot table and changing the solve order for the calculated items.
10. Enhancing Pivot Table Formatting: Applying conditional formatting using color scales, icon sets, and data bars. Changing settings to control column widths, visible items, and label buttons.
11. Creating a Pivot Chart: Creating and modifying a default pivot chart. Adding fields and changing the pivot chart style. Formatting a pivot chart and adding chart titles and labels. Adding trend lines and creating a dynamic chart title.
12. Printing and Extracting Data from a Pivot Table: Extracting underlying records from a value cell by using the Show Details feature. Creating multiple copies of a pivot table by using the Show Report Filter Pages feature. Printing, and adjusting the print options to achieve the best results. Using the GetPivotData function to extract specific data. Using cell references in a GetPivotData formula.

Related Pages

Pivot Tables Introduction

Create a Pivot Table

Pivot Data Field Layout

Show and Hide Pivot Items

Clear Old Pivot Items

Pivot Field Settings

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.