This page provides a step-by-step guide of how to create an advanced pivot table in Excel.
Note that the directions on this page apply to current versions of Excel (2007 and later). If you have an earlier version of Excel, go to the page on how to create an advanced pivot table in Excel 2003.
|1||Date||Invoice Ref||Amount||Sales Rep.||Region|
Click on the Pivot Table button, which is located within the 'Tables' grouping, on the 'Insert' tab of the Excel ribbon.
You will be presented with the 'Create PivotTable' dialog box (shown on the rightabove).
Make sure that the selected range refers to the range of cells that you want to use for your Pivot Table.
There is also an option asking where you want the Pivot Table to be placed. This allows you to place your pivot table in a specified worksheet. Otherwise, select the default option New worksheet.
You will now be presented with an empty Pivot Table, and the 'Pivot Table Field List' task pane, which contains several data fields. Note that these are the headers from your initial data spreadsheet.
We want the Pivot Table to show the sums of the sales figures for each month, broken down by region and sales rep.
Therefore, from the 'Pivot Table Field List' task pane:
The resulting Pivot Table will be populated with the daily sales totals for each sales region and each sales rep, as shown below.
As required, the final pivot table (shown below) displays the total monthly sales, broken down by sales region and sales rep.
If you want to visually improve the above Pivot Table, you could format columns B-G as currencies.
The easiest way to format a range of cells as currencies is to select the range to be formatted (columns B-G in the above example), and then to click on the currency format button, which is found in the 'Number' group on the 'Home' tab of the Excel ribbon (see rightabove)).
The Pivot Table report filter allows you to view the data for a single value, or a selection of specified values in your data fields.
For example, in the pivot table above, you could view just the data for the North sales region or just the data for the South sales region.
In order to view just the data for the 'North' sales region, return to the 'Pivot Table Field List' task pane, and drag the 'Region' field header into the 'Report Filter' (or 'Filters') area.
You will see that a 'Region' field appears at the top of the Pivot Table. Use the drop-down list of this field to select the Region 'North'. The resulting Pivot Table (shown on the rightabove) displays just the sales for the North region.
You can also quickly view just the sales for the South region by selecting 'South' from the drop down menu.