GETPIVOTDATA in Collabora Office: Retrieve Pivot Results by Field and Item Names
Use Collabora GETPIVOTDATA’s two syntaxes, reproduce a small pivot report, preserve field-based references, and understand filter and subtotal limits.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =GETPIVOTDATA("Sales";$E$1;"Region";"East";"Product";"Pen") returns the pivot result for East/Pen when E1 lies inside the intended pivot table. It addresses the result by names rather than a fragile output-cell position.
Two documented syntaxes
=GETPIVOTDATA(TargetField; PivotTable [; Field1; Item1] … [; Field126; Item126])
=GETPIVOTDATA(PivotTable; Constraints)
The second syntax applies when there are exactly two arguments and the first is a cell/range reference. Otherwise the first syntax applies; it is the form shown by the Function Wizard.
TargetField names the source data column or the displayed data-field name, such as “Sum - Sales”. PivotTable must reference a cell inside a pivot table or a range containing one. If a range contains several pivots, the vendor says the most recently created table is used; a single clear anchor avoids that ambiguity.
Without Field/Item constraints, the first syntax returns the grand total. Field and item names are case-insensitive, and the order of pairs is not significant.
Example: build a reproducible pivot source
Enter this source table in A1:C5:
| Region | Product | Sales |
|---|---|---|
| East | Pen | 10 |
| East | Pencil | 20 |
| West | Pen | 30 |
| West | Pencil | 40 |
Create a pivot table at E1 with Region as rows, Product as columns, and Sales summarized by SUM. Include grand totals and ordinary automatic subtotals. Use E1 as the anchor only if it is within the resulting pivot; otherwise use a verified cell inside it.
This is a setup recipe, not a claim that a pivot was created in an application during preparation of the guide.
Expected lookups for the fixed dataset
| Formula, assuming E1 is inside the pivot | Independent expectation |
|---|---|
=GETPIVOTDATA("Sales";$E$1) |
100 |
=GETPIVOTDATA("Sales";$E$1;"Region";"East") |
30 |
=GETPIVOTDATA("Sales";$E$1;"Product";"Pen") |
40 |
=GETPIVOTDATA("Sales";$E$1;"Region";"East";"Product";"Pen") |
10 |
The arithmetic is transparent: East totals 10+20=30, Pen totals 10+30=40, and the complete table totals 100. A requested subtotal must actually be available under the pivot’s configuration.
Advanced example: use the compact constraint form
For the same table:
=GETPIVOTDATA($E$1;"Sales Region[East] Product[Pen]")
has independently expected result 10. Constraints are space-separated; entries can use single quotes within the double-quoted complete string when needed. A constraint can be Field[Item], or just an item when that item is unique across the pivot fields.
The data-field name may be omitted only if the pivot has one data field. With several measures, include it explicitly. This keeps “Sales” from being confused with a count or another numeric field.
Subtotals and filters are part of the contract
The vendor returns a single matching result or an included subtotal summarizing the matches. No match, or several matches without an appropriate subtotal, produces an error. Hidden source entries excluded by pivot settings are ignored.
In the first syntax, subtotal values are used only when their function is auto. The second syntax can request a subtotal function with Field[Item;Function], supporting Sum, Count, Average, Max, Min, Product, Count (Numbers only), StDev (Sample), StDevP (Population), Var (Sample), and VarP (Population).
A pivot filter’s selected item is implicitly used when no explicit filter constraint is supplied. An explicit constraint must match the selected filter item or an error results. A request for West cannot override a filter currently selecting East.
Layout resilience and troubleshooting
Moving row/column fields can preserve a name-based lookup, but deleting a field, renaming an item, hiding needed results, or changing filter settings can alter or break it. “Layout resistant” does not mean independent of the pivot’s content and configuration.
Check the anchor, TargetField, exact field/item meanings, active filters, and available subtotal functions. The source does not enumerate every native error code. SUM and SUMIFS can provide independent source-data checks for simple reports, but they do not automatically reproduce all pivot filtering and aggregation settings.
Availability and verification
The exact function is documented in Collabora Office 26.04 desktop help, retrieved and reviewed on October 17, 2026. This establishes a documentation scope, not the first release that introduced the function or the last version in which it works; those versions and any retirement date remain unestablished. Collabora Online and mobile support are not inferred from desktop help, nor is behavior in another spreadsheet product.
The examples are documentation-based expectations with independent calculations. They have not been executed in Collabora Office. No application screenshot or native error/coercion certification is implied. Formulas use English function names and semicolon argument separators.
References
- Collabora Office 26.04: GETPIVOTDATA (checked October 17, 2026).
- Editorial testing standards.