How to Set Up an Excel Pivot Table

It’s easy to create a pivot table, with a few mouse clicks. However, the hard part, especially at first, is deciding what goes where. If you aren’t sure where to begin, these steps will help you set up an Excel pivot table. Download the free workbook to follow along.

Before You Build a Pivot Table

A pivot table is a quick way to show a summary for many rows of data. It is a flexible alternative to a structured worksheet report that has typed headings, and formulas to calculate the totals.

There are a few things to do though, before you build a pivot table. Being prepared can save you lots of time and troubleshooting later!

1. Check the Source Data

Check your source data, to be sure that it is organized correctly. There are guidelines on the Create an Excel Pivot Table page. Take a few minutes to read those, if you haven’t done so already.

In this tutorial, the source data is a named Excel table, with sales records for a food company. Each record has details on one product sale. We know:

• what was sold – Category, Product
• where it was sold – Region, City
• when it was sold – OrderDate, Year
• how much was sold – Quantity, SalePrice, OrderCount

2. Set a Goal

Think about what you want to show in the pivot table.  What do you want to count or sum? (You can use other functions too, but count and sum are used most often.)

For example:

• To know where things are selling, you could show a sum of SalePrice for each City.
• To see what is selling, show the total Quantity for each Category.
• To understand when things are selling, show a sum of OrderCount for each Month.

For our pivot table, we will start with a simple report that shows the total number of orders for each City.

3. Think About the Layout

Even if you haven’t made a pivot table before, you have probably created Excel reports on a worksheet. How would you set up a worksheet report to show a total for each city?

Imagine what that would look like, and set up a small report on a worksheet, with a couple of headings, and some fake numbers. Or sketch out the report on paper, to help you picture what should go where.

The screen shot below show a worksheet report plan, with city names (shortened) in column A. The numbers in column B show where we want the total number of orders for each city.

Create a Quick Pivot Table

Now that you’ve done the preparation steps, you’re ready to create the pivot table. 

• First, select a cell in the source data table. That will make it easier for Excel to build the pivot table.
• Then, click the Insert tab on the Excel Ribbon.

There are two pivot table commands on the Insert tab of the Excel Ribbon, and both options are explained below.

• Recommended PivotTables - select a layout and Excel creates a quick pivot table
• PivotTable - Excel creates a blank pivot table

Recommended PivotTables

If you’re using Excel 2013 or Excel 2016, the Recommended PivotTables feature is a great way to get started. It shows you different pivot table layouts that you could start with, based on your source data. 

View the Recommended PivotTables:

• On the Insert tab of the Excel Ribbon, click the Recommended PivotTables command
• Scroll through the list of  Recommended PivotTables to see all the suggestions
• To see a bigger view of any layout, click on it
• Look for a layout that is similar to the plan that you sketched. Is there a layout that uses the same fields, or something similar? For our example, we're looking for City and Sum of OrderCount.

Choose a Recommended PivotTables layout:

After you've looked through the list of recommended pivot tables, click on the one that you want to use. Here's how you can choose a layout:

• Exact match: If one of the layouts is exactly what you need, click on that layout to select it, and click OK.
• Closest match: If you don’t see a layout that is exactly what you need, click on the layout that is closest to what you need, and click OK.
• No match: If none of the layouts are close to what you need, click Cancel, or click the Blank PivotTable button. Then, go to the section below, for the steps on setting up a blank pivot table.

For this example, the Sum of Quantity by City is similar to what we need, so click on that layout, then click OK.

See the Pivot Table

When the Recommended PivotTables dialog box closes, Excel inserts a new worksheet in the workbook, with the pivot table that you selected. Quick and easy!

In the screen shot below, you can see the completed pivot table for Sum of Quantity by City. Now, go to the PivotTable Field List section below, to see how to make changes to the pivot table.

Blank Pivot Table

If you’re using an earlier version of Excel, or if you don’t want to use the Recommended PivotTables feature, you can start with a blank pivot table, and create your own layout.

To create a blank Pivot Table:

• On the Insert tab of the Excel Ribbon, click the PivotTables command
• The Create PivotTable dialog box opens, with 3 sections to fill in -- Choose the Data, Choose Where, Data Model.

Choose the Data

• The first section is “Choose the Data That You Want to Analyze”
• Because you selected cell in the source data table, the option “Select a Table or Range” is activated. The source data table’s name is shown in the Table/Range box – Sales_Data

NOTE: If you want to use a different table or range, you can type an Excel table name or select a different range address in the Table/Range box

Choose Where

• The next section is “Choose Where You Want the PivotTable Report to Be Placed”
• New Worksheet is the default selection. Leave that option selected, for this example.

NOTE: Instead of adding a new sheet, you can click the Existing Worksheet option, and select the sheet where you want the pivot table to be created.

Data Model

• The final section is “Add This Data to the Data Model”.  Leave that box unchecked – we want to create a normal pivot table from the source data. (Using the data model would create an OLAP-based pivot table)

Click OK

• Click OK, to create the blank pivot table.

When the Create PivotTable dialog box closes, Excel inserts a new worksheet in the workbook, using the next available sheet number. The outline of an empty pivot table starts in cell A3.

Now, go to the PivotTable Field List section below, to see how to add fields to the pivot table.

Open the PivotTable Field List

Now that you have a pivot table started, you can add or remove fields, or move the fields to a different area. There is a built-in PivotTable Field List, to help with that task.

To see the PivotTable Field List:

• Click any cell in the pivot table layout.
• The PivotTable Field List pane should appear at the right of the Excel window, when a pivot cell is selected.
• If the PivotTable Field List pane does not appear click the Analyze tab on the Excel Ribbon, and then click the Field List command.

In a minute, you’ll use the PivotTable Field List to add or remove fields. First, take a look at the sections in the PivotTable Field List.

Fields | Search Box | Layout Areas

In the field list shown below, the Region field has a check mark, and the Region field appears in the Rows box. Your field list will have different fields checked, or no fields.

Fields

Near the top of the PivotTable Field List pane is a list of the column headings from your Excel table; they appear in the same order as in the Excel table. In the pivot table, these are called fields.

If you used a Recommended PivotTable layout, you will see a check mark beside the fields that are in the pivot table.

Search

In Excel 2016, there is a Search box above the list of fields. This can help you quickly find a field name in a long list. Click in that box and start typing. The field list will be filtered automatically, to only show the field names that contain the string of letters that you typed.

Layout Areas

At the bottom of the PivotTable Field List pane are the four areas of the pivot table: Report Filter, Column Labels, Row Labels, and Values. You can drag the fields into these areas, and they’ll appear in the matching area of the pivot table layout on the worksheet

If you used a Recommended PivotTable layout, you will see the fields from that layout in those areas.

Add Field to Pivot Table Layout

The quickest way to add a field to the pivot table layout is to use the check boxes in the field list.

• If a field has text values, Excel will put it in to the Row area. That will create a list of headings at the left side of the pivot table.
• If a field has only numeric values, Excel will put it into the Values area -- to the right of the Row fields.

Recommended Pivot Table

If you started with a recommended pivot table, add a check mark to the OrderCount field.

• The OrderCount field will be added to the Values area, as Sum of OrderCount. It shows the total number of orders in each city.

Blank Pivot Table

If you started with a blank pivot table, add a check mark to the City and OrderCount fields.

• The City field will be added to the Row area, and the city names appear in column A, as a set of headings. Even if there are multiple orders for a city, its name only appears once.
• The OrderCount field will be added to the Values area, as Sum of OrderCount. It shows the total number of orders in each city.

Remove Field From Layout

The quickest way to remove a field from the pivot table layout is to clear its check boxes in the field list.

• If you used the Recommended Pivot Table, remove the check mark from the Quantity field.

Excel removes the field from the pivot table layout, so only the City and OrderCount fields are showing.

Add More Fields

After you create your pivot table, you can add more fields, to show additional details about the data. Currently, the pivot table shows the total number of orders for each city. In this example, you can add another field, to see which product categories were sold.

• In the PivotTable Field List, add a check mark to the Category field.

Category is added to the Rows area of the pivot table layout, below the City field. Now you can see the total number of orders for each city, and the number of orders for each product category, in each city.

You could also add a field to show when the orders were sold. The source data has a Year field, so add a check mark to Year in the field list. Because the years are numbers, Excel adds the field to the Values area, as Sum of Year.

We don't want a Sum of the Years -- we want to use that as a heading in the pivot table. Let's fix that!

Move the Fields

Instead of using the default location for a pivot table field, you can move it. In this example, the Year field should not show a Sum. Even though the field contains numbers, we don't want to use is in the Values area.

To change the Year field to a heading, you can move it to the Rows area or to the Columns area.

First, follow these steps to move the Year field to the Rows area:

• In the PivotTable Field List, point to the Sum of Year field in the Values area
• Drag the Sum of Year field to the Rows area, above the City field.

Now the pivot table shows the number of orders for each year, and the city and category subtotals.

That makes a tall narrow pivot table, that is a bit hard to read. To make it easier to compare the totals for each year, you can move the Year field to the Columns area. That will create year heading across the top of the pivot table.

Now you can see the data for the years side-by-side, so it's easier to compare the data for each city and the products sold there.

Add a Filter Field

Pivot tables make it easy to summarize a larg amount of data, and sometimes you'd rather focus on part of the data, instead of seeing everything. To focus on data for one of the regions, you can add the Region field as a filter.

• In the PivotTable field list, right-click on the Region field, and click Add to Report Filter

The Region filter appears above the Row labels on the worksheet.

• Click the drop down arrow for the Region filter, and select the West region.

The pivot table changes -- only the cities in the West region are visible -- Los Angeles and San Diego.

Keep Experimenting

Those are the basic steps for planning, creating, and changing a pivot table. Get the sample workbook at the link in the next section, and experiment with that data. Plan a different type of pivot table, and try to build that. For example:

• Show the sum of sales price for each region for each year, with a filter for product
• Show the sum of quantity for each product for each region, with a filter for category

Then, make a copy of one of your own workbooks, and plan a simple pivot table using your data.

Based on the fields in your data, what other types of pivot table reports could you create, to show the where, when, what and how much of that data?

The more you experiment, the better you'll get at showing the reports that you need. And no matter how long you've been making pivot tables, it's worthwhile to try a different layout, to see if it makes the information easier to understand.

Download the Sample File

Download the sample workbook for this tutorial, to follow along with the instructions. The zipped file is in xlsx format, and does not contain any macros.

More Links

FAQs - Pivot Tables

Pivot Table Introduction

Count Duplicates with Pivot Table

Summary Functions

Pivot Table Blog

Pivot Table Article Index

Free Pivot Table Add-in