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 Links

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

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

More Pivot Table Resources

Peltier Tech Chart Utility

Subscribe without commenting

Trackbacks

  1. […] the first article, Using Pivot Tables in Microsoft Excel, I explain how to set up a simple pivot table, based on a list of food […]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.