Pivot Options: Data Area

To access this screen:

Use the Data Area tab to define which values are calculated in the pivot table, how they are formatted, and which summary method is used. These settings populate the Data Area on the main Pivot Table screen and control the values shown in the pivot table cells.

Activity steps:

  1. Display the Data Area tab.

  2. Expand Apply task filter to select a filter if you want the pivot table to use only tasks that match that filter if required. By default <No task filtering> means no filter is applied (all data items are used in the pivot table).

  3. In the table below, select a Field to define a value field for the pivot table.

  4. Under Format, review the convention used to display numbers, if a numeric field was selected. Click the field to set how that value is displayed in the pivot table.

  5. For a numeric field, click Format to set how values are displayed in the pivot table.

    • Select a preset format if you need a standard display, for example, use <Default> to display the value in the most obvious format, such as a currency value for cost fields, and so on.

    • Set the number of decimal places if you need to control numeric precision.

    • Enter a value in Custom Format Text if you need a specific format.

      • Use N0, N1, N2 and similar values to show a number with 0, 1, 2 or more decimal places.

      • Use C0, C1, C2 and similar values to show a currency symbol with the selected number of decimal places.

      • Use E in the custom format if you need exponential notation.

      • Use P with a number, for example P3, if you need percentage display with a fixed number of decimal places.

  6. In Summary Type, select the calculation to use for the selected field.

    • Select Count to show the number of records.

    • Select Sum to show the total value.

    • Select Min to show the lowest value.

    • Select Max to show the highest value.

    • Select Average to show the arithmetic mean.

    • Select StdDev to show standard deviation based on a sample of the data.

    • Select StdDevp to show standard deviation based on the full population of the data.

    • Select Var to show variance based on a sample of the data.

    • Select Varp to show variance based on the full population of the data.

    • Select Grade/Ratio if you need a grade-based or ratio-based result defined for the selected data.

    • Select CountDistinct to show the number of unique values.

    • Select Median to show the middle value.

    • Select Mode to show the most frequent value.

    Note: Display the Summary Type for the Field on the pivot table using the General tab's Show domain function option.

  7. Open the main Pivot Table screen and check the Data Area and pivot output to confirm that the selected calculations return the result you need.

Related topics and activities: