ExcelFunctions.net

Search Site:

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.

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

A | B | C | D | |
---|---|---|---|---|

1 | Invoice No. | Date | Item Details | Price |

2 | BX00001 | 01/01/2011 | IPod | $130 |

3 | BX00002 | 01/01/2011 | Laptop Computer | $549 |

4 | BX00003 | 01/01/2011 | Digital TV | $1,099 |

5 | .. . | .. . | .. . | .. . |

You could produce a simple pivot table from the above data, that shows the peak months for sales. You could also show how the peak sales vary, by month, for different item types.

Note that the example below applies to current versions of Excel (2007 and later). If you are using an earlier version of Excel, you may prefer to view the page on how to create a pivot table in Excel 2003.

The following steps guide you through the process of creating a simple pivot table using the above sales spreadsheet.

Click on a single cell inside your data table or select the entire data set that is to be used in the pivot table.

(Note that if a single cell inside the data set is selected, Excel should be able to detect the entire data range automatically, for use in your pivot table).

- Click on the Pivot Table button, which is located in the 'Tables' group, on the 'Insert' tab of the ribbon.

- 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 is correct.
- Select where you want to put your pivot table (if you are not sure, keep the default setting of 'New Worksheet'), then click
**OK**.

Excel will now 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). This task pane is used to define how the Pivot table is to be structured.

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.

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 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 right-click menu.

This causes the 'Grouping' dialog box to be displayed. From within this dialog box, select 'Months' and click **OK**.

The grouping of the months results in the pivot table shown aboveon the right. From this we can clearly see that the peak months, in terms of the numbers of sales in 2011 were March and July, each of which had 442 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, instead of dragging the date field into the '∑ Values' Area of the 'Pivot Table Field List' task pane, 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__.

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

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.

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.