Create a Pivot Table in Excel

These instructions are for creating a pivot table in Excel 2013 and later. For Excel 2010 / 2007 instructions, click here.

Create a Pivot Table

Watch this video, to see the steps for creating a pivot table in Excel 2013 and later. There are written instructions below the video.

Preparing Your Pivot Table Data

This tutorial gives you a quick overview of creating a pivot table. For a more detailed tutorial, go to the How to Plan and Set Up a Pivot Table page.

Before you create a pivot table, organize your data into rows and columns, and create an Excel Table. There are instructions here.

In this example the source data contains information about food sales, in two regions -- East and West.

Creating a Pivot Table

After your source data is prepared, you can create a pivot table. First, see which pivot table layouts are suggested by Excel.

1. Select any cell in the source data table.
2. On the Ribbon, click the Insert tab.
3. In the Tables group, click Recommended PivotTables.

   

4. In the Recommended PivotTables window, scroll down the list, to see the suggested layouts. Click on a layout, to see a larger view.

   

5. Click on the layout that you want to use, then click OK.

Modifying the Pivot Table

A pivot table is created in your workbook, on a new sheet, in the layout that you selected. When you select a cell within the pivot table, a PivotTable Field List appears, at the right of the worksheet.

You can change the layout of the pivot table, after it's been created. We'll add the TotalPrice field to the pivot table.

1. In the PivotTable Field List, add a check mark to the TotalPrice field. The TotalPrice field is automatically added to the pivot table, in the Values area, as Sum of TotalPrice.

Test an Interactive Pivot Table

Click here to see a completed version of a pivot table based on insurance policy data.

The pivot table demonstration is interactive, so you can use the Report Filters, at the top of the pivot table, to limit the amount of data that is being summarized.

Download the Sample File

Click here to download the zipped sample file with the Region Sales data. The zipped file is in xlsx format, and does not contain macros.

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

• copy the formatting from one pivot table, and apply it to another pivot table.
• change all the values from Count to Sum
• remove the "Sum of" from all the headings

and much more!

More Pivot Table Resources

• Pivot Table Blog
• Pivot Table Article Index
• Pivot Table Video Index

• Add-In: PivotPower Premium
• Beginning Pivot Tables Book
• Pivot Tables Recipe Book

Tutorials:

• FAQs - Pivot Tables
• Pivot Table Introduction
• Grouping Data
• Multiple Consolidation Ranges
• Running Totals
• Summary Functions
• Clear Old Items in Pivot Table