Excel Pivot Table Learn Free Most Important Tools In Excel

This Excel Pivot Table Tutorial provides a step-by-step guide on

How to create pivot tables in Excel. ?

This is followed by a guide on how to create a more advanced, two-dimensional Excel Pivot Table. Finally, we describe how to sort a Pivot Table by the data fields, so that you can easily analyse your data. Each stage of the tutorial is accompanied by a Pivot Table example. 

As the interface used to create a Pivot Table in Excel is slightly different in Excel 2003, compared to later versions of Excel, we have produced two separate versions of parts 2 & 3 of the tutorial, so that you can work through the guide that relates to your current version of Excel

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...

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

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

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 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.

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.







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 next few sections of this tutorial.