Excel Pivot Tables

If you work with large amounts of data, you will almost certainly benefit from using Excel pivot tables, to assist you in interpreting and making sense of that data.

Imagine you have the simple spreadsheet below, containing sales data for 2011. The columns of the spreadsheet have the headings "Invoice No.", "Date", "Item Details", "Quantity", "Price Each" and "Price Total".

 ABCD
1Invoice No.DateItem DetailsPrice
2BX0000102/01/2011IPod$600
3BX0000202/01/2011Laptop Computer$800
4BX0000302/01/2011Digital TV$1,400
5.
.
.
.
.
.
.
.
.
.
.
.

The rest of this page will show you how to create a simple Excel pivot table using the above spreadsheet, to show which were the peak months for numbers of sales and to find out if this varies for different item types.

The example below applies to Excel 2007, 2010 or 2013. If you are using an earlier version of Excel, you may prefer to see the page on how to create a pivot table in Excel 2003.


Create a Simple Excel 2007, 2010 or 2013 Pivot Table

Before creating your pivot table, click on a single cell inside the data set. From this, Excel should be able to detect the range of data to be used in the pivot table.

Then complete the following steps :

Steps to create an Excel 2007, 2010 or 2013 Pivot Table
  • Click on the Pivot Table button, which is generally located on the left, within the 'Insert' tab.
  • You will be presented with the 'Create Pivot Table' input window. Make sure the option 'Select a table or range' is selected and make sure the range that Excel has automatically entered is correct.
  • Select where you want to put your pivot table (the default setting of 'New Worksheet' is fine) and click OK
  • In the 'Pivot Table Field List' window, drag the 'Date' field into the 'Row Labels' Area
    (this tells the pivot table that we want our rows grouped by date)
  • Again, select the 'Date' field and this time, drag this into the '∑ Values' Area
    This tells the pivot table to show the the count of the date entries (ie. the number of rows containing each date)

Pivot Table in Excel 2010

You will notice that the pivot table is now populated with the dates in the left hand column and the count of each date (ie. the number of entries for each date) in the second column of the table, as shown aboveon the left.

However, we want the data to be grouped by month.

To do this, use the mouse to right-click on the left hand column (the dates column) of the pivot table and select the option Group ...
From the list that appears, select 'Months' and click OK.

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 peak month, in terms of the number of sales was December.


Note: In the above example, for simplicity, we have used the pivot table to show number of sales per month. However, it is more likely that you would want to show the total value of sales per month.

To do this, instead of dragging the date field into the '∑ Values' Area of the 'Pivot Table Field List' Box, drag the 'Price Total' field into this box. 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 that was shown for the date field.


Create a 2-Dimensional Pivot Table in Excel 2007, 2010 or 2013

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' Box. If this has disappeared, simply click anywhere on the pivot table and it will reappear at the right hand side of your spreadsheet.

Pivot Table Field List in Excel 2007, 2010 or 2013

Drag the 'Item Details' field into the 'Column Labels' area. 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 sales figures for each individual item type.