Excel PivotTable Tutorial

Creating a Basic PivotTable

PivotTables are designed to make it easy to tabulate and summarize data in your worksheet. In order to create a PivotTable, you first need to select the range of data that you want to base the table on. For this example, select the cell range A2:F20:

Excel Worksheet

Next, click Insert → PivotTable:

Insert PivotTable Button

This action will open the Create PivotTable dialog box:

Create PivotTable Dialog

The range of data that you just selected will be displayed within the Table/Range text box:

Create PivotTable Dialog Table/Range textbox

You also have the option of selecting data from an external data source (such as another workbook or even a database) by selecting the "Use an external data source" radio button and clicking Choose Connection.

Lower in the Create PivotTable dialog, you can see options to choose where the new PivotTable will be placed:

Create PivotTable Dialog choose PivotTable report to be placed

For this example, leave the default settings unchanged and click OK:

Create PivotTable Dialog default settings

A new PivotTable will be created on a new worksheet. This worksheet will automatically be displayed with the PivotTable area in the upper left-hand corner:

New PivotTable Worksheet

Now that the PivotTable has been created, you can start adding information to it. Do this by checking the boxes beside the Month, Salesman, Region, and Profit fields in the PivotTable Fields pane:

PivotTable Fields Pane

The PivotTable will now be populated with the data that you selected:

PivotTable Populated

As you can see in the following image, the profit has been organized by Month and by Salesman, with a total profit for each Salesman in the Sum of Profit column. Because Region has been checked in the PivotTable Fields pane, you can also see a profit breakdown by region for each salesman:

PivotTable Populated

Using the PivotTable Fields Pane

The PivotTable Fields pane will be displayed once a PivotTable has been inserted. In the sample workbook, you can see it on the right-hand side of the Excel 2013 window:

PivotTable Sample Worksheet

If you cannot see this pane, click PivotTable Tools – Analyze → Field List:

PivotTable Analize Menu

The PivotTable Fields pane is divided into two primary sections:

PivotTable Fields pane

The Fields section lists all of the various fields that you can add to the PivotTable. If a field has a checkmark beside it, this indicates that the field is already part of the PivotTable. In this example, you can see that Month, Salesman, Region, and Profit are all part of the PivotTable:

PivotTable Fields section

In the upper right-hand corner of this section, you can see the Tools drop-down command. Click this command to see different layout options for the PivotTable Fields pane:

PivotTable Fields layout options

As you can see, there are five preset layouts that you can choose from. To apply a new layout, click on one of the options listed. (Generally, it is best to choose a layout that contains both the Fields and Areas sections.) Click the Tools button again to close the menu.

The Areas section of this pane allows you to specify what fields appear as filters, columns, rows, and values:

PivotTable Fields Areas section

Sorting Pivoted Data

To sort data in a PivotTable, click the arrow in the top left-hand corner of the table:

PivotTable Sorting

This menu will feature a few sorting options:

PivotTable Sort Menu

Choosing the "Sort A to Z" option will sort the table rows in ascending order. Choosing "Sort Z to A" will sort the table rows in descending order. If there are multiple row fields in the table, you can specify which field to sort using the “Select Field” drop-down menu:

PivotTable Sorting Select Field menu

For expanded sort options, click More Sort Options:

PivotTable More Sort Options

This action will open the Sort dialog:

PivotTable Sort dialog

Again, you will see options to sort data in ascending or descending order. In addition, once you select a radio button, you can click the corresponding menu to choose a field to sort by. There is also a button on the bottom of the Sort dialog labeled More Options. If you click this button, another dialog will appear with an AutoSort option. If this option is selected, the data will be sorted automatically every time the PivotTable is updated.

See all Seven Institute Excel training Options