Pivot Tables > Get Started

How to Set Up Excel Pivot Table

People say, "it's easy to make a pivot table" in Microsoft Excel, but you're having trouble getting started. In the steps below, I'll help you make a quick plan, then build a pivot table from sample data. Download my Excel workbook, and follow along. Soon, you can say, "Pivot tables are easy!"

Author: Debra Dalgleish

What is a Pivot Table?

A pivot table is a quick and flexible way to show a summary for thousands of rows of data in an Excel workbook. With a few mouse clicks, Excel builds the report for you, using your dataset, and it's easy to change the layout, at any time later. You can use pivot tables instead of old-style Excel reports, that take a long time to build, because they have typed headings and complicated formulas to calculate the totals.

For example, the pivot table in the screen shot below shows a summary of 500 sales orders. Excel did all the construction work for me, and it only took a minute or so!

Before You Build a Pivot Table

There are a few important things for you to do, before you build a pivot table. Being prepared can save you lots of time and help you avoid problems later!

Here are the 3 preparation steps that I recommend you follow, before building your first few pivot tables.

• Step 1. Check the Source Data
• Step 2. Set a Goal
• Step 3. Think About the Layout

The details for each step are shown below.

Note: After you build a few pivot tables, you might be comfortable doing the preparation steps in your head, instead of on paper.

Step 1. Check Source Data

Before you build, check the source data that you plan to use for the pivot table, and see if it is set up correctly.

First, be sure that the data is:

• organized in a table
• has a unique heading in each column
• Tip: There are guidelines on the Create an Excel Table page.

For the pivot table example that I'm building, the source data is a named Excel table, with sales records for a food company.

• Each sales record (row) in the table has details on one product sale
• You can see a few of the fields (columns) in the screen shot below.

Here are a few ways that I could summarize the data in my pivot table:

• What was sold? – Category, Product
• Where it was sold? – Region, City
• When it was sold? – OrderDate, Year (not shown)
• How much was sold? – Quantity, SalePrice, OrderCount (not shown)

Step 2. Set a Goal

The next step is to think about what you want to show in the pivot table. Why are you building it? 

What do you want to count or sum? (You can use other calculation functions too, but count and sum are used most often.)

For example, you might have these goals, if you're creating a pivot table to share with a client, or your boss, or co-workers:

• 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 my example pivot table, I'd like to get a simple report that shows the total number of orders for each City. That could help me set an advertising budget in each city.

Step 3. Think About Layout

The final preparation step is to make a rough sketch of what the completed pivot table should look like.

• What pivot table layout could you use, based on the goal that you set?

Don't worry about this step too much though! Try making a simple outline on an Excel sheet, like I often do.

• How would you set up a worksheet report to show a total for each city?
• Get a blank Excel sheet, and type in a few headings and fake numbers
• Don't bother with any formulas -- the pivot table can do your totals automatically

Or, if you think better with a pencil in your hand, sketch out the pivot table layout on a sheet of scrap paper.

Either of these methods - Excel spreadsheet or pencil & paper, will help you picture what should go where in the pivot table that you're going to build.

The screen shot below shows my layout plan, sketched out on an Excel sheet.

• I put city names (shortened) in column B.
• The fake numbers in column C show where I'd like 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.
  • This will make it easier for Excel to build the pivot table.
• Next, click the Insert tab on the Excel Ribbon.

There are two pivot table commands in the Tables group, at the left side of the Insert tab:

• Recommended PivotTables - select a layout and Excel creates a quick pivot table
  • Use this command if you're not too experienced with pivot tables
  • Or, use it when you're looking for fresh ideas for your reports
• PivotTable - Excel creates a blank pivot table
  • Use this command if you've built lots of pivot tables already, and don't need suggestions

Both options are explained in the sections below.

Recommended PivotTables

If you’re using Excel 365, or Excel 2013 (or later version), the Recommended PivotTables feature is a great way to get started.

With this feature, Excel suggests different pivot table layouts that you could start with, based on the source data that you selected. 

Use Recommended PivotTables

To try the Recommended PivotTables feature, follow these steps:

• On the Insert tab of the Excel Ribbon, click the Recommended PivotTables command
• At the left of the window that opens, scroll through the list of  Recommended PivotTables, to see all the suggestions
  • Tip: To see a bigger view of any layout, click on it
• While you scroll, 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 my example, I was looking for a layout with 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 the most appropriate layout, from all of Excel's suggestions:

• 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. At least that step is quick and easy!

In the screen shot below, you can see the completed pivot table for Sum of Quantity by City.

Next, go to the PivotTable Field List section below, to see how to make changes to the pivot table. (You can skip over the Blank Pivot Table section below)

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 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.
  • Note: If the PivotTable Field List pane does not appear:
  • -- Click the Analyze tab on the Excel Ribbon
  • -- At the right end of the tab, in the Show group, click the Field List command.

What's in the PivotTable Fields List?

In the next section, you’ll use the PivotTable Fields List to add or remove fields.

First, take a look at the 3 main sections in the PivotTable Fields pane.

• Fields | Search Box | Layout Areas

Each section is described below the screen shot.

Fields

Near the top of the PivotTable Fields List pane, there is Field List - this shows a list of the column headings from your Excel table. If a field has been added to the pivot table layout on the worksheet, that field has a check mark.

• In the pivot table, the source data columns are called fields.
• By default, the fields are listed in the same order as the headings in your source data Excel table.

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

In the field list shown above:

• Region field has a check mark, because it has been added to the pivot table layout
• Region field appears in the Rows box.

Your field list might have different fields checked, or no fields checked.

Search

In Excel 2016 and later versions, there is a Search box above the list of fields. This can help you quickly find a field name in a long list.

To use the Search box:

• Click in the search box
• Start typing the field name you want to find.

As you type, 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 different areas of the pivot table. These areas have slightly different names in some versions of Excel:

• Filters (or Report Filter)
• Columns (or Column Labels)
• Rows (or Row Labels)
• Values

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

In the next section, you'll see how you can drag the pivot table fields into these areas, to add them to the pivot table layout on the worksheet.

After you add a field to a layout area, that field will appear in the matching area of the pivot table layout on the worksheet

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, because it is one of the numeric fields in the source data.
  • It shows the total number of orders that were placed, 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 large 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.

Video: Pivot Table Month Counts

Here's another pivot table example, built from a list that just has one column! In this example, 100 people were asked to name their birth month, and the list was entered on an Excel worksheet.

This short video below how to build a pivot table that gets a count of the number of times each month was mentioned in the survey results. There are step by step instructions on the Count Duplicates in Months List page, and you can get the sample file there too.

Download the Sample File

Food Sales: Download the sample Food Sales workbook for this tutorial, to follow along with the instructions on this page. 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