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 number of items in each category;
- The sum of a data column, broken down into data types.
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:
-
Step 1: Select the Data To Be Included In The Pivot Table
You can manually select the entire range of data to be included in the Pivot Table or you can simply select a single cell inside your data table (note: if you select a single cell, Excel will attempt to identify and use the entire current data range for in the pivot table).
-
Step 2: Click On The Pivot Table Button
Click on the Pivot Table button, which is located in the 'Tables' group, on the 'Insert' tab of the Excel ribbon.
-
Step 3: Check the Details In the Pivot Table Dialog Box
You will be presented with the 'Create Pivot Table' dialog box. Make sure the option 'Select a table or range' is selected and make sure the range that Excel has automatically entered relates to the range that you want to use for your pivot table.
Select where you want to place your pivot table (if you are not sure, keep the default setting of 'New Worksheet'), then click OK.
-
Step 4: Specify The Contents and Structure of the Pivot Table
Excel will present you with an outline of a pivot table on your worksheet and, on the right hand side of your spreadsheet, you will see a task pane labelled 'Pivot Table Field List' (or 'PivotTable Fields', depending on your version of Excel). You will use this task pane to define how the Pivot table is to be structured.
In this example, we initially want to group the data by date, to find out which month had the largest number of sales. To do this:
-
In the 'Pivot Table Field List' task pane, drag the 'Date' field into the 'Row Labels' (or 'Rows') 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.
(The pivot table should default to displaying the count of the date entries, i.e. the number of rows containing each date).
-
In the 'Pivot Table Field List' task pane, drag the 'Date' field into the 'Row Labels' (or 'Rows') area.
-
Step 5 - Group the Pivot Table (If Required)
You will notice that the pivot table is now populated with the dates in the left hand column and the count of each date (i.e. the number of entries for each date) in the second column of the table, as shown below (left).
However, for this example, 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 right-click menu.
This causes the 'Grouping' dialog box to be displayed. From within this dialog box, select 'Months' and click OK.
Final Pivot Table
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.
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.
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.