Excel Pivot Tables Tutorial: Create a Pivot Table Report, Add & Remove Fields
Create a Pivot Table and Pivot Chart report; Add, Copy, Move, Rearrange & Remove Pivot Table Fields; use Pivot Table Field List pane.
As applicable to Excel 2007
Excel Pivot Table Tutorial:
1. Create a Pivot Table report; Add, Copy, Rearrange & Remove Pivot Table Fields; ‘Pivot Table Field List’ Pane
2. ‘Pivot Table Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data.
5. Group Items in a Pivot Table report, Group Data, Group Date and Time Values, Grouped Field.
Refer complete Tutorial on working with Pivot Tables using VBA:
Create and Customize Pivot Table reports, using vba
A Pivot Table is a report created in tabular format which automatically (without using any Excel functions or formula) extracts, organizes (by sorting, counting and totalling) and summarizes data from a relatively larger data table. These reports make it a vital tool for data analysis, comparisons or to determine trends.
A Pivot Table summarizes (groups, filters, sorts, formats, …) detailed and large data making them ideal for data analysis and comparison. Using named ranges create a dynamic data source so that new or deleted items automatically get updated when the Pivot Table is refreshed. The Pivot Table can be rearranged and restructured by moving data (this is called pivoting) and we can analyze and compare the effect of moving and rearranging which makes a Pivot Table interactive. Hence a Pivot Table is a dynamic and interactive report used to extract, organize and summarize detailed data into meaningful information. A Pivot Chart report being a graphical display of data of its associated Pivot Table report make this combination an unbeatable management analytical tool.
Pivot Tables serve as an important management tool, by providing significant analysis of information extracted from base data, viz.:
– to determine the sales figures for each car model for different regions;
– to determine the trends of sales figures over the past years;
– to determine regions which contribute maximum sales for the company.
Create a Pivot Table and/or Pivot Chart report
1. Choose source data range in the same or another workbook, or from an external data source:
Organize Base Data:
Enter the data in a worksheet range, in tabular form, with rows and columns. The first row should contain column headings or labels which will be used for field names in Pivot Tables. There should be no blank rows or columns, not even a blank row after the first row of column headings. The Pivot Table calculates sub-totals and grand-totals, so any totals in the base data should be removed. It might be preferable to used named ranges for base data because if you add (or reduce) to your original data then these changes are automatically included when you just refresh the PivotTable and you will not manually have to Change Data Source range specification. This base data range can be in the same worksheet where you create a PivotTable or PivotChart Report, or in another worksheet in the same workbook or in another workbook.
Create a Pivot Table and/or Pivot Chart Report:
In the ‘Insert’ tab on the ribbon -> click ‘PivotTable’ in the ‘Tables’ group -> click ‘PivotTable’ to insert a PivotTable or click ‘PivotChart’ to insert a PivotTable with a PivotChart. Clicking on ‘PivotTable’ opens the dialog box ‘Create PivotTable’, and clicking on ‘PivotChart’ opens the dialog box ‘Create PivotTable with PivotChart’.
Choose the data that you want to analyze: In the dialog box ‘Create PivotTable’ (or ‘Create PivotTable with PivotChart’) you have options to “Select a table or range” or “Use an external data source”.
Select a table or range: If you choose this option, your pre-selected worksheet data range automatically gets filled in the ‘Table/Range’ box or alternatively you can select or enter a range. If the source data is residing in the same workbook (where the Pivot Table or Pivot Chart is report is being inserted), its range is entered as Sheet44!$D$5:$D$10 where Sheet44 is the worksheet name. If source data is residing in another workbook in the same folder, the workbook name should precede the worksheet name viz. [PivotDataSource.xlsx]Sheet1!$A$3:$G$11 where PivotDataSource.xlsx is the workbook name. If source data is residing in another workbook in a different folder, then the folder path should be entered viz. ‘ExcelTips&Tricks\SS\[PivotDataSource2.xlsx]Sheet1’!$A$3:$G$11 where PivotDataSource2.xlsx is the workbook name and ExcelTips&Tricks is the folder name and SS is the subfolder name which contains the workbook. However, in this case where the workbook is in a different folder and it is moved to a new folder, the Pivot Table will not be able to connect with it, so practically it is prudent to have the workbook containing the source data in the same folder. If you are using a named range for your source data, in the ‘Table/Range’ box, enter the name viz. =CarSales or CarSales without the equal to sign where CarSales is the named range.
External Data Source: You can choose the data which is present in the same or another workbook as explained above, or else the data may be located in an external data source which could be a text file, a database or an Online Analytical Processing (OLAP) cube. To ‘use an external data source’ option, you will need to connect to your workbook with a data connection. A data connection is connection information to access the external data source and it can be stored in the workbook or in a connection file (viz. .odc or .dsn) on the computer. You will find some settings in ‘PivotTable Options’ dialog box not available and dimmed out because these would be available only for OLAP data source (also some settings would not be available for an OLAP data source). It may be noted that we are not covering ‘External Data Source’ in our Pivot Tables topic for now.
Choose where you want the Pivot Table or Pivot Chart report to be placed:
You can create a Pivot Table or Pivot Chart in either a new worksheet or in the existing worksheet itself by choosing the relevant option. Creating in a new worksheet will place the Pivot Table report starting at cell A1. To create in the existing worksheet, enter/select the starting cell where you want the Pivot Table report to be placed, in the ‘Location’ box.
Displaying the initial blank Pivot Table and/or Pivot Chart Report:
Click Ok after entering in the dialog box you have opened. If you have chosen to insert a PivotTable, a blank PivotTable report is created in the specified location, and the ‘PivotTable Field List’ Pane also appears which allows you to Add or Remove Fields, Move Fields to different Areas and to set Field Settings which are tools to structure the Pivot Table and arrange the data. If you have chosen to insert a PivotChart, a blank PivotChart report is created in the specified location together with an associated Pivot Table report. Creating a PivotChart report also displays a ‘PivotChart Filter’ Pane which is used to Sort and Filter the data in the Pivot Chart report. Any layout or data change in the associated Pivot Table report gets automatically reflected in the Pivot Chart report.
The default layout of a Pivot Table report in Excel 2007 is the Compact Form which optimizes for readability, while the tabular and outline forms include field headers. The compact form minimizes scrolling by including the row labels in one column wherein each field is differentiated by indenting and field headers are not shown. For details regarding layout options of a Pivot Table report, refer the section ‘Pivot Table report Layout, Style Options and Styles (Design tab)’.
2. Create Pivot Chart/Pivot Table report from an existing Pivot Table report:
Create a Pivot Chart from an existing Pivot Table: Select a cell in the Pivot Table report, select the ‘Insert’ tab on the ribbon, click a chart type in the ‘Charts’ group. You cannot use an XY(Scatter), Bubble, or Stock chart type with a chart that has been created from Pivot Table data.
Create a Pivot Table report based on an existing Pivot Table: In the earlier versions of Excel, you had an option to create a Pivot Table report based on an existing Pivot Table – you were asked when using the same data of an existing report to create a new report whether to base the new report on the existing report. However, this option is not available in Excel 2007. But you can copy an existing Pivot Table report after selecting it, in Excel 2007, wherein the original and copy will be separate reports.
Delete a Pivot Table or Pivot Chart report
Select a cell in the Pivot Table report, then under the ‘PivotTable Tools’ tab on the ribbon click ‘Options’ tab, click on ‘Select’ in the ‘Actions’ group and then click ‘Entire PivotTable’. This will select the entire Pivot Table report – now press ‘Delete’. This will delete the Pivot Table report. Note that when you delete an associated Pivot Table report in this manner, the Pivot Chart report does not get deleted but gets converted into a Regular Chart.
To delete a Pivot Chart report, select it and press ‘Delete’. Note that deleting a Pivot Chart report does not have any effect on the associated Pivot Table report.
Customize a Pivot Table report
When you insert a Pivot Table, a blank Pivot Table report is created in the specified location, and the ‘Pivot Table Field List’ Pane also appears which allows you to Add or Remove Fields, Move Fields to different Areas and to set Field Settings. Changes made in the ‘PivotTable Field List’ pane get automatically updated in the PivotTable report. The ‘Options’ and ‘Design’ tabs (under the ‘PivotTable Tools’ tab on the ribbon) have various tools to customize the Pivot Table report and determine its structure, layout and design.
The various aspects of Customizing a Pivot Table report have been summarized in different sections as below:
(i) Create a Pivot Table report; Add, Copy, Rearrange & Remove Pivot Table Fields; ‘PivotTable Field List’ Pane – current section
(ii) ‘Pivot Table Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data.
(v) Group Items in a Pivot Table report, Group Data, Group Date and Time Values, Grouped Field.
The ‘PivotTable Field List’ Pane
Using the Pane:
By default the ‘PivotTable Field List’ Pane is divided into two sections: the ‘Fields Section’ which lists the available fields which can be included in the Pivot Table report, and the ‘Areas Section’ which lists the four Pivot Table areas of ‘Report Filter’, ‘Values’, ‘Row Labels’ & ‘Column Labels’. The Pane shows the fields that are currently included in the PivotTable report (these are the selected fields in the ‘Fields Section’) as well as to which Areas they have been assigned (as they appear in the ‘Areas Section’). You can restructure the Pivot Table report and rearrange the data with the Pane which allows you to Add or Remove Fields, Move Fields to different Areas and to set Field Settings.
Show or Hide the Pane:
Click ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, in the ‘Show/Hide’ group, clicking the ‘Field List’ will alternatively show or hide the Pane. You can also close the Pane by: (i) at the top right of the Pane, click on ‘Task Pane Options’ (the down arrow) and in the list click ‘Close’; or (ii) at the top right of the Pane, click on ‘Close’ (ie. x).
Move and Size the Pane:
At the top right of the Pane, click on click on ‘Task Pane Options’ (the down arrow) which has a list of 3 commands: ‘Move’, ‘Size’ and ‘Close’. Clicking on ‘Move’ will display a 4-pointed arrow with which you can drag the Pane to its new position. Another way to ‘Move’ is by just clicking on the top band of the Pane which will display the 4-pointed arrow. Clicking on ‘Size’ will display a double arrow (slanted) at the bottom of the Pane which enables you to re-size the Pane. Another way to re-size is to move your cursor on the borders (top, bottom, left and right) of the Pane and where a double arrow appears (vertical or horizontal), drag and re-size the pane with it – with this you can adjust both the height and width of the Pane. When the Pane view is ‘Fields Section and Areas Section Stacked’ or ‘Fields Section and Areas Section Side-By-Side’ wherein both the Fields and Areas Sections are displayed, the size of each Section can also be adjusted with the double arrow which appears on the four borders and on the section divider between the sections. Clicking on ‘Close’ will close the Pane.
Change the Pane View:
You can change the Pane View by clicking the button towards the top right of the Pane and choose from the 5 options: (i) Fields Section and Areas Section Stacked: The Fields Section appears at the top in the Pane while the Areas Section appears at the bottom part of the Pane under the Fields Section. This view accommodates smaller number of fields, and this is also the Default view. Refer Image 1a; (ii) Fields Section and Areas Section Side-By-Side: The Fields Section appears at the left in the Pane while the Areas Section appears on its right side. This view accommodates higher number of fields. Refer Image 1b; (iii) Fields Only: Only the Fields Section is visible wherein Fields can be added or removed from the Pivot Table – refer Image 1c; (iv) Areas Section Only (2 by 2) – only the Areas Section shows (wherein fields can be rearranged) in a 2 column x 2 rows view – refer Image 1d; and (v) Areas Section Only (1 by 4) – only the Areas Section shows (wherein fields can be rearranged) in a 1 column x 4 rows view – refer Image 1e.
Automatic or Manual Updating of Pivot Table Report:
When a change is made in the ‘PivotTable Field List’ Pane, the Pivot Table report gets updated automatically (default setting). When you are dealing with large amounts of data, you might prefer to quickly update (manually) the Report in the end, after making all changes in the Pane. To do this, select the check box at the bottom left of the Pane, ‘Defer Layout Update’, and after making the changes in the Pane, manually click on the ‘Update’ button at the bottom right, which will update the Report with the changes. Note that in case you have selected ‘Defer Layout Update’ and made changes in the Pane, but before clicking the ‘Update’ button you deselect ‘Defer Layout Update’ or the Pane gets closed, the changes will be discarded without any warning message. Also, if you have selected ‘Defer Layout Update’ and made changes in the Pane but not clicked the ‘Update’ button, you will have to switch back to automatic updating before you can use the Report. If you close the workbook with manual updating selected and then re-open, the setting would have changed to the default automatic updating. So the correct method would be to switch to manual updating (by selecting ‘Defer Layout Update’), and then make changes in the Pane, click on the ‘Update’ button for quickly effecting changes in the Report, and then switch to automatic updating (by deselecting ‘Defer Layout Update’).
Add, Copy, Rearrange & Remove PivotTable Fields
Add Fields in a Pivot Table report:
The ‘PivotTable Field List’ Pane has two sections – Fields Section and the Areas Section. In the Fields Section, are the field names, which are the column headings mentioned in the first row of the Source Data. At least two fields are required in a Pivot Table report – a row or column field and a data field. You can add fields in a Pivot Table in the following ways:
In the Fields Section of the Pane, choose fields to add to the Report. Selecting the check box of a field will add the field in the Areas Section of the Pane and also in the Pivot Table report. By default, numeric fields are added to the ‘Values’ area and non-numeric (text) fields get added to the ‘Row Labels’ area. You can thereafter rearrange these fields to whichever area you want. If more than one field is added to the Values area, a ‘Values’ Column Label automatically gets added. The fields of the Values area are displayed in columns in the Pivot Table report. You can move the ‘Values’ Column Label to the Rows Labels area (but not to the ‘Report Filter’ area), which will display fields of the Values area in rows in the Pivot Table report.
Another way to add fields is to right click on the field name in the Fields Section of the Pane, and choose one of the 4 options – Add to Report Filter, Add to Column Labels, Add to Row Labels or Add to Values. The selected field will get added to the chosen area in the Areas Section of the Pane and also in the Pivot Table report.
Another way to add fields is to select the field name and drag the field from the Fields Section in the Pane and assign to any of the 4 areas of Column Labels, Row Labels, Values and Report Filter in the Areas Section. This also adds the field to the respective area in the Pivot Table report. You can also drag fields between these four areas within the Areas Section, or drag fields within a particular area to determine their order in that area. You can also remove a field by dragging it outside the Pane.
Copy Fields in a Pivot Table:
You can add the same field more than once to the Values area. The field can be numeric or non-numeric, but it can be added more than once only to the Values area. To do this, in the Fields Section of the Pane, select the field and drag the field from the Fields Section and assign to the Values area in the Areas Section (or right-click on the the field name in the Fields Section and choose ‘Add to Values’). This is particularly useful when you want to show or compare calculations in different ways, say simultaneous values based on total sales and on average sales, or total sales and percentages, and so on. Refer Image 2, which simultaneously shows ‘Sum of Sales’, ‘Average Sales’ & Sales as ‘% of Column’.
Rearrange Fields in a Pivot Table report:
Four Areas in the Areas Section:
The ‘PivotTable Field List’ pane has two sections – Fields Section and Areas Section. There are 4 areas in the Area section: Report Filter, Column Labels, Row Labels and Values. As a first step, you initially add a field to any of the four areas as explained above. You can move (ie. rearrange) fields between these four areas or within a particular area to determine their order in that area. Changes made in the ‘PivotTable Field List’ pane get automatically updated in the Pivot Table report.
Report Filter: This is the field on which the Pivot Table report is filtered. In case the Year field is assigned to this area, the report will summarize data for either one or more years, as selected. Report Filters are used to display and focus on a subset of data in a Pivot Table report, for example, to display data by time period (viz. year), by geographical area (viz. countrywise), or products (viz. cars, trucks, … for an auto company), or plants (viz. Plant A, Plant B, ..). Use Manual Filter to narrow down the focus within this subset of data.
Values: The field whose values are to be summarized, is assigned to this area. Data can be summarized in many ways, for example, by Sum, Average, Count, Max, Min, Product, and so on. If the field has numbers, which is usually the case, then values can be summarized as Sum, Average, Max, Min, etc (say, Sum of Sales or Average of Sales). However, a text field which does not have numbers but only text, can be summarized as Count which indicates the number of times a specific text entry appears in the field, such as a color field containing “red” and “blue” text in which case it can be a count of the number of times the red or blue text appears in the color field.
Column Labels: The field assigned as Column, and will display as column labels. The column field which is lower in position gets nested within the immediately higher column.
Row Labels: The field assigned as Row, and will display as row labels. The row field which is lower in position gets nested within the immediately higher row.
References in using a Pivot Table:
Items: Fields also usually have subcategories which are referred to as Items. The Report Filter, Column, or a Row field can have subcategories. The fields are mentioned as column headings in the source data (in the top row), below which in the same column in the source data are its subcategories referred to as items.
Data Area: This is referred to as the area in a Pivot Table report which contains the Data Fields. This area shows the summarized data. When you add a field from the source data that contains values to be summarized, to the Data area, Excel indicates the default summary function for the data. SUM is the default for numeric values ie. the ‘Sales’ field containing numbers will show as ‘Sum of Sales’; COUNT is the default for text fields.
Illustration:
Image 3a shows the Source Data, arranged in a Table in an Excel worksheet – range A3:E19.
Image 3b shows when we click the ‘Insert’ tab (and click ‘PivotTable’ in the ‘Tables’ group, then click ‘PivotTable’ to insert a PivotTable) on the ribbon after selecting the range A3:E19 and the ‘Create PivotTable’ dialog box opens.
Image 3c displays the initial blank Pivot Table report created with the ‘PivotTable Field List’ pane, in a separate worksheet.
Image 3d shows the Pivot Table report in ‘Compact Form’ which is its default layout and ‘PivotTable Field List’ pane displays how the fields have been added/arranged.
Image 4 shows the Pivot Table report in ‘Tabular Form’ – layout has been changed in the ‘Design’ tab (under the ‘PivotTable Tools’ tab on the ribbon). Here the different areas have been marked as follows:
1. Grey shaded cell – Year – this is the Report Filter Field.
2. Dark Red shaded cell – Sum of Sales ($) – this is a Values Field which contains values to be summarized.
3. Blue & Green shaded cells – Car Models & Region – these are Row Fields, also referred as Row Labels.
4. Light Blue cells – Compact & MidSize – these are items in the Row Field of Car Models, also referred as Row Item Labels.
5. Light Green cells – Europe & N America – these are items in the Row Field of Region, also referred as Row Item Labels.
6. Red shaded cell – City – this is a Column Field, also referred as Column Label.
7. Orange cells – London, New York, Paris & Toronto – these are items in the Column Field of City, also referred as Column Item Labels.
8. Yellow shaded cells – this is the Data Area, the cells displaying the summarized data.
Move and Rearrange Fields (& Items):
Move Fields between the 4 Areas: To move or rearrange fields, click on the field name in the Areas Section of the ‘PivotTable Field List’ Pane and choose from the commands: Move Up (moves field one position up), Move Down (moves field one position down), Move to Beginning (moves field to the top of the area), Move to End (moves field to the bottom of the area), Move to Report Filter, Move to Row Labels, Move to Column Labels and Move to Values. You can also drag fields between the four areas within the Areas Section, or drag fields within a particular area to determine their order in that area.
In a Pivot Table report, you can move a Row Field to a Column Field and vice-versa, and you can also move to determine order within the Row Fields or Column Fields. Select a Field Name in a Pivot Table report, right click and point the mouse cursor to ‘Move’, then select from a list of Move commands: Move Up, Move Down, Move to Beginning, Move to End, Move to Left, Move to Right, Move to Rows, Move to Columns.
In a Pivot Table report, you can move to determine order of Fields within the Report Filter area. Select a Field Name in a Pivot Table report, right click and point the mouse cursor to ‘Move’, then select from a list of Move commands: Move Up, Move Down, Move to Beginning, Move to End, Move to Left, Move to Right.
In a Pivot Table report, you can move to determine order of items within a Row Field or within a Column Field. Select a cell in the rows or columns area in a Pivot Table report, right click and point the mouse cursor to ‘Move’, then select from a list of Move commands: Move Up, Move Down, Move to Beginning, Move to End, Move to Left, Move to Right.
To move an item within a Row Label or Column Label, select the relevant item in the Pivot Table report, and move your mouse cursor over the borders of the cell where the cursor changes to a four-pointed arrow, and then drag the item to its new position.
You can also move or manually sort items by: select an item in the Pivot Table report and type in exactly the other item you wish to replace the selected item with while the rest of the order remains the same.
To move by dragging a Row Field to the Columns area or vice-versa in a Pivot Table report, or to change the order within Row Fields or Column Fields, move your mouse cursor over the Field in the Pivot Table report where the cursor changes to a four-pointed arrow, and then drag the Field to its new position. However, to enable dragging of fields you need to display the report in Classic Pivot Table layout – in the ‘Display’ section of the ‘Display’ tab in the ‘PivotTable Options’ dialog box, select the check box of ‘Classic PivotTable layout (enables dragging of fields in the grid)’.
Remove Fields:
To remove or delete a field, click on the field name in the Areas Section of the “PivotTable Field List” pane and click on ‘Remove Field’ in the list of commands. You can also remove a field by dragging it outside the Pane. Deselecting a check box in the Fields Section of the Pane also removes the field.