Contextures

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. The full transcript is available below

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.

pivot table data

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.

    recommended pivot tables

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

    recommended pivot tables

  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.

pivot table and field list

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.

add fields from field list

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!

Video Transcript

Here is the full transcript for the video - Create a Pivot Table in Excel 2013 - shown above.

VIDEO TRANSCRIPT

A pivot table is a great way to summarize data in Excel. Here we have a table with sales records.

We can see where things were sold, what we sold, and how much and how much money we got on each sale. We can scroll down and see row after row of data.

Now I could create formulas to summarize things, but with a pivot table it will just be a few clicks to get totals. In Excel 2013 it's easier than ever to create a pivot table because there's a new tool to help you with some suggested arrangements for your data.

  • I'm going to click any cell in this table.
  • On the Insert tab, at the left, there's a Tables group and here's the new tool, which is Recommended Pivot Tables. I'll click that.

It opens up a new window, and based on the data that I've got here, it's suggesting some layouts

  • So the first one is showing the cities and then taking the total price and giving me the total sales in each city. That might be useful if I'm interested in where things are selling.
  • If we want to know what's selling, here's a layout that has the different product categories and then for each region how much we sold.
  • Below that, we can see those product categories again, and just the total price, instead of broken out by region.

You can scroll down, there are lots more options. If I go down further, we're looking at the product categories and then the product names below that. This time it's the quantity, rather than the price.

  • I'll select that one. I'll click OK, and it puts a new sheet in the workbook.

Here's the layout that I selected. Now it's got the quantity. I'm also going to add the total price. Over here, I can see all the fields from that data sheet.

  • I'll click on total price. That puts another column here where the values are.

This one went in as Sum of Total Price. This one is Count of Quantity. If I go back and look at my data, I can see that going down the quantity column, there's one item here that's text rather than a number.

So Excel sees this not as a number column, but as either text or mixed numbers and text. So it defaults to Count. But we can change that.

  • I'll just right click on one of those numbers, Summarize Values By, and instead of Count, I'll click on Sum.

So now we can see the total quantity and the total price for each product category and product.

It's a quick way to get started. You can change the layout after you've selected one of the sample layouts, but you're up and running very quickly.

 

More Pivot Table Resources

Tutorials:

 

 

 

Get weekly Excel tips from Debra

 

 

pivot power premium

 

 

pivot power premium

 

Last updated: September 11, 2019 3:20 PM