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 are produced by Excel, to summarise large amounts of data in a spreadsheet. This is best explained by way of an example...


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


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


Pivot Table Showing Sum of Sales for 4 Sales People

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

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

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



Expanded table of values from a pivot table selected cell

A further feature of pivot tables is the ability to quickly show the data that makes up any part of the table. For example, if you wanted to see a list of Brown's sales during January 2013, you would simply double click on the cell showing this value (the value $23,918 in the above example spreadsheet).

Excel then creates the new worksheet (shown on the right), listing just Brown's sales during January 2013.


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.

Go To Excel Pivot Table Tutorial Part 2 - Create a Pivot Table in Excel

Return to the Excel Pivot Table Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net