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

What is a Pivot Table in Excel?

A Pivot Table gathers all the data in an Excel spreadsheet (or range of a spreadsheet) and presents a summary of this data in a table. This table allows you to summarise information such as:

• The number of items of each data type;
• The sum of a data column, broken down into data types;
• The average of a data column, broken down into data types.

This is best explained by way of an example...

The spreadsheet below lists every sale that was made by a company during the first quarter of 2016. The spreadsheet records the sale date, the invoice reference, the invoice total, the name of the sales representative, and the sales region.

A B C D E
1 Date Invoice Ref Amount Sales Rep. Region
2 01/01/2016 2016-0001 \$819 Barnes North
3 01/01/2016 2016-0002 \$456 Brown South
4 01/01/2016 2016-0003 \$538 Jones South
5 01/01/2016 2016-0004 \$1,009 Barnes North
6 01/02/2016 2016-0005 \$486 Jones South
7 01/02/2016 2016-0006 \$948 Smith North
8 01/02/2016 2016-0007 \$740 Barnes North
9 01/03/2016 2016-0008 \$543 Smith North
10 01/03/2016 2016-0009 \$820 Brown South
11 .
.
.
.
.
.
.
.
.
.
.
.
.
.
.

An Excel pivot table can summarise the data in the above spreadsheet for easy analysis.

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

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.

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 Pivot Table).

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

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 these pivot tables in the following sections of this tutorial.