Let’s start with the most common question:What is a pivot table in Excel?«
Pivot Tables in Excel help to summarize large amounts of data in a comparative table. This is best explained with an example.
Suppose a company has kept a table of sales made in the first quarter of 2016. The table contains data: date of sale (Date), invoice number (Invoice Ref), invoice amount (Amount), vendor name (Sales Rep.) and sales region (Region). This table looks like this:
| 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 | … | … | … | … | … |
A pivot table in Excel can summarize the data presented in a given table, show the number of records or the sum of the values in any column. For example, this pivot table shows the total sales of each of the four salespeople for the first quarter of 2016:
Below is a more complex pivot table. In this table, the sales totals of each seller are broken down by month:
Another advantage of Excel PivotTables is that they can be used to quickly extract data from any part of the table. For example, if you want to see a sales list of a seller by last name Brown January 2016 (Jan), just double-click on the cell that represents this value (in the table above, this value $ 28,741)
This will create a new table in Excel (as shown below) that lists all the seller’s sales by last name. Brown for January 2016.
For now, we are not talking about how the pivot tables shown above were created. The main goal of the first part of the tutorial is to answer the question: “What is a pivot table in Excel?“. In the following parts of the tutorial, we will learn how to create such tables.★
★ Read more about pivot tables: → Pivot tables in Excel – tutorial