Contextures

Home > Pivot > Layout > Create

Create a Pivot Table in Excel

Create an Excel pivot table, so you can quickly analyze Excel data. I show the steps in a short video, and there are written steps and screen shots too.

recommended pivot tables

Create a Pivot Table

Watch this video, to see the steps for creating a pivot table in Excel. There are written instructions and screen shots below the video, and a sample file that you can download.

Step 1) Prepare Your Pivot Table Data

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 set contains information about food sales, in two regions -- East and West.

pivot table data

Step 2) Create a Pivot Table

After your source data is prepared, you can create a pivot table.

First, follow these steps, to see which pivot table layouts Excel recommends.

  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.
  5. Then, click on a layout, to see a larger view.
    • recommended pivot tables
  6. Next, click on the layout that you want to use
  7. To create the pivot table, click OK.

Pivot Table on Worksheet

As soon as you click the OK button, a pivot table is created in your workbook.

  • The new pivot table is located on a new worksheet
  • The pivot table has the layout that you selected from the Recommended PivotTables dialog box.
  • The pivot table shows totals by Category and Product
  • There are subtotals and grand total too.
  • Select any cell in the pivot table, and a PivotTable Field List appears

Pivot Table Field List

In the PivotTable Fields pane, there is a button for each field in the pivot table:

  • Category is in the Rows area
  • Quantity is in the Values area, as Count of Quantity.
  • There are no fields in the Filters area or in the Columns area

pivot table and field list

Step 3) Adjust the Pivot Table

The best part of working with a pivot table is that it's really easy to change the layout. 

For example:

  • To add another field, just add a check mark to its name in the Pivot Table¬†field list.
  • To remove a field, remove its check mark in the field list
  • To move a field, drag a field button to a different area in the pivot table field list

In the screen shot below, I added the TotalPrice field to the pivot table layout.

add fields from field list

Try This Pivot Table

Before you build your own pivot table in an Excel workbook, you can see how a pivot table works, by trying the interactive Microsoft Excel example shown below.

Below the interactive pivot table, there are a few things you can try, to see different results in the pivot.

Note: You can download a copy of the file, to test the pivot table features on your own computer.

Change the Filters

Above the data in the pivot table, there are filter fields - State, Business Type (BusType), and Flood.

Make the following changes to the pivot table filter fields, to change what the pivot table is showing:

  • In cell B1, click the drop down arrow, to see a drop-down list of State codes
    • Uncheck one or more of the state code, to remove that data from the pivot table display
    • At the bottom of the drop down list, click the OK button
    • The value in cell B1 changes from (All) to (Multiple Items)
    • The pivot table shows the total amounts for the selected states only, instead of all the states
  • In cell B3, click the drop down arrow, to see a drop-down list with Y and N, for Flood
    • Select Y (yes), then click the OK button
    • The value in cell B3 changes from (All) to Y
    • The pivot table shows the total amounts for only the insurance policies that have flood insurance coverage

Sort the Pivot Table Values

Currently, the pivot table is sorted alphabetically by the values in the Rows area -- the Construction type, and the Region.

To see the data in a different way, you can follow these steps to sort by the policy count

  • In column A, select one of the cells with a region name, such as cell A8 (East)
  • In cell A6, with the Row Labels header, click the drop down arrow
  • Point to Region, then click Sort by Value
  • In the Sort by Value (Region) dialog box, click the arrow at the right side of the "Select Value" box
  • Click on Count of Policy
  • For Sort Options, click the radio button for Largest to Smallest
  • Click the OK button

After you click OK, in the pivot table, under each construction type, the regions are listed from largest to smallest, based on the policy count field.

pivot table regions sorted by count of policy

Show Amounts as Sum or Count

When you added the TotalPrice field, Excel automatically added it to the Values area, and used the Sum function to summarize the prices.

Excel did that because:

  • TotalPrice is a number field
  • Every row in the source data, for the TotalPrice field, contains a real number.

However, when the pivot table was created, the Quantity field was added to the Values area, because it's a number field too. But the Quantity field shows a count, instead of a sum.

Why did Quantity use the Count function, instead of Sum?

  • Although most of the values are numbers, there is at least one text value in the Quantity column, as you can see in row 12, in the screen shot below.
  • If any values in a field are non-numeric, Excel uses the Count function for the Value field, instead of the Sum function.

pivot table data

Get 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.

Video Transcript: Create a Pivot Table

Here is the full transcript for the Create a Pivot Table video.

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 Tutorials

How to Plan and Set Up a Pivot Table

FAQs - Pivot Tables

Pivot Table Shortcuts

Pivot Table Introduction

 

About Debra

 

 

Last updated: February 24, 2024 2:53 PM