Excel Pivot Tables

If your work involves the analysis of large amounts of data, you will almost certainly benefit from using Excel pivot tables, to help you to interpret and make sense of the data.

An Excel Pivot Table gathers all the data in a spreadsheet (or a range of a spreadsheet) and presents a summary of this data in a table, that allows you to see, at a glance, information such as:

The following example shows how to create a Pivot Table from the simple spreadsheet below, which contains a company's sales for 2015.

  A B C D
1 Invoice No. Date Item Details Price
2 BX00001 01/01/2015 IPod $130
3 BX00002 01/01/2015 Laptop Computer $549
4 BX00003 01/01/2015 Digital TV $1,099
5 .
.
.
.
.
.
.
.
.
.
.
.

(Note that this example below applies to current versions of Excel (2007 and later).


How to Create a Simple Excel Pivot Table

In order to create a Pivot Table from the above example spreadsheet:

Final Pivot Table

Pivot Table in Excel 2010

The grouping of the months results in the pivot table shown aboveon the right. From this we can clearly see that the month with the greatest number of sales was March, with 624 sales.

Further details of grouping pivot tables is provided in the pivot table tutorial (part 3).


Note: In the above example, for simplicity, we have used the pivot table to show number of sales per month. However, you might prefer to show the total value of sales per month.

To do this, return to the 'Pivot Table Field List' and drag the 'Price Total' field into the '∑ Values' area. Because this field is a numeric value, by default, Excel displays the sum of the entries in the 'Price Total' field, rather than the count.

For an example of this, see the pivot table tutorial (part 2).


Create a 2-Dimensional Pivot Table in Excel

If you now want to discover if the peak month varies for each sales item, you can do this by creating a 2-dimensional pivot table.

Starting with the 1-dimensional monthly pivot table shown above, we now return to the 'Pivot Table Field List' task pane. If this is no longer visible, simply click anywhere on the pivot table and it should reappear at the right hand side of your spreadsheet.

Excel Pivot Table Field List in Excel

Drag the 'Item Details' field into the 'Column Labels' (or 'Columns') area of the task pane. You will notice that the Item Details are immediately inserted across the top of your pivot table, resulting in the final pivot table shown below.

2-Dimensional Pivot Table in Excel 2010

From the final pivot table, it is now easy to see the monthly number of sales for each individual item type.


'Recommended Pivot Tables' in Excel 2013

If you are using one of the latest versions of Excel (Excel 2013 or later), your insert tab will also have the option to produce 'Recommended Pivot Tables'. This option presents you with suggested pivot table formats, based on your data. An example of this is available on the Microsoft Office website.