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 sales data for 2011. The columns of the spreadsheet have the headings "Invoice No.", "Date", "Item Details", "Quantity", "Price Each" and "Price Total".
|1||Invoice No.||Date||Item Details||Price|
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.
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.
Before creating your pivot table, click on a single cell inside your data table or select your entire data set. Note that if you have any single cell inside the data set selected, Excel should be able to detect the entire range of data automatically, for use in your pivot table.
Next, complete the following steps:
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.
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 number of sales were March and July, which each had 442 sales.
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.
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' (or 'Pivot Table Fields') Box. If this has disappeared, 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' area (called the 'Columns' area in Excel 2013). 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 sales figures for each individual item type.
If you are using Excel 2013, 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.