Introduction

What is a pivot table?

A pivot table is a summary of a data set that is represented as a table.

In pivot tables, the data can be summarized in multiple dimensions by positioning different fields of the original data set in rows, columns, or filters of the pivot table. Also, pivot tables can use various statistical functions to handle the data: sum, average, count, min, max, etc.

Why do we need it?

A pivot table is an extraordinarily powerful tool to analyze large data sets. If it didn’t exist, it would take users a lot of time and work to replicate the same functionality with formulas and other tools.

Quick example

Sample task

Let’s say, you have your bank account report with dates, names, and amounts of all your bank transactions over the last three months (Figure 1). Based on this information, you want to quickly create a summary with total amounts spent or received by transaction type (Figure 2).

sample excel data

Figure 1. Sample Excel data set

pivot table example

Figure 2. Pivot table example

pivot table select data set

Figure 3. Selecting a data set

How to create an Excel pivot table?

First and foremost, you need to make sure that the data set is structured appropriately: the table should have column headers in it’s first row, there should be no untitled columns and no empty rows. As soon as the data is cleaned up, the actual process is very straightforward:

  1. Select the data set, including the header row (Figure 3)
  2. In Excel ribbon, go to Insert >> Tables >> Pivot table (Figure 4)
  3. When Insert PivotTable window appears, click OK (Figure 5)
  4. Excel would create a new worksheet with Pivot table canvas on the left and Field list on the right (Figure 6). If the Field list doesn’t appear, select any cell inside the canvas.
  5. In the Field list (Figure 7):
    • Click on the field with categories (Transaction in our case), hold and drag it to the Rows area at the bottom
    • Click on the field with values (Amount in our case), hold and drag it to the Values area at the bottom
      • Make sure that the name of this field in Values area starts with Sum of… If it starts with something else (e.g., Count of…), then double-click on it, select Value Field Settings… and double-click on Sum in Summarize Values By section

As soon as you drag and drop a field into a specific area (Rows, Columns, Values, or Filters), its summary appears in Pivot table canvas on the left-hand side of Excel window. Figure 7 shows how your screen should look like after you completed the steps mentioned above.

pivot table ribbon

Figure 4. Pivot table in the Ribbon

pivot table create

Figure 5. Create PivotTable window

pivot table fields areas canvas

Figure 6. Fields list and canvas

pivot table add fields

Figure 7. Adding fields to the canvas

The same way, fields can be added to Columns area to create a two-dimensional table.

Test yourself...

Pivot Tables Quiz

Take a quick quiz to understand how well you know Excel pivot tables:

Share This