Excel Pivot Table Tutorial Part 1 - What is a Pivot Table in Excel?

This Pivot Table tutorial begins by addressing the common question "What is a Pivot Table in Excel?".

Excel Pivot Tables are tables that summarise large amounts of data in an Excel spreadsheet. This is best explained by way of an example...

Imagine your company kept a spreadsheet of every sale that was made during the first quarter of 2016. The spreadsheet records the sale date, the invoice reference, the invoice total, the name of the sales person, and the sales region. This spreadsheet is shown below:


 ABCDE
1DateInvoice RefAmountSales Rep.Region
201/01/20162016-0001$819BarnesNorth
301/01/20162016-0002$456BrownSouth
401/01/20162016-0003$538JonesSouth
501/01/20162016-0004$1,009BarnesNorth
601/02/20162016-0005$486JonesSouth
701/02/20162016-0006$948SmithNorth
801/02/20162016-0007$740BarnesNorth
901/03/20162016-0008$543SmithNorth
1001/03/20162016-0009$820BrownSouth
11.
.
.
.
.
.
.
.
.
.
.
.
.
.
.


Pivot Table Showing Sum of Sales for 4 Sales People

An Excel pivot table can summarise the data in the above spreadsheet, to show the number entries or the sums of the values in any data column.

For example, the above pivot table on the right shows the total sum of all sales, for each of the four sales people, for the first quarter of 2016.

A more complex pivot table is shown below. In this pivot table, the sales totals are broken down by month for each sales person. The sales totals for each area are also shown.

Pivot Table Showing Monthly Sales for 4 Sales People in 2 Areas

A further feature of Excel pivot tables is the ability to quickly extract the data from any part of the pivot table.

For example, if you wanted to see a list of Brown's sales during January 2016, you would simply use the mouse to double click on the cell showing this value (the value $28,741 in the above example spreadsheet).

Excel then creates the new worksheet (shown below), listing Brown's sales during January 2016.

Expanded table of values from a pivot table selected cell

For the time being, don't worry about how the above pivot tables are produced. This section of the tutorial simply aims to answer the question "What is a Pivot Table in Excel?". We will learn how to create the tables in the next few sections of this tutorial.


Continue to Part 2 of the Excel Pivot Table Tutorial  >>