Using Pivot Tables in Microsoft Excel

This introduction has been contributed by Debra Dalgleish, Excel MVP.
Contextures, Copyright © 2014. All rights reserved.
Debra Dalgleish, Excel MVP since 2001, is the author of three books on pivot tables, published by Apress. She shares Excel tips, tutorials, and videos on her websites, www.contextures.com and www.pivot-table.com.

What is a Pivot Table?

A pivot table creates an interactive summary from many records.

For example, you may have hundreds of invoice entries in a list on your worksheet.

A pivot table can quickly total the invoices by customer, product or date.

Pivot Table Data

Before you create a Pivot Table or Pivot Chart, organize your data in a table that Excel can understand.

1. Use Headings: As in this example, the first row must have headings. Use a different heading for each column.
2. Keep It Together: Keep all the data together — don’t include any completely blank rows or columns in the table.
3. Isolate It: Leave at least one blank row and one blank column between the data table and any other information on the worksheet.
4. Create a Named Table: To make it easy to maintain your pivot table, and ensure that new data is included when you update, format the source data as a Named Table.

Create a Pivot Table

Many web sites give instructions for creating a pivot table. (See Pivot Table and Pivot Chart Links).

Create a blank pivot table by clicking a command on the Ribbon.

Click another command and Excel will even recommend a few layouts, based on your data.

Excel shows a preview of a few recommended pivot tables.

After the pivot table is built, Excel displays a task pane summarizing the data in the pivot table.

You can easily rearrange the information in the pivot table by checking the boxes and dragging the buttons to a new position.

Pivot Table Articles on the Peltier Tech Blog

• Using Pivot Tables in Microsoft Excel
• Pivot Tables in Microsoft Excel: Links
• Working with Pivot Charts in Excel
• Making Regular Charts from Pivot Tables
• Explore Your Data With Pivot Tables
• Grouping by Date in a Pivot Table
• Referencing Pivot Table Ranges in VBA
• Pivot Chart Formatting Changes When Filtered
• Using Pivot Table Data for a Chart with a Dual Category Axis
• Pivot Table Resources

Pivot Table Links

The following links on the Contextures web site help with some of the common issues and questions about pivot tables:

• Pivot Table and Pivot Chart FAQs
• Pivot Table Article Index
• Pivot Table Video Index

These are the most popular pivot table articles on the Contextures web site:

• Pivot Table Introduction
• Grouping Data
• Pivot Table from Multiple Sheets
• Running Totals
• Summary Functions
• Clear Old Items in Pivot Table Drop Downs

More Pivot Table Resources

• Contextures Pivot Table Blog
• PivotPower Premium Add-in by Contextures
• Beginning Pivot Tables Book by Debra Dalgleish
• Pivot Tables Recipe Book by Debra Dalgleish