Adding Fields to a Pivot Table

A pivot table is blank when it loads for the first time. You need to drag and drop fields to various locations to construct the table. Follow these steps after loading a new pivot table:

1. If not already displayed, click the Field List icon in the pivot table toolbar to open the Pivot Table Field List.
   • This list contains all of the fields you specified for the view's entity as well as for any linked entities.
   • See the following example:
     
     
2. Select fields from the Field List and drag them to the various locations in the pivot table, as desired. Note that you can stack multiple fields in the same area but you cannot add a field to more than one area of the pivot table. The available locations include:
   • Drop Filter Fields Here: If you did not specify view filters or if you want to further refine the results in the pivot table, you can add fields here to filter the table results. For example, you might want to filter a table based on order date.
   • Drop Row Fields Here: Drop one or more fields in this area to create row entries for the table. You can add multiple fields to create layered rows. For example, you can add the Company field to the left edge of the row area and then add a Person field to the right of the company. This creates top-level rows for each company in the dataset and then sub-rows for each person at a particular company. See this example:
     
     
   • Drop Column Fields Here: Drop one or more fields in this area to create columns entries for the table. As with rows, you can add multiple fields to create layered columns. For example, you might want to display order data by person based on the product that appears on an order's first order line.
   • Drop Totals or Detail Fields Here: Drop one or more fields in this area to specify the details of the report. For example, you might want to drop the Grand Total field to display the Grand Total for each order associated with a particular person and product.
      
3. Move or rearrange fields as necessary. The Fields added to the pivot table layout can be modified in a variety of ways. For example, they can be moved to other areas of the pivot table, removed entirely from the pivot table, or sorted, as described below:
   • To remove a field, select its heading in the pivot table. Right-click the field and then select Remove Field from the pop-up menu. The field is removed from the pivot table area but remains in the Field List and can be added again later if needed.
   • Some fields can be sorted, either ascending or descending, depending on their location on the pivot table. To sort a field, right click on the field and select either Sort Ascending or Sort Descending.
      
4. Add calculations to the pivot table, as desired.
   • Calculations can be performed on certain numeric pivot table fields, depending on where they appear in the pivot table (typically in the Total or Details area).
   • To apply calculations, right-click the field and select AutoCalc. Then select the appropriate calculation from the list of choices: Sum, Min, Max, or Count. Not all choices may be available for each field.
   • In the figure below, a Sum of Grand Total calculation has been added to the detail tab to add the totals from all of the displayed orders. The calculated field is added to the Field List automatically.
     
     
     
     
5. Use the plus (+) and minus ( ) signs to expand and collapse items to display only specific data. These options are also available in the right-click menu when you select one or more items in the table.
6. Modify the layout for a particular field using the Command and Options dialog.
   • For example, you might want to display order totals as currency amounts (as shown below).
   • To open the dialog, either select Commands and Options... from a right-click menu or click the Commands and Options icon in the pivot table toolbar.
     
     
7. Export or print the pivot table, as needed.
   • See Exporting Pivot Table Data to Microsoft Excel for export instructions.
   • See Printing a View for printing instructions.