Excel Pivot Table Data in Formulas: How to Use It

Charts & pivot tables · Updated November 19, 2026

Yes. In Excel for Microsoft 365, use GETPIVOTDATA to retrieve a value from a PivotTable by its field names and items, or use an ordinary cell reference when you want the result from one specific pivot cell. For the examples below, assume the PivotTable starts at A3 and its value heading is Sum of Sales: =GETPIVOTDATA("Sum of Sales",$A$3,"Region","West") returns the West sales total.

What it means to use PivotTable data in a formula

A PivotTable summarizes records, often by categories such as region, month, or product. You can use its results in formulas elsewhere in the workbook. The key choice is whether the formula should follow a particular PivotTable cell or find a summary by its field and item labels.

A direct reference such as =B5 reads whatever is currently in cell B5. GETPIVOTDATA instead asks for a value associated with specified PivotTable fields. That difference matters when the PivotTable is filtered, rearranged, or expanded: a cell reference can point to a different result after the layout changes, while a field-based request is designed to identify the requested summary.

There is a third approach: calculate from the original source data with functions such as SUMIFS. This can be useful when your formula should not depend on the PivotTable’s display, but it will not automatically behave like a PivotTable filtered by a slicer. These methods complement one another; none is best for every workbook.

How GETPIVOTDATA works

GETPIVOTDATA uses the name of a value field, a reference to any cell inside the PivotTable, and optional pairs of field names and item names. Its general structure is =GETPIVOTDATA(data_field,pivot_table,[field1,item1],...). The data field is the value caption shown in the PivotTable, such as Sum of Sales or Average of Units. The pivot_table argument anchors the request to the correct PivotTable.

For example, suppose the PivotTable has Region and Product as row fields and Sum of Sales as its value field. With the PivotTable beginning at A3, the formula in the opening example requests the West total. Add another field-and-item pair to narrow the result to a product. Assuming the same layout, and West sales for Desk total 200, use =GETPIVOTDATA("Sum of Sales",$A$3,"Region","West","Product","Desk"); it returns 200.

You can also refer to cells containing the requested item names. Assume E2 contains West and F2 contains Desk, and the same PivotTable begins at A3: =GETPIVOTDATA("Sum of Sales",$A$3,"Region",E2,"Product",F2) returns 200. This makes a report easier to reuse: change E2 or F2 rather than editing the formula. Field and item text must match the PivotTable’s labels; spelling, spaces, and the displayed value-field caption can matter.

To make Excel create a GETPIVOTDATA formula, type an equals sign in a cell and click the relevant value in the PivotTable. Excel normally inserts a formula using that value’s field and item labels. If Excel inserts a simple cell reference instead, check the Generate GetPivotData option on the contextual PivotTable Analyze tab. In Microsoft 365 for Windows, it is available from the Options menu on that tab; ribbon placement can vary with the window size. On Mac, use the PivotTable Analyze tab and its Options controls if shown; exact placement can vary by Excel release.

When a direct cell reference is enough

A direct reference is simple and appropriate when a formula is intentionally tied to a fixed position—for instance, a dashboard card that should display the value currently shown in B5. Assuming B5 contains the West total of 200, =B5 returns 200. You can also perform a calculation on that displayed value. If B5 is 200, =B5*1.1 returns 220, a 10% increase.

The trade-off is that the address does not explain what the value represents. If a user sorts, expands, or rearranges the PivotTable, B5 may show a different category or a heading. A formula may still calculate without an error while silently pointing at the wrong result. Use direct references when the layout is controlled and position is the intended link; use GETPIVOTDATA when the category names are the intended link.

Use source-data formulas when the PivotTable is not the right source

SUMIFS can calculate a total directly from the records behind a PivotTable. This is helpful when a report needs a fixed calculation, or when you do not want its answer to depend on where a value appears in the PivotTable. It also means you must keep the source ranges and criteria in the formula accurate.

Assume the source data has Region in A2:A100, Product in B2:B100, and Sales in C2:C100; E2 contains West and F2 contains Desk. If the matching Desk records in West have sales of 120 and 80, =SUMIFS($C$2:$C$100,$A$2:$A$100,E2,$B$2:$B$100,F2) returns 200. This result is based on the source rows, not on the PivotTable’s visible layout. If the data grows beyond row 100, the ranges must be extended, or the source should be organized as an Excel Table and the formula adapted to use its structured references.

Unlike GETPIVOTDATA, this SUMIFS formula does not automatically respond to PivotTable filters or slicers. It applies only the criteria written into the formula. That distinction is a common source of confusion: two formulas can both return sales totals but answer different questions because one follows the PivotTable’s selections and the other filters the raw records independently.

How PivotTable filters and refreshes affect formulas

A GETPIVOTDATA formula retrieves a value from the PivotTable as it currently presents its data. Changing a report filter or slicer can therefore change the available values and the formula’s result. If the formula asks for a combination that is not present in the current PivotTable, Excel may return an error rather than a zero. A zero result, by contrast, can mean the requested combination exists but its summarized value is zero.

Refreshing the PivotTable updates it from its source, subject to the workbook’s connection and refresh settings. A formula linked to the PivotTable then reads the refreshed result. However, refreshing does not fix a formula whose requested field name or item no longer matches. If the source labels change, review dependent formulas and any report cells that use them.

When several PivotTables are on a sheet, use an anchor cell inside the intended one. A reference outside the PivotTable, or an anchor pointing to a different PivotTable, can produce a reference error or retrieve from the wrong summary. For a dependable workbook, keep the PivotTable’s location clear, use cell-based criteria where practical, and test formulas after changing filters and refreshing.

Calculated fields are different from worksheet formulas

Excel also lets you create a calculated field inside some PivotTables. This is not the same as writing a worksheet formula that reads a PivotTable result. A calculated field becomes part of the PivotTable’s summary and is based on fields in its source records. It is useful when the derived measure belongs alongside the other PivotTable values.

For example, a calculated field might derive a line-level margin from source fields for revenue and cost, then summarize that calculation in the PivotTable. A worksheet formula beside the PivotTable, on the other hand, can combine already summarized values or present them in a custom report. Choose based on the intended level of calculation: before summarization, inside the PivotTable, or after summarization.

Calculated-field options depend on the PivotTable’s source. PivotTables based on the Excel Data Model or certain OLAP sources do not support all the classic calculated-field features. In those cases, a measure created with the model’s tools may be the appropriate way to define a reusable calculation. Do not assume a formula available in a conventional PivotTable will be available for every data connection.

Choosing the right method

  • Choose GETPIVOTDATA when you want a summary identified by PivotTable field names and items, especially in a report that should survive changes in the PivotTable’s position or arrangement.
  • Choose a direct cell reference when you deliberately want the current contents of one fixed cell and the PivotTable layout is stable.
  • Choose SUMIFS or another source-data formula when the formula should calculate from the records using its own criteria rather than inherit the PivotTable’s displayed result.
  • Choose a calculated field or measure when the derived calculation should be part of the PivotTable itself; confirm that the source type supports the feature you need.

For a dashboard with several independent summary cards, GETPIVOTDATA often makes the intent clearest. For a one-off calculation beside a fixed report, a cell reference may be more readable. If users expect a slicer to control a number, make sure the formula actually depends on the PivotTable rather than independently summing the source rows.

Common errors and practical checks

A #REF! error often means Excel cannot find the requested PivotTable value. Check that the anchor is inside the intended PivotTable, that the value-field caption is correct, and that each field and item exists in the current layout or filter state. If a value field was renamed—for example, from Sum of Sales to Total Sales—update the data_field text to the caption Excel displays.

If a formula gives a plausible but incorrect number, check whether it is pointing to the intended PivotTable and whether its criteria describe the intended summary. Also check whether a filter or slicer has excluded the requested item. A direct reference can be especially hard to diagnose because it may keep returning a valid number after the PivotTable’s layout changes.

Before building a large report, test one formula for a category whose answer you can verify in the PivotTable. Then change a filter, refresh the data, and confirm that the formula behaves as expected. If the report should show a friendly message when a requested combination is unavailable, you can wrap the retrieval in IFERROR; use that only when replacing an error with a message or alternative value makes sense, rather than hiding a broken field name.

Frequently Asked Questions

Can I use a PivotTable result in another worksheet?
Yes. A GETPIVOTDATA formula can refer to an anchor cell on the worksheet containing the PivotTable, using a sheet-qualified reference when needed. A normal cell reference can also point to another sheet, but it remains tied to a cell address.

Why does Excel return a cell reference instead of GETPIVOTDATA?
The Generate GetPivotData option may be turned off. Enable it from the PivotTable Analyze tab’s Options controls, then type an equals sign and click the PivotTable value again.

Does GETPIVOTDATA update when I use a slicer?
It reads from the PivotTable, so its result can change as the slicer changes the PivotTable’s visible summary. A separate SUMIFS formula does not automatically inherit that slicer selection.

Can GETPIVOTDATA return a value from a collapsed PivotTable group?
It can request an item by field and item name rather than relying on the item’s visible row position. The item and requested value must still be available in the PivotTable’s current structure and filter context.

Should I use GETPIVOTDATA or SUMIFS?
Use GETPIVOTDATA when the answer should come from the PivotTable and respond to its selections. Use SUMIFS when the answer should be calculated from source records using criteria specified in the worksheet formula.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.