Apple Numbers GETPIVOTDATA Function: Pivot Fields and Aggregates
Retrieve pivot averages with a fully specified source table, paired field filters and a weighted-total reconciliation example.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
GETPIVOTDATA retrieves an aggregate from a pivot table, optionally narrowed by field/item pairs. It selects a result by the pivot’s meaning rather than by assuming that a particular summary always remains at one worksheet coordinate.
The function retrieves the pivot table’s aggregate. It does not create the pivot table or decide whether its value field should be a sum, count or average.
Availability and verification
The official GETPIVOTDATA reference was retrieved October 15, 2026. The original function-introduction release and last functioning version are unknown from the checked sources. Current shared function documentation is not a last-working-build test or evidence of identical behavior on every older Mac, iPad, iPhone and iCloud client.
No native Numbers session was executed. The examples use independent arithmetic or constrained text models, with data-dependent results identified explicitly. Public-source retrieval is separate from native verification.
Apple explicitly limits this function to Numbers within its shared iWork function help. The Mac release history announces pivot tables in Numbers 11.2, but that broad feature announcement does not explicitly establish this individual function’s introduction.
Syntax and paired filters
GETPIVOTDATA(aggregate-field, pivot-cell, field-name, item, field-name…, item…)
aggregate-field identifies the value field to retrieve; Apple’s example uses a reference to its header cell. pivot-cell identifies the target pivot table. Subsequent field/item pairs are optional, but each field must have its corresponding item, so the total argument count is even.
Use quotes around field names and text items. Numeric items, such as class number 1, can remain numeric. A quoted label and a numeric identifier should not be silently interchanged.
Complete source dataset and pivot setup
Create a source table with headers Name, Class and Grade, and these six records:
| Name | Class | Grade |
|---|---|---|
| Student 1 | 1 | 91 |
| Student 1 | 2 | 94 |
| Student 1 | 3 | 82.5 |
| Student 2 | 1 | 94 |
| Student 2 | 2 | 91.5 |
| Student 2 | 3 | 91.5 |
Create a pivot with Name and Class as row groups and Grade summarized by Average. Name the pivot table Student Grades Pivot. Arrange its fields so the aggregate header is C1 and C2 is inside the pivot, matching Apple’s illustrated layout. Inspect the actual table before using those references; this is a reproducible setup specification, not a claim that every default pivot layout uses those cells.
Retrieve grand total and filtered averages
=GETPIVOTDATA(Student Grades Pivot::$C$1,Student Grades Pivot::$C$2)
The independent target is 90.75. The six grades sum to 544.5, so their average is 544.5/6.
Adding "Name","Student 1" gives 89.1666666667, often displayed as 89.17. Adding "Name","Student 2","Class",1 instead gives 94. These results reproduce the values in Apple’s pivot example from a completely stated source dataset.
The Student 2 total independently equals 92.3333333333. The two class-3 results are 82.5 and 91.5; their cross-student average is 87 when that aggregate is present in the pivot’s configured scope.
Advanced: unequal group sizes change the total
Add one more source record: Student 1, Class 1, Grade 96. Refresh or update the pivot to include that record before checking formulas; the function cannot be assumed to compensate for an out-of-date pivot.
The updated Student 1/Class 1 mean is 93.5, but Student 1’s overall mean is 90.875, computed from all four underlying records. The updated grand mean is 91.5, because the seven grades total 640.5.
An unweighted average of Student 1’s three class means is 90, which is different from 90.875. Averaging displayed subgroup means discards unequal record counts. GETPIVOTDATA should retrieve the intended aggregate from the pivot instead of replacing it with that shortcut.
Advanced: make a report resilient to layout changes
Keep the selected field name and item values visible beside a report formula. If a pivot is rearranged, verify that pivot-cell still identifies the intended pivot and aggregate-field still identifies the intended measure. The field/item filters express the desired slice; an accidental reference to a different value field can still select the wrong statistic.
For the original six-record fixture, a report with Student 1 and Student 2 labels has expected averages 89.1666666667 and 92.3333333333. Preserve the full-precision results for later calculations rather than rebuilding a grand total from rounded labels.
Errors and troubleshooting
Check pairs first: a field without an item is incomplete. Then check exact field names, whether numeric class items are numeric, and whether the chosen aggregate exists in the configured pivot.
The dedicated page does not enumerate native errors for nonexistent items, filtered-out groups, invalid pivot references or stale source data. Those cases were not executed. Do not claim that a missing group returns zero or that every hidden item remains retrievable.
A correct formula can still report an unintended value when the pivot’s aggregation changes from Average to Sum. Verify the pivot definition, source-record count and refresh state alongside the formula itself.